PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

0
votes
2answers
707 views

migrate SQL Server geography data to PostGIS geometry data

I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS. I know there's a way to do the ...
4
votes
1answer
261 views

Postgresql - Internal Array handling - array type or pointer type

I have a question, about the internal handling of array columns in Postgres. Does the engine handle it as a classical array with memory alloc or is it somehow a pointer list. It would be interesting ...
1
vote
3answers
4k views

Many-to-one Subselection in single query

I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2. To create a simple example, lets say T1 is Cars, ...
1
vote
1answer
171 views

Postgres not vacuuming 1 table

I have created a table where I store serialized Java objects. They are frequently accessed and costly to create, so that is the reason. Solution works fine to me, however table size grows to 25GB ...
0
votes
0answers
498 views

Multiple PostgreSQL running as primary server on pgpool

I'm facing a problem with my pgpool setup. Currently, this is my requirement based on the pgpool documentation here : Server A : Install with PostgreSQL database. Run as primary server Server B : ...
0
votes
1answer
589 views

What's included in the default OSX 10.8 install of Postgres?

I've seen several sites claim that Postgres is installed by default for OSX 10.7 and above and I see there's a psql command in /usr/bin/, but there doesn't seem to be a pg_ctl anywhere. So is the ...
2
votes
1answer
2k views

Violates foreign key constraint

I'm trying to implement the below represented UML and I can't figure out where is my mistake since I have already place a value into the table. -- Schema: public DROP SCHEMA IF EXISTS public ...
2
votes
1answer
401 views

Select consecutive number of rows starting from some number

What I need is to select 100 consecutive rows from a table, starting at some defined point without ordering data. As far as I can observe, inserted data to a table is stored in no order but is ...
0
votes
1answer
333 views

Heroku PostgresApp - Fails to start with edited postgresql.conf

I am trying to tune my local pg running from the Heroku Postgress App. I am seeing strange errors in the OSX console whenever I change some of the internal settings: PM Postgres[78709]: Could not ...
2
votes
2answers
1k views

Postgresql after 'for each row' triggers don't actually execute after each row?

If I have a single sql statement that inserts or updates lots of rows and a db trigger that runs after each row is inserted or updated, it seems like the triggers run after all the rows are inserted ...
2
votes
1answer
225 views

Number of virtual tables that specified user is able to write queries in PostgreSQL

I have one question about PostgreSQL that I can't understand. As it seems so far, it needs some of bruteforce in tables. Well, I did that lots of time and I found nothing. So, firstly I will cover my ...
1
vote
1answer
641 views

I just upgraded PostgreSQL with yum on Amazon Linux, now PostgreSQL won't start. What do I do?

I just ran yum update on my Amazon Linux instance as part of an upgrade to 2012.09 and it upgraded PostgreSQL from 9.1 to 9.2. Now PostgreSQL won't start. What do I do? (This is a very incomplete ...
2
votes
1answer
399 views

Copy Postgres databases with indexes between instances

How do you copy a single Postgres database between installations without using pg_dump and without regenerating indexes? We have an application we deploy which uses Postgres 8.4.7 database in Linux. ...
3
votes
1answer
716 views

Tricky selection of grouped rows, selecting based on values of two distinct but related rows

Say I have a table with the following information (sorted here for display purposes) taskid | reference | centreid | date_out --------------------------------------------------- 10001 | ...
3
votes
3answers
238 views

PostgreSQL equivalent of SQL Server SQL_VARIANT_PROPERTY

Does PostgreSQL have an equivalent function for determining column metadata? Reference: SQL_VARIANT_PROPERTY pg_typeof() appears to be close, but it does not show the precision and scale. Looking up ...
3
votes
1answer
305 views

Postgres Plus Cloud Database on AWS for write heavy social game?

I'm one of four founders of a big social gaming iPhone app in Sweden. We now suffer from severe database server issues so I'm turning to you guys to get some help. First of all, let me get this out ...
6
votes
2answers
968 views

SQL ANSI JOIN precedence

I have a query that looks like this: SELECT * FROM TBLA A LEFT JOIN TBLB B ON A.Col1 = B.Col2 RIGHT JOIN TBLC C ON B.Col3 = C.Col4 JOIN TBLD D ON C.Col5 = D.Col6 In which order ...
0
votes
0answers
558 views

PostgreSQL pg_bulkload query eats all free disk space

I have a simple task: bulk insert csv file to the table. The .ctl file is TYPE = CSV # Input file type ESCAPE = \ # Escape character for Quoting ...
3
votes
1answer
847 views

“Zero” downtime and minimal space requirements for upgrade to PostgreSQL 9.2

Need to migrate from PostgreSQL 8.3.x to 9.2 Constraint 1: 8.4 and above have made datetime format to be integer by default. Wasn't so in 8.3.x. As a result pg_upgrade can't be used. Unless server is ...
5
votes
1answer
809 views

What AWS instance to choose for write-heavy PostgreSQL database server

We are now having problems trouble to tackle the intensive traffic to our servers during peak hours; see this related question on tuning the DB. At the moment we are using a smaller cloud service for ...
7
votes
1answer
4k views

How to determine the sweet spot between pool size and database connections in PostgreSQL

We are having trouble handling the traffic during peak hours to our database server. We're looking into improving the hardware (see this question about that side of things) but we also want to work on ...
3
votes
2answers
809 views

Continuous archiving: is it possible to use multiple machines?

Is it possible to use the archive_command to send to multiple machines? Is it just as simple as separating with a && since it is essentially just a command line? command1 && command2 ...
1
vote
1answer
549 views

