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
0answers
122 views

Identical Postgres index scan taking 5x longer on server

I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject: Column | Type | Modifiers ...
38
votes
3answers
62k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
1
vote
1answer
115 views

Hiding variable input in psql

I am attempting to prompt for a username and password when running a database creation script, and I want to hide any text input for my password prompt. In Oracle's sqlplus, I can use HIDE at the end ...
4
votes
1answer
2k views

How to fine-tune Postgres for IO usage?

I have a Postgres database I am hosting in a quad core server with 8 GB of RAM. At any time, there can be 100 or so reads and writes occurring concurrently. I am experiencing high IO wait time. When ...
1
vote
4answers
6k views

How to change the data directory on Postgresql 9.1 on Ubuntu

I am having a problem changing the data directory on postgresql 9.1 on ubuntu 13.04: I first did a: sudo pg_dropcluster --stop 9.1 main and then attempted to create a new one in the desired path: ...
1
vote
1answer
1k views

Why does pgAdmin3 set OIDS=FALSE and owner to postgres by default?

Why does pgAdmin3 automatically set the table owner to postgres when its created and set OIDS=FALSE? I am new to PostgreSQL and want to know the rational behind this default behavior. -- Table: ...
3
votes
2answers
448 views

How to implement posts with “seen by” like facebook?

Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me. And if I see a specific post inside a group thought a url ...
4
votes
1answer
470 views

Streaming Replication Failover - how to point second slave at new master?

I have a Postgres database HA cluster I've set up in a lab. I'm using PgPool-II to manage fail-over. For the cluster itself, I have three identical Postgres 9.2 servers set up like so: Server A - ...
2
votes
2answers
15k views

Use CASE to select columns in UPDATE query?

I can use CASE to choose which columns to display in a SELECT query (Postgres), like so: SELECT CASE WHEN val = 0 THEN column_x WHEN val = 1 THEN column_y ELSE 0 END AS ...
0
votes
1answer
231 views

Postgres sql dump import error

I'm facing a issue while importing the dump in my system, i'm getting the error as " Error: syntax error at or near "" LOCK TABLESDB_NAME` WRITE;" and exiting with "out of memory" We had ...
3
votes
1answer
1k views

In Postgres, How can I merge two tables?

I have a set of web services that all need updating with a new set of static data. I can't use a distributed transaction, but would like to be able to ask each service to rollback it's changes if I ...
8
votes
1answer
2k views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
5
votes
1answer
4k views

Could too many idle connections affect PostgreSQL 9.2 performance?

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
1
vote
3answers
831 views

PostgreSQL newbie - how to create objects in a database

I've just finished installing PostgreSQL and pgadmin3 on Ubuntu. Using pgadmin, I've been able to create a new user called 'test' and also a new database with 'test' as the owner. Now I'm trying to ...
4
votes
3answers
1k views

Is a REINDEX required after CLUSTER?

I'm considering using CLUSTER to reorder a table by an index. I understand that this recreation of the table data makes all the existing indexes either bloat or be useless. I've seen some indications ...
11
votes
3answers
15k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
2
votes
1answer
256 views

PGPool Frequent Queries

We recently setup a PostgreSQL Cluster with PGPool in front of it. This is our first go at such a thing. We setup our QA environment to use the new PGPool, and we cranked up the logging on the ...
3
votes
2answers
72 views

disallow SET command in a postgresql server

Im planning to publish my postgresql server to a few untrusted clients. I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the ...
1
vote
1answer
64 views

POSTGRES DB Server Blunder

This weekend I had to rebuild my laptop which had Postgres running. For some reason, I forgot to dump the data but I did make a complete copy of the original hard drive. Is it possible to recover ...
0
votes
3answers
710 views

Incremental backup in postgresql

I want to migrate a production database from one linux server to other. I am not concern about upgrading my database version, just I want to move the database from one server to other with minimum ...
5
votes
2answers
2k views

Transactions, references and how to enforce double entry bookkeeping? (PG)

Double entry bookkeeping is a set of rules for recording financial information in a financial accounting system in which every transaction or event changes at least two different nominal ...
3
votes
1answer
529 views

How to turn off all RAISE NOTICE on production server?

I put a lot of RAISE NOTICE in functions for debugging purpose. I think this will affect performance, so they should be removed on production environment. Is there any way to opt-out those ...
6
votes
2answers
392 views

Postgres performance difference on insert

