PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

1
vote
0answers
27 views

Problems while building plpgsql_check on Windows

I try to build plpgsql_check extension by @PavelStehule (Erwin Brandstetter recommended it to me in this answer) on Windows XP 32bit with Visual Studio 2010 (I used this article as a manual). However, ...
0
votes
1answer
39 views

How to get pg_archivecleanup on Amazon Linux 2014.03?

I have an Amazon EC2 instance based on Amazon Linux AMI 2014.03. I installed PostresSQL from their repos: yum install postgresql-libs postgresql postgresql-server postgresql-devel I've read about ...
1
vote
1answer
39 views

Postgres resource management

I'm consolidating multiple Linux boxes running Postgres into one box. Given the way Postgres handles resource utilization, would one single server service handling all the databases provide a much ...
4
votes
2answers
48 views

Update SQL n:n table with multiple associations

I have done this before, but before I mindlessly repeat something that I consider as a hack, I'm asking here. I have 3 tables (details left out for clarity) : inv_items id bigserial (PK) sku ...
1
vote
1answer
47 views

Does PostgreSQL support multi-threaded replication?

Is PostgreSQL replication single-threaded ? Are there any tools to achieve multi-threaded replication ? I'm asking this since Mariadb support this and currently I'm learning both these dbs.
0
votes
0answers
11 views

Creation of a table with foreign keys in postgres is taking longer time [duplicate]

I am wondering if someone can help me understand this. I tried to add an column to a table of 22000 rows which is referencing other tables(one of these tables have 0.5 million records). It didn't ...
1
vote
0answers
69 views

PostgreSQL continuous archiving - use snapshot for base backup on AWS?

I'm new to AWS and PostgreSQL admin and have been poring over the docs trying to come up with a good backup strategy for a fairly simple setup. I have a single Amazon Web Services EC2 instance that ...
1
vote
0answers
85 views

Mariadb new features vs PostgreSQL? [closed]