How to check if databases are consistent in pgsql replication?

There is some tool for mysql (from percona I think) which could compare databases by making md5 sums of tables data. We used it in our mysql replication system to check if data is correctly ...
1
vote
1answer
704 views

PostgreSQL 9.1 - Asynchroneous commits performance boost

During peak hours our users experience a lot of timeouts due to an overloaded database server. We are currently trying to make optimizations in order to cope with the high traffic. We read in the ...
2
votes
0answers
513 views

PostgreSQL 9.2.1 out of memory on \COPY [closed]

I'm running a postgreSQL 9.2.1 installation in a FreeBSD 9.0 Jail with default settings. I've configured loader.conf according to this post: ...
7
votes
3answers
878 views

Efficiently comparing prices in different currencies

I want to make it possible for user to search products within a price range. User should be able to use any currency (USD, EUR, GBP, JPY, ...), no matter what currency is set by the product. So, the ...
1
vote
1answer
78 views

Slave as a master in PostgreSQL [closed]

I have set up a hot standby in PostgreSQL 9.0 on Windows. I want that, at the time of fail-over, the slave should take over the role of master and start performing operations (both read and write) ...
1
vote
1answer
1k views

Can't start postgres server, SHMMAX too small

When I try to start the postgres server with sudo /etc/init.d/postgresql start I get this error: Starting PostgreSQL 8.4 database server: mainThe PostgreSQL server failed to start. Please check ...
0
votes
1answer
6k views

Postgres requires commit or rollback after exception

Just trying to understand the thinking here... This bug explains that with other databases, you are allowed to run additional commands after a SQLException occurs. It was written in 2001 but has ...
3
votes
2answers
636 views

Do I risk losing the benefits of indexing if I have an index on every column?

I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of ...
15
votes
2answers
17k views

Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding ...
3
votes
1answer
781 views

Dump PostgreSQL schema with foreign keys and referenced data

The official PostgreSQL documentation states that pg_dump can use the --schema=SCHEMA option to selectively dump a given schema. However according to the same documentation (read Note on ...
11
votes
1answer
6k views

Index optimization with dates

I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field. I want to divide the query by millions, for scalability & concurrency purposes, and I ...
1
vote
1answer
270 views

postgresql pgadmin3 queryconnect attribute does not work

I'm using Fedora 17 and pgadmin3 to auto-connect to a postgresql database with one button click. Every time I want to run an SQL statement, I have to do 4 steps, (open pgadmin3), (connect to the ...
1
vote
1answer
571 views

drop postgres table with minimal locking

I'm trying to run the following Postgres migration script DROP TABLE some_table; DROP TABLE another_table; The problem is that this script causes excessive locking when run on the production ...
1
vote
2answers
149 views

How to remove tables and indexes?

I've a file with the SQL statements to create tables, indexes, etc, and another file with data test to use in the test database. Then, I want that they can be removed. How to remove table and indexes ...
2
votes
1answer
235 views

PostgreSQL asynchronous replication (and possible data loss)

I'm trying to wrap my head around Postgres' asynchronous replication. The way I'm reading the documentation, if the master goes down after data was committed, but before it's shipped to slaves, the ...
13
votes
3answers
4k views

Set names to attributes when creating JSON with row_to_json

Is it possible to rename default f1, f2, f3... names when using row_to_json function for only some columns? I can do row_to_json(customers) returning ...
5
votes
1answer
966 views

Check what event called the trigger on PostgreSQL?

I'm trying to check what event called my trigger, like INSERT, UPDATE or DELETE. Oracle triggers can check this in a simple 'IF' statement: IF INSERTING, IF UPDATING or even IF DELETING Is there a ...
13
votes
4answers
6k views

Locking issue with concurrent DELETE / INSERT in PostgreSQL

This is pretty simple, but I'm baffled by what PG does (v9.0). We start with a simple table: CREATE TABLE test (id INT PRIMARY KEY); and a few rows: INSERT INTO TEST VALUES (1); INSERT INTO TEST ...
4
votes
1answer
301 views

Troubleshooting Postgresql spatial query failure. Next steps?

We have a set of spatial queries that are failing and I'm struggling with troubleshooting them. I suspect that we're running into some bug, but I'd like to nail things down a bit better so as to be ...
7
votes
1answer
1k views

Unexpected Seq Scan when doing query against boolean with value NULL

I have a database column called auto_review where column type is boolean. There is an index for that field, created using the ActiveRecord ORM. CREATE INDEX index_table_on_auto_renew ON table USING ...
8
votes
2answers
7k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
3
votes
1answer
120 views

query discrepancies on very large postgres table

I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem. these two queries although similar are taking massively ...
2
votes
2answers
876 views

PostgreSQL performance degradation over time on a write intensive db

I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the ...
3
votes
1answer
2k views

Ensure correct username when using pg_restore

I have just installed postgres 9.1.6 on a local Ubuntu server. Now I'm trying to restore a database dump from a database on Heroku. The local database is setup like this: sudo -u postgres psql -c ...
3
votes
1answer
723 views

High availability in postgresql

We will be switching from mysql cluster to either postgresql or oracle (with RAC). Mysql cluster is quite reliable, but lacks features in SQL that make programmers life easier (they consider it as ...
23
votes
2answers
6k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
5
votes
2answers
181 views

PostgreSQL: Query DB for rows containing integer[] > or < than specified value

I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less ...
0
votes
1answer
151 views

Postgres replication/replication to server outside firewall

I am looking for a way to have a postgresql database (running server version 9.1) replicated. I want the mirroring to be able to have one server (the slave) standing outside our firewall. I have been ...