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)

5
votes
3answers
2k views

Postgres Write Performance on Intel S3700 SSD

I'm not seeing the Postgres write performance increases I thought I would with a single SSD vs a hardware RAID 10 array of (16) 15k RPM SAS drives. I have a Dell R820 with a PERC H700 hardware RAID ...
5
votes
2answers
447 views

Custom unique column constraint, only enforced if one column has a specific value

Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is "true", then ...
5
votes
5answers
3k views

Improve sort performance in PostgreSQL?

I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
5
votes
5answers
2k views

Altering table schema takes too much time

I am using PostgreSQL database. I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
5
votes
3answers
1k views

How to pass a table type with an array field to a function in postgresql

i have a table called book CREATE TABLE book ( id smallint NOT NULL DEFAULT 0, bname text, btype text, bprices numeric(11,2)[], CONSTRAINT key PRIMARY KEY (id ) ) and a ...
5
votes
1answer
5k views

Scalability limitations of PostgreSQL and MySQL

I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB. I suspect that limits as such do exist as one would not come up with Netezza, ...
5
votes
2answers
567 views

Save postgres database from broken ubuntu 10.04 server

Recently a database server with an important db broke (some grub linux issue that I'd prefer not to solve). I can still access the filesystem. Is there a chance to transfer the database by only ...
5
votes
2answers
136 views

Indexing a large static dataset

My current situation is this: I have 328 million lines of data in a flat csv file. Not optimal. I want to be able to query this data (I'll explain in more detail). Grep is getting tired. This data is ...
5
votes
1answer
2k views

How to determine the collation of a table in PostgreSQL?

I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy ...
5
votes
2answers
155 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 ...
5
votes
2answers
2k views

About “Transaction ID Wraparound”

Now , I read the document about "Transaction ID Wraparound " , but there are something that I really don't understand, the document is the following url ...
5
votes
2answers
2k views

Should I use the PostgreSQL bit string?

I've been learning about the bit string data type lately, and I am quite curious about: At the bottom of this doc page there is the sentence: ... plus 5 or 8 bytes overhead depending on the ...
5
votes
2answers
5k views

Why did Postgres UPDATE take 39 hours?

I have a Postgres table with ~2.1 million rows. I ran the below update on it: WITH stops AS ( SELECT id, rank() OVER (ORDER BY offense_timestamp, defendant_dl, ...
5
votes
4answers
2k views

Trouble setting up Postgres for the first time

I have some, albeit limited, experience with MySQL and MongoDB (both running fine on server) but having trouble with Postgres and find the documentation lacking for newbies, like myself. Postgres is ...
5
votes
2answers
1k views

Single data type for imprecise date values, as allowed by ISO 8601

How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values? ISO 8601 allows date values with reduced precision. ‘1964’, ...
5
votes
2answers
3k views

upgrading from postgres 9.1 to 9.3 on ubuntu server

I have my production server(ubuntu 13.10) running with postgresql 9.1. I want to use few features of 9.3, hence want to upgrade. Could someone help me with upgrading from 9.1 to 9.3 so that there is ...
5
votes
1answer
375 views

What fillfactor for caching table?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
5
votes
3answers
14k views

Granting access to all tables for a user

I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
5
votes
3answers
663 views

“AT TIME ZONE” with zone name PostgreSQL bug?

I was answering this stackoverflow question and found strange result: select * from pg_timezone_names where name = 'Europe/Berlin' ; name | abbrev | utc_offset | is_dst ...
5
votes
1answer
3k 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 ...
5
votes
1answer
570 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 ...
5
votes
1answer
4k views

How to set Postgresql database to see date as “MDY” permanently

Ho can I set my database to see the 'date' as "MDY" without running: SET datestyle = "ISO, MDY"; every-time I'm trying to access it? I'm using Postgresql version 9.1, Ubuntu 12.04. My system ...
5
votes
1answer
2k views

PostgreSQL: Unable to run repmgr cloned database

I'm running tests with PostgreSQL hot standby with 1 master, and exactly 1 slave. I am using the instructions on this guide: ...
5
votes
2answers
4k views

Is there a simple way in PL/pgSQL to check if a query returned no result?

I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this: select c.data into data from doc c where c.doc_id = id and c.group_cur > ...
5
votes
1answer
171 views

If PostgreSQL starts after copying data, is the data ok?

I'm copying the data for a PostgreSQL database, that is, /var/lib/postgresql, from one server to another. The servers are identical except for the fact that one is 32bit and the other one is 64. I ...
5
votes
2answers
291 views

PostgreSQL, integer arrays, index for equality

I have a huge list of integer arrays (300,000,000 records) stored in Postgres 9.2 DB. I want to efficiently search these records for an exact match (equality only). I have heard of the intarray module ...
5
votes
1answer
218 views

PostgreSQL PREPARE query with IN () parameters

I'm trying to prepare a query from PHP like: pg_prepare($con, "prep", "select * from test where tid in ($1)"); and then execute it with: $strpar = "3,4,6,8,10"; pg_execute($con, "prep", ...
5
votes
2answers
2k views

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) ...
5
votes
1answer
1k views

Recovering a dropped table in PostgreSQL

I have : CREATE TABLE ketoan_vn.Customers ( CustomerId character varying(10) NOT NULL, CustomerName character varying(150), CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId) ); I ...
5
votes
1answer
435 views

How to customize constraint violation messages?

I want define my own message for each constraint. Is it possible to customize constraint violation messages in PostgreSQL?
5
votes
1answer
527 views

Difference between idx_tup_read and idx_tup_fetch on Postgres

On Postgres 8.4 when you do: select * from pg_stat_all_indexes where relname = 'table_name'; It returns the fields idx_tup_read and idx_tup_fetch, what is the difference?
5
votes
1answer
118 views

Upgrade postgres without messing with ports?

The standard procedure for upgrading Postgres appears to require that I temporarily run both the old and the new version. This means that I have to change the port of the old version. This seems ...
5
votes
2answers
1k views

PostgreSQL: Last access time to table

I am in charge of a large PostgreSQL database, with few dozen tables. I suspect that many of these tables are never accessed. What's the best way to check when was the last time a certain table was ...
5
votes
3answers
2k views

Postgresql row-level encryption

Looking over the docs, I didn't see any mention of row-level encryption. Lets assume I have a database of highly classified information that needs to get locked down. How can I implement a table with ...
5
votes
1answer
3k views

How to insert an IP-address into an inet column in PostgreSQL?

I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are ...
5
votes
3answers
643 views

How to keep an unique counter per row with PostgreSQL?

I need to keep an unique (per-row) revision number in a document_revisions table, where the revision number is scoped to a document, so it's not unique to the whole table, only to the related ...
5
votes
1answer
864 views

How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?

I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for ...
5
votes
1answer
2k views

Moving postgresql data to different drive

I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
5
votes
2answers
857 views

Span single transaction over several sequential psql sessions

I am running PostgreSQL 8.4 database server and psql terminal front-end on Ubuntu 10.04 Lucid Lynx and would like to span a single transaction over several sequential psql sessions. When I connect to ...
5
votes
2answers
1k views

Postgresql 8.3: Limit resource consumption per query

I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy. This particular version of PostGIS has a bug when calculating a buffer() on very complex segments, which causes ...
5
votes
2answers
406 views

Best practices to run time offset delayed replication

I want to setup replication system on postgresql like #1 MASTER SERVER and two replicated servers: #2 SERVER in realtime (for load balancing), #3 SERVER delayed with some time offset (for example 2 ...
5
votes
1answer
511 views

Get WAL files from AWS RDS PostgreSQL instance

We have a Postgres RDS instance on Amazon Web Services. We have automatic backups enabled, and we take snapshots on a daily basis. We would like to generate a local 'up-to-date' backup of the RDS ...
5
votes
1answer
965 views

How can I correctly choose maximum number of occurrences of a string while grouping by another field?

I am using Postgresql 9.0. I have the following fields in a table: id, name. id name 1 John 1 Mary 1 Mary 1 Mary 1 John 1 Mary 3 Paul 3 Paul 3 George ...
5
votes
5answers
5k views

SQL Query Formatter

Are there any (Linux based) SQL Query Formatting programs/plugins/extensions? I use PostgreSQL and MySQL but other DB's are welcome as well. I can use a VM to test with but would prefer a Linux ...
5
votes
2answers
2k views

How to wrap long lines when SELECTing SQL text columns?

I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length. From: SELECT * FROM test; test_id | text ...
5
votes
1answer
810 views

How to reclaim disk space on PostgreSQL?

I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued delete from command to delete all records from it (I ...
5
votes
1answer
56 views

How to see amount of reusable space for a table

I had a huge table(about 300mil rows). After massive DELETE and VACCUUM table files have stopped to grow. That's exactly what I expected. But is there any way to see how much space became reusable?
5
votes
1answer
353 views

Optimizing PostgreSQL for transient data

I have several tables with 100-300 columns of integer types each, that hold highly volatile data. The datasets are keyed by one or two primary keys, and when refresh occurs, the whole dataset is ...
5
votes
1answer
619 views

PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly

We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same ...
5
votes
1answer
97 views

How to tell if database tuning is fruitful (PostgreSQL)

As a DBA, I regularly observe my database, tune up some queries (either getting the programmers to re-write it or add index or update statistics if that's what is needed, etc). I also tune up the ...