Mariadb 10 offer lot of new features. ( https://mariadb.com/products/mariadb/mariadb-vs-mysql) . I want to know, with these new features, will PostgreSQL would be a better solution for newly starting ...
0
votes
0answers
17 views

setting auto-increment value of primary key to maximum in postgre sql [duplicate]

the sequencing of my auto incremented id value which is also primary key of the table has changed due to some unknown reason. i want to know whether i can reset this value to the maximum value by ...
2
votes
1answer
50 views

Difference between pg_column_size(table.*) and pg_column_size(table.col1) + pg_column_size (table.col2)

From PG DOC pg_column_size(any) : number of bytes used to store a particular value (possibly compressed) pg_column_size shows the space used to store any individual data value Example: ...
0
votes
0answers
65 views

Data Synchronization from SQL-Server to PostgreSQL

I am trying to figure out the best method for duplicating data in one-direction from a SQL-Server database to PostgreSQL. I know that there are no replication providers for SQL-Server to PostrgreSQL. ...
1
vote
2answers
97 views

What is a good solution for reliably transferring data between SQL Server and PostgreSQL?

In analyzing some of our business data, we're realizing that we need to join data across disparate data sources. For example, our application data is warehoused in Postgres (ported from MongoDB via ...
0
votes
1answer
33 views

Postgresql Function Trying to Insert into 1 row instead of 2

Postgresql 9.3, Mac OS X 10.6.7 I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows. First, here is the Table T1: ...
2
votes
1answer
70 views

storing millions of rows of denomalized data or some SQL magic?

My DBA experience doesn't go much further than simple storage + retrieval of CMS style data - so this may be a silly question, I don't know! I have a problem whereby I need to lookup or calculate ...
1
vote
2answers
39 views

Can I find errors without using testing data for all the branches of each function?

In PostgreSQL, syntax errors (except for trivial syntax errors) are reported while running the code in question; unlike Oracle, PostgreSQL can't find errors without running the part of the code. This ...
3
votes
1answer
99 views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
0
votes
1answer
46 views

Database design Problem for the web-app

Hello I am in a problem of designing a web-app database schema. Suppose I have 10 shops and every shop gives deals on every different day Sunday, Monday and so on and for limited time period like ...
0
votes
1answer
36 views

How to start my 9.2 data directory using an 8.4 server?

I have an old version of postgres (8.4.13) that doesn't run for various reasons. I plan to move the database files in there to a new install of postgres 9.3. How would I go about doing this? For one I ...
0
votes
1answer
56 views

Using the correct postgres server

I'm not sure what happened but I logged in on friday and did psql -d name to access a db that was perfectly running just fine. Then I log in this monday morning and try to access the same database ...
4
votes
2answers
64 views

Postgres reported table / db size varies wildly from dump size

I have a very strange misreporting of the size of one specific database. According to \l+ in psql the DB size is 292 MB. Doing a sum over the sizes reported by the following statement also reports ...
0
votes
1answer
35 views

Implementing Permissions Database structure on a PHP site to restrict the Access in Multiple levels

I am working on a PHP site that shows the business of a Vehicle Showroom company in which we have 3 users as, Admin(Owner) = Here Admin Have all Access by default(View,Edit,Delete to all) Super ...
1
vote
0answers
47 views

Join and aggregate result and insert blanks for missing appendants

Consider these two simplified tables: CREATE TABLE "sources" ( "id" serial NOT NULL, "name" text NOT NULL, CONSTRAINT "sources_pkey" PRIMARY KEY ("id") ); CREATE TABLE "values" ( ...
2
votes
1answer
63 views

In Postgresql, after pg_dump the access privileges on the database itself are not restored

Suppose user foo owns a database gee, and user foo has granted user bar with all access privileges on database gee gee=> \l List of databases Name | Owner | ...
1
vote
2answers
57 views

Postgresql: datatype for email address

What would be the right datatype to store email addresses in PostgreSQL? I can use varchar (or even text), but I wonder if there is a more specific data type for emails.
2
votes
2answers
74 views

Official PostgreSQL Capitalization conventions [closed]

Is there an official PostreSQL convention regarding capitalization in DB, Table and field names? The examples on the official site suggest a lowercase and _ word separation, and I wonder whether this ...
2
votes
1answer
27 views

Rewrite sqlite trigger to postgresql

Can you help me please with this problem? I need rewrite this simply trigger from sqlite to PostgreSQL format: In sqlite: CREATE TRIGGER connections_INSERT_update_connection_root_trg ...
1
vote
1answer
43 views

“Recover” data from running PostgreSQL instance; data directory was deleted

I accidentally removed data directory /var/lib/pgsql/9.1/data from running postgresql server. I guess I'm going to lose my data when server will be restarted. I'm wondering if postgresql has any ...
0
votes
1answer
31 views

Postgresql Chained Slaves and Slave Promotion

So I have a setup like Master -> Slave1 -> Slave2. Slave1 replicates from Master and Slave2 replicates from Slave1. If I promote Slave1 to master is it possible for Slave2 to continue replicating from ...
0
votes
0answers
27 views

PostgreSQL and Single Table Inheritance

I read about STI (Single Table Inheritance) and like the idea. Does PostgreSQL handle 95% NULL values in some columns if I have about 1M rows well? Does it impact performance if there many columns ...
1
vote
1answer
43 views

Postgres: my “data/backup” directory is way too large

In my postgres 9.3 data directory, there exists a subdir called "backup" that is 6.1G compared to my pg_xlog which is only 130M. I understand that pg_xlog is the WAL logs, so what is in the backup? ...
0
votes
2answers
49 views

What is the Maintenance DB asked by pgAdmin?

pgAdmin dialog for adding new database connections asks for a "Maintenance DB". In order to be able to connect, I set it to the database I want to connect (and also, I have the rights to connect). ...
0
votes
0answers
25 views

Where is psql connecting?

TL;DR: It was a network issue. Read the updates at the bottom for what I found. Typing psql, I can connect: $ psql psql (9.3.2) Type "help" for help. ben=# Typing psql -h localhost, I can't ...
1
vote
0answers
107 views

pgAdmin goes “out of memory” with large queries

I am trying to run query with results stored in file from PostgreSQL. Now I am trying to use pgAdmin for this task: running query to file option. Problem starts with large queries. When I try running ...
1
vote
1answer
81 views

SQL hourly data aggregation in postgresql

I am a newbie with database so I'm seeking your help with this one. I have a table containing time series data. 2012/01/01 00:10, 10 2012/01/01 00:30, 5 2012/01/01 01:00, 10 2012/01/01 01:40, 10 ...
1
vote
2answers
69 views

Function execute query, manipulates result and then return the same result

I need a function that does something like this pseudocode: function get_data() RETURN SET OF something... as BEGIN myResultSet = select id, some_other_column from ...... limit 20000; update ...
1
vote
1answer
30 views

Limit balancing results by a column

I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed. I also have a function that returns me the ...
1
vote
2answers
68 views

how should i do this trigger?

I am a newbie to the sql world :) Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ? Here is the code : DELIMITER ...
1
vote
2answers
55 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
0
votes
0answers
19 views

Longest IPv4 Prefix Match using PostgreSQL

Given an IP address 192.168.0.1, and a table with a column next_hop_subnet storing subnet IP addresses, do you see any problem with the following PostgreSQL logic, accuracy or performance-wise: ...
2
votes
1answer
64 views

Extremely long query time after upgrading to Postgres 9.3 from 9.1

We're attempting to upgrade from Postgres 9.1 to Postgres 9.3 on a FreeBSD 9.2 zfs box. Performance is noticeably slower across the board, but we have one query that has gone from 353ms to >200000ms. ...
0
votes
0answers
33 views

GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys

I'm trying to execute the following statement in a Rails app that uses postgis. Postgres 9.3.4, Postgis 2.1.3 (with homebrew on Mac). ActiveRecord::StatementInvalid: PG::InternalError: ERROR: ...
1
vote
2answers
72 views

Return several values from a PL/pgSQL procedure - to speed up the query

I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is ...
0
votes
1answer
46 views

PgBouncer Uncertainties

I'm trying PgBouncer for the first time with a simple Python web application (Flask + psycopg2), and I have a hard time interpreting the meaning of its log messages by simple googling. I'm basically ...
0
votes
1answer
19 views

How to CREATE TABLE … LIKE without the NOT NULL Constraints?

I'm trying recreate a table's structure inside a function by using some dynamic SQL. EXECUTE 'CREATE TABLE ' || my_table_name || '_bk' || ' (like ' || _my_table_name || ')'; That will be ...
1
vote
1answer
45 views

PostgreSQL WHERE clause with two OR'ed conditions, priority to first

I'm storing user data (username, email, password hash) in PostgreSQL, and I can only enforce uniqueness on username. I'd like to let users provide either their username or their email when they login. ...
1
vote
1answer
62 views

Add NOT NULL constraint to large table without table scan

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table ...
0
votes
0answers
36 views

ZLM 7.3, postgres database must be vacuumed within x transactions

We have a legacy ZLM 7.3 IR4 primary server running on a SUSE 10.4 server with a local postgresql (8.1.23) database. We are getting the following warning from the nightly vacuum cron script ...
0
votes
0answers
20 views

PG Commander, how I can see how long the query is running already?

For postgres queries I use both PG Commander (osx) and pg_admin (ubuntu). With PG Commander I can't seem to figure out where I can monitor the running time of a query. In pg_admin there is a tiny ...
1
vote
1answer
73 views

Have Postgresql query planner use nested loop w/ indices over hash join

I'm having a problem with some StackOverflow-schema related data loaded into PostgreSQL 9.3.4. I have a query that is taking about 10x longer than it should, due to the fact that it is choosing to use ...
0
votes
1answer
45 views

Compare two similar Postgres databases for differences

I occasionally download publicly available data sets in the form of Postgres dBs. These datasets are updated/modified/expanded over time by the repository host. Is there a Postgres command or ...