I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions? I went quite straight forward way (i know that it is not ...
0
votes
1answer
249 views

Object-oriented programming CRUD design pattern

At first I have posted this question at http://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern - that's because I think it's a subect question, and it can ...
1
vote
1answer
751 views

How to grant only functions while revoking tables?

I am a newbie to PostgreSQL, and configuring a new database. I wrote several functions for indirect access(read/write) to data, and I want to prohibit all direct access to table. GRANT CONNECT ON ...
0
votes
1answer
348 views

Postgres 9.1 wait events

Is there anything in Postgresql 9.1 similar to wait events dynamic views of oracle? I need to find queries which are waiting for a long time, and the events they are blocked on.
1
vote
1answer
158 views

Write performance of Postgresql 9.1 with read-only slave

I have a Postgresql 9.1 database that is being hosted by Heroku. It currently has a read-only "follower". I need to truncate several large tables (over 100GB) of data and reloads them. Is the ...
1
vote
1answer
2k views

How I can copy from local file to remote DB in PostgreSQL? [closed]

I am a novice in psql and need some help. How can I load a local CSV to a remote DB? I am using the following command \COPY test(user_id, product_id, value) FROM '/Users/testuser/test.tsv' ...
0
votes
1answer
138 views

Comparing two tables for a UUID change and fix it

I have two tables which I'm trying to reconcile the differences of in postgresql. Table A is old and needs updating. Table B is an updated, schema identical version of Table A which I have the data ...
1
vote
1answer
520 views

Postgresql querying trends

Firstly apologies if this is a duplicate, I am fairly new to SQL and so Im not sure what the correct terminology to use in my searches So I have a database which records motor races, with the ...
0
votes
1answer
932 views

Set field values to newly imported rows in PostgreSQL table with existing data

I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import. Question: For the newly imported ...
4
votes
2answers
2k views

Inner join using an array column

Having trouble indexing and executing a query in O (log n) time. The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
2
votes
0answers
238 views

How to execute a non-table-locking update operation on PostgreSQL? [closed]

Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
3
votes
1answer
532 views

Most efficient to prevent duplicates

I'm building an application that will fetch messages with JSON every once in a while and store these in a postgres db. Now I want to prevent duplicates, so I calculate the sha-512 of the message and ...
1
vote
1answer
111 views

Big jump in search time for Postgres Index query for results with high selectivity

I am doing some performance comparison of databases and lucene for full-text searching. So I use Postgres to create an Index for the data to search: CREATE INDEX bodies_index ON bodies USING gin ...
2
votes
1answer
2k views

Limiting number of results in a Partition using OVER(PARTITION BY)

In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the ...
0
votes
1answer
74 views

Very fast replication

I used this guide to make replication between my two db servers : http://wiki.postgresql.org/wiki/Streaming_Replication But, when I updated a row on master server it will be available in slave ...
1
vote
1answer
1k views

which postgres do i have installed? what directory needs to be in my path to run initdb?

I need to use postgresql with ruby on rails. I have these directories /Library/PostgreSQL and /usr/local/Cellar/postgresql/usr:local:Cellar:postgresql:9.2.2 This 2nd directory name is ...
0
votes
1answer
853 views

Getting error while importing large data through csv

I am new Postgres. I am getting constraint violation errors while importing a large CSV file. I want to disable all of the database constraints temporarily - I do not know how to do this.
1
vote
1answer
3k views

How to use array variable in query in PostgreSQL

Create table t1 ( xcheck varchar[], name text ); CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[]) RETURNS record AS DECLARE xrc as record; execute 'select name from t1 where xcheck @> ...
1
vote
1answer
62 views

Can pg_hba.conf trust processes?

Is it possible to configure PostgreSQL such that it can trust a single process, instead of a specific user?
4
votes
2answers
3k views

Postgres: Use result of group by as column names

I have a messages table in a database, which include a sender id and a message type (and of course many more columns not relevant for this question). I try to create a query which counts how many ...
2
votes
1answer
61 views

What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?

Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this? SELECT id FROM table WHERE table.int_col > 1; I'm specifically interested in ...
14
votes
1answer
5k views

Using MongoDB and PostgreSQL together

My current project is essentially a run of the mill document management system. That said, there are some wrinkles (surprise, surprise). While some of the wrinkles are fairly specific to the ...
5
votes
1answer
480 views

How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
0
votes
1answer
581 views

Passing argument in trigger dynamically

CREATE TRIGGER audit_proc_tr AFTER INSERT OR UPDATE OR DELETE ON "log".hi FOR EACH ROW EXECUTE PROCEDURE "log".audit_proc(argument); CREATE OR REPLACE FUNCTION ...
3
votes
1answer
118 views

Postgres caching and bytea

I have a DB which, among other things, stores images (as bytea, if that's interesting). It also stores users' metadata, activity logs, etc., but images currently take 500MB out of the total 600MB ...
2
votes
1answer
276 views

REST webservice as a Foreign Data Wrapper in postgresql

I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find ...
2
votes
1answer
1k views

How to make postgresql SUM to be more accurate on large amount of floating point data?

I'm trying to SUM 8 million floating point (REAL) values with simple query like this: SELECT SUM(metric) FROM metrics; However, it returns very inaccurate result. It should return 137,586.77, but ...
0
votes
0answers
137 views

PostgreSQL : Londiste issue

I am new to Londiste, And I want to install Londiste, accroding the Londiste Tutorial after I intall skytool (http://pgfoundry.org/frs/?group_id=1000206) , I can not find the pgqadm.py script , ...