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)

1
vote
1answer
266 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
559 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
148 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
234 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 ...
12
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
925 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
5k 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
298 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
989 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
119 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
856 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
706 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
177 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
149 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 ...
3
votes
1answer
931 views

How to wait between PostgreSQL DB restore and reindexing?

I've written a script to upgrade PostgreSQL from 8.2.1 to 9.2.1 on a Linux From Scratch distribution: Start the pg service Vacuum all DBs (not sure if this is needed) Backup with pg_dumpall Stop the ...
1
vote
1answer
602 views

PostgreSQL - How can I manage WAL archiving for different databases on a server?

I'd like to use WAL archiving for incremental backup of my database. But I have 2 databases on my server, and I can't afford the disc space for backup of both of them. So I only want to backup one of ...
7
votes
1answer
617 views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
4
votes
1answer
3k views

Transform all columns records to lowercase

I'm using PostgreSQL 9.1 and I have a users table with a login column. login names are case-sensitive, for example Bob, MikE, john. I would like to transform all these records into lowercase. ...
1
vote
1answer
428 views

Error creating index on PostgreSQL 9.1

I have a process where user A creates a table and user B tries to create an index on the new table, owned by A. Users A and B are from the same group but user B is an automated process. Is it ...
3
votes
1answer
112 views

Storing application settings with different datatypes

I'm developing a web application and I plan to store application settings in the database in name,value fields (one row = one setting). These settings range from directory paths to simple yes/no ...
10
votes
1answer
3k views

How to preserve the original order of elements in an unnested array?

Given the string: 'I think that PostgreSQL is nifty' I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details ...
2
votes
2answers
8k views

PostgreSQL not working, pgAdmin always says server not listening

I am brand new at this so maybe it's a simple mistake. I was given a very large .sql file with the instructions to make a copy of a database locally on a computer. I was told "to be able to install ...
0
votes
1answer
506 views

postgresql 9.1 Multiple Cluster on same host

I have 2 cluster databases, running on the same host, Ubuntu. My fist database port is set to default but my second database port is set to 5433 in the postgresql.conf file. While everything is ok ...
0
votes
1answer
134 views

Can't access Brew PostgreSQL

I've used Brew to install PostgreSQL. I want to log in with psql. Since I haven't set a password I 'm trying to login without one. When I try I get this: psql: fe_sendauth: no password supplied I ...
11
votes
5answers
10k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
1
vote
2answers
4k views

Creating High Availability Cluster with PostgreSQL

I am new to database design. I have a task to create a high-availability cluster. I Googled a lot, however I haven't found any good way to do this on PostgreSQL. Where should I start and what are ...
3
votes
1answer
434 views

Model with variable number of “properties” of different types.

I would like to have a table of microscope slides, where on top of the basic id, name,date_created would each slide also have a number of editable "properties". Imagine one slide having ...
4
votes
2answers
618 views

How to wait for PostgreSQL to be startable / restorable?

I'm testing a PostgreSQL 8.2.1 to 9.2 upgrade on a virtual machine running a custom Linux distro. The upgrade procedure is as follows: Start the pg service Vacuum all DBs (not sure if this is ...
2
votes
2answers
391 views

How can I 'fake' stats for generate_series data?

With 9.1 and earlier, using generate_series in the select clause does not increase the expected row count (but of course does increase the actual row count), leading to possible poor planning: ...
0
votes
1answer
3k views

Pgpool install - libpq is not installed or libpq is old

Based on the documentation here, I want to create a pgpool II with PostgreSQL databases. When I try to install, I encounter this error: configure: error: libpq is not installed or libpq is old ...
1
vote
1answer
790 views

Recommendations for PostgreSQL training [closed]

I'm looking for recommendations for PostgreSQL training (on-site, off-site, remote) that covers administration, performance optimization, and clustering solutions like Slony and pgpool. Finding ...
2
votes
1answer
1k views

Getting a random row from PostgreSQL?

I want to get a random row from my table by id. My table: ID|Word |Dificult|Category_id| 1 |'Dumb' |'Easy' | 3 | 2 |'Leopard'|'Medium'| 6 | If my user selects a category and ...
1
vote
1answer
362 views

When using a master plus multiple slaves, how do I change over from one server to a brand new one with minimal downtime?

In my particular case, I'm using Heroku Postgres (repackaged Amazon EC2 instances running PostgreSQL), but platform-agnostic answers are also good! Heroku's instructions describe the process for ...
3
votes
2answers
1k views

How do I implement insert-if-not-found for transactions at serializable isolation level?

I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following. We have a table called artist with 2 columns, (name, id) where name is the ...
2
votes
1answer
128 views

Does updating a partially indexed field add or remove it from an index in Postgres

We can create a partial index for a field in Postgres. I am looking at using this to stop rows which are archived from being in an index on a created_at field: First we create the index: test=# ...
3
votes
1answer
2k views

How can I create a dblink from Postgresql 8.3 to SQL Server (2000 and 2008)?

I have a Postgresql database that need to read data from two SQL Servers (one SQL Server 2000 and one SQL Server 2008). I want to setup two dblinks to the SQL Servers. After googling, foruming, ...
3
votes
1answer
256 views

Why does PostgreSQL aggregate result in the Index or Seq scan outputting all columns of the table?

Doing a query like this: SELECT fruit, avg(cost) FROM fruit_table GROUP BY fruit results in a query plan like this: Sort (cost=3713873.41..3713874.46 rows=420 width=16) Output: fruit, ...
8
votes
2answers
581 views

High Availability for postgresql

I'm new in PostgreSQL database. Recently our developer needed to do some upgrades in our systems. Because of that we are planning to implement some method in order to implement database failover. ...
2
votes
1answer
103 views

Optimising query on view that merges similar tables with a clear discriminator

Using PostgreSQL 8.4, I have a number of tables that have a very similar structure, but that belong to different categories: CREATE TABLE table_a ( id SERIAL PRIMARY KEY, event_time TIMESTAMP ...
5
votes
2answers
3k views

Proper storage size estimation technique for PostgreSQL

We are preparing a PostgreSQL database for production usage and we need to estimate the storage size for this database. We are a team of developers with low expertise on database administration, so we ...
8
votes
2answers
2k views

PostgreSQL Index Caching

I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions: PostgreSQL indexes, like rows, live on disk ...
4
votes
1answer
518 views

PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED

I am using PostgreSQL 9.1.5 and the psql to run a scipt containing a series of INSERT statements in various tables: psql -U usernamefoo databasenamefoo -f dml_script.sql The table constraints have ...
2
votes
2answers
1k views

Select rows with repeat data within certain timeframe

As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
3
votes
2answers
549 views

Is the speed of a PostgreSQL SELECT adversely affected by too many indexes on the table?

I have read that when having a lot of indexes on a database It can seriously hurt the performance but in the PostgreSQL doc I can't find anything about it. I have a very big table with something like ...
2
votes
2answers
2k views

How to execute a query from psql without waiting for the result?

My query (to create a new table from existing table) takes a very long time. So I've set up a remote database in my office - more RAM there. I can connect to my database from home as usual with psql. ...
-1
votes
1answer
844 views

Insert a 'null' string in a table in postgresql [closed]

I would like to know if it is possible to insert a 'null' string in a column which has NOT NULL constraint. I'm using postgresql. The query would look like the below INSERT INTO ...
2
votes
2answers
200 views

Teaching Slony replication to select slave nodes

There is a system with up to 256 nodes in db cluster. We need to replicate some data from one of them across to all others. But for every row to replicate there is a list of nodes where it must be ...