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)

8
votes
2answers
2k views

Select longest continuous sequence

I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field. Consider the following table: lap_id (Serial), lap_no (int), car_type ...
8
votes
2answers
34k views

How to get the timestamp column in only milliseconds from PostgreSQL?

I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database. If I select colums, it has a nice and readable format per default: SELECT created FROM ...
8
votes
3answers
110 views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | ...
8
votes
3answers
1k views

Email address unique or primary key?

I am a newbie in databases. I read around and found out that it's probably not a great idea to use email address as primary key because string comparisons are slower which effects performance in ...
8
votes
1answer
5k views

disable constraints before using pg_restore.exe

When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same: ERROR: insert or update on table "someTable" violates foreign key constraint ...
8
votes
3answers
183 views

Is it reasonable to mark all columns but one as primary key?

I have a table representing movies. The fields are: id (PK), title, genre, runtime, released_in, tags, origin, downloads. My database cannot be polluted by duplicated rows, so I want to enforce ...
8
votes
1answer
4k views

Fast general method to calculate percentiles

I want to find n>1 percentiles of an unsorted column in PostgreSQL. For example the 20th, 40th, 60th, 80th and 100th percentile. An obvious solution is to count and sort the column and then do a ...
8
votes
2answers
680 views

Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting

While writing a query other day a thought came to me and have stuck in my mind. What is preferable, first checking if a value for a unique column exists and then inserting or insert and let db raise ...
8
votes
2answers
7k views

Is REINDEX dangerous?

I've been trying to COUNT(*) a table with 150,000 rows that has a Primary key. It tool about 5 minutes, so I figured out this is an indexing problem. Citing the PostgreSQL manual: REINDEX is ...
8
votes
1answer
87 views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
8
votes
1answer
1k views

BLOBs or references in PostgreSQL

I need to store binary data files in a PostgreSQL database which runs on an Ubuntu server. Initially there will be a few dozen files of approximately 250kb in size each. However, the number of files ...
8
votes
1answer
1k views

What happens in PostgreSQL checkpoint?

Here's part of my checkpoint log: 2014-03-26 11:51:29.341 CDT,,,18682,,532854fc.48fa,4985,,2014-03-18 09:15:24 CDT,,0,LOG,00000,"checkpoint complete: wrote 15047 buffers (1.4%); 0 transaction log ...
8
votes
2answers
652 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. ...
8
votes
2answers
10k views

How can I specify the position for a new column in PostgreSQL?

If I have a table with the columns: id | name | created_date and would like to add a column, I use: alter table my_table add column email varchar(255) Then the column is added after the ...
8
votes
1answer
2k views

PostgreSQL pg_stat_activity shows COMMIT

We recently replaced our database server with an upgraded machine with 4 x quad core CPUs and 32Gb of ram. We also repurposed our old box to serve as a slave with streaming replication. Both boxes are ...
8
votes
2answers
171 views

Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

I have created the table donor in the schema reference as per: CREATE TABLE reference.donor ( donor_code smallint PRIMARY KEY, donor_name character varying NOT NULL, donor_type smallint ...
8
votes
1answer
12k views

Usage of COPY FROM STDIN in postgres

I just started using Postgres and I'm trying to create a sample db to understand its functions, looking around, I found some scripts in pgfoundry.org. I understand the commands, since I previously ...
8
votes
1answer
3k 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 ...
8
votes
2answers
4k views

Can I have one table be an “alias”, or “symlink”, to another?

I have two tables with the same structure, A and B. A certain application is written so that it always writes the same data to both tables. After a discussion with a colleague about the potential to ...
8
votes
3answers
14k views

PostgreSQL 9.1 Hot Backup Error: the database system is starting up

I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue. After restarting Postgres on the slave server the pgstartup log file and the daily log file under ...
8
votes
2answers
3k views

View Postgresql Memory Usage

I'm running Postgresql on a Ubuntu server and need to be able to monitor its memory usage. Currently I have scripts running in a one-minute cron job that monitor/log various statistics and need to ...
8
votes
2answers
435 views

Biological Sequences of UniProt in PostgreSQL

What is the best way to store UniProt biological sequences in PostreSQL? Data Details We pull in 12 million sequences from UniProt - this number is likely to double every 3-10 month. The length of ...
8
votes
1answer
8k views

MongoDB performance vs. PostgreSQL with 5.5 million rows / documents

Can someone help me compare these queries and explain why the PostgreSQL query executes in just under 2000ms and the MongoDB aggregate query takes almost 9000ms and sometimes as high as 130K ms? ...
8
votes
1answer
5k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
8
votes
2answers
162 views

Running a complex query for every date in a range

I've got a table of orders Column | Type | Modifiers ...
8
votes
1answer
32 views

When are privileges listed in \l and when not?

When are access privileges listed by \l, and when are they not? The access privileges listed by \l can change after a grant and revoke: $ createuser -EP my_readonly $ psql development development=# ...
8
votes
1answer
201 views

unable to insert row at id, but row does not exist

Here is the weird problem i am facing. I am trying to enter data using following query insert into product_product (id, product_tmpl_id, make_equip, model_equip, name_template, serial_num_equip, ...
8
votes
2answers
3k views

Can PostgreSQL create a case sensitive database?

I'm porting some code from some version of Sybase to PostgreSQL. This is a C application that uses the Sybase client library. My approach is to write a translation layer that translates calls to ...
8
votes
1answer
680 views

Fast hamming distance queries in postgres

I have a large database (16M rows) containing perceptual hashes of images. I'd like to be able to search for rows by hamming distance in a reasonable timeframe. Currently, as far as I properly ...
8
votes
2answers
2k views

Migrate heroku database to Amazon RDS with minimum downtime

I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then ...
8
votes
2answers
7k views

PostgreSQL for high volume transactions and for Data warehousing

Am quite new to PostgreSQL, I have never done a large deployment using it before. But, I have good experience in enterprise solutions and I want to try and apply some of what I learned using ...
8
votes
1answer
1k views

How do I stop/disable PITR and safely clean WAL segments?

Our production server is running PostgreSQL v8.2.3 on CentOS release 5.2 (Final). We've setup PITR in our production server. For some reasons, after setting up PITR, we're not able to manage and ...
8
votes
1answer
179 views

How do data checksums interact with streaming replication?

Data checksums are a new feature introduced in 9.3, and: there is a new GUC parameter "ignore_checksum_failure" which will force PostgreSQL to continue processing a transaction even if corruption ...
8
votes
1answer
444 views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
8
votes
0answers
281 views

Modification to GEQO (Genetic Query Optimization) of PostgreSQL

I need to implement a functionality that is in line with the GEQO functionality of PostgreSQL. I understand that GEQO approach is to encode query plans as integer strings and GEQO generates these ...
7
votes
5answers
5k views

SELECT DISTINCT on multiple columns

Supposing we have a table with four columns (a,b,c,d) of the same data type. Is it possible to select all distinct values within the data in the columns and return them as a single column or do I ...
7
votes
5answers
2k views

Are there any professional fulltime PostgreSQL DBA's?

For my job I work on a JavaEE application with PostgreSQL as the database. Although we have a sysadmin for our productions servers, who also manages our database servers, we have no full-time DBA ...
7
votes
3answers
6k views

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column?
7
votes
3answers
9k views

Set a column's default value to the concatenation of two other columns' values?

I have a Postgresql 8.1 database. In one table, there are three columns: first_name, last_name, display_name. Is it possible to set the default value of display_name to be first_name + " " + ...
7
votes
3answers
6k views

Checking whether two tables have identical content in PostgreSQL

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table. A PostgreSQL ...
7
votes
4answers
1k views

Should I invest the time to change the column type from CHAR(36) to UUID?

I have a few million rows in my database already. I didn't know about the PostgreSQL UUID data type when I designed my schema. One of the tables has 16M rows (about 3.5M to 4 M records per shard), ...
7
votes
2answers
1k views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
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 ...
7
votes
1answer
6k views

Starting PostgreSQL server after a HDD crash results in FAILED STATE

I am using Fedora 15 with PostgreSQL 9.1.4. Fedora crashed recently after which: An attempt to start the PostgreSQL server : service postgresql-9.1 start gives Starting postgresql-9.1 (via ...
7
votes
5answers
15k views

PostgreSQL: The database cluster initialization failed

Both C:\PostgreSQL and C:\PostgreSQL\data have postgres user with full access and admin rights. I ran postgresql-9.1.2-1-windows.exe from postgres user as admin. Target C:\PostgreSQL Every way I ...
7
votes
2answers
5k views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
7
votes
1answer
667 views

Concept of Schema in PostgreSQL

I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design. Why should I use it? Can it affect me in future if I decide to not think ...
7
votes
4answers
4k views

PostgreSQL: Creation date of a table

I've recently finished a project, during which many DB tables were created. Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables. Is there a ...
7
votes
1answer
1k 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", ...
7
votes
1answer
3k views

Algorithm for finding the longest prefix

I have two tables. First one is a table with prefixes code name price 343 ek1 10 3435 nt 4 3432 ek2 2 Second is call records with phone numbers number time 834353212 10 ...