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)

10
votes
4answers
7k views

PostgreSQL designing tool [closed]

I am about to design a database which is going to run with PostgreSQL. I am used to the magnificent tool called MySQL Workbench for MySQL database. It is useful and it looks good which I kind of ...
10
votes
2answers
12k views

How to turn json array into postgres array?

I have a field user that holds a json document that roughly looks like that: { name: 'foo', tags: ['foo', 'bar'] } What I would like to do is to turn that tags array into a concatenated ...
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 ...
10
votes
2answers
5k views

PostgreSQL vs. MySQL - Advantages / Disadvantages with a spatial component

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
10
votes
1answer
5k views

How to migrate large blob table from mysql to postgresql?

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
10
votes
2answers
2k views

PostgreSQL: Can I do pg_start_backup() on a live, running db under load?

Our established replication has broken ("requested WAL segment has already been removed" during downtime) We cannot easily stop the master again. Can we do pg_start_backup(), rsync ${PGDATA}/ ...
10
votes
3answers
2k views

PostgreSQL Initial Database Size

There are 2 parts to my question. Is there a way of specifying the initial size of a database in PostgreSQL? If there isn't, how do you deal with fragmentation when the database grows over time? ...
10
votes
1answer
2k views

Generate an exception with a Context

When PostgreSQL throws an exception, there is a line "CONTEXT" like: ERROR: INSERT has more target COLUMNS than expressions LINE 3: ... ...
10
votes
4answers
5k views

Tools to migrate from SQLite to PostgreSQL

I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the ...
10
votes
1answer
557 views

Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
10
votes
1answer
419 views

Disk file effects of delete and vacuum

I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk ...
9
votes
2answers
13k views

How to view data in pgAdmin III

I can't believe they make it this hard. I am at a loss about how to view the data in my database. Is there an easy way to see what data is in my tables with pgAdmin III? Alternatively, is there a ...
9
votes
3answers
7k 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, ...
9
votes
1answer
3k views

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
9
votes
2answers
13k views

Permission denied in Postgres

I added the user myuserto Postgres. Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres. Then I tried to ...
9
votes
3answers
1k views

Where does the magic column “name” come from?

I got this by accident: db=> select name from site; ERROR: column "name" does not exist LINE 1: select name from site; ^ db=> select site.name from site; name ...
9
votes
1answer
6k views

postgresql: how do I dump and restore roles for a cluster?

Where are roles stored in a cluster, and how do I dump them? I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors: psql:mydump.sql:3621: ERROR: role ...
9
votes
1answer
1k views

What is the difference between PostgreSQL 9.0 Replication and Slony-I?

Is there still any use for Slony-I, and if so, what is it? For clarification, from 9.0 Postgresql supports built in streaming replication.
9
votes
2answers
103 views

How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

I understand that a trigger on table t defined with FOR EACH STATEMENT will run once when I execute an UPDATE t .... Now, when t is defined with FOREIGN KEY ... REFERENCES a ... ON UPDATE CASCADE, ...
9
votes
1answer
502 views

Why is SELECT * much faster than selecting all columns (in a different column order) by name?

On a table with columns a,b,c,d,e,f,g,h,i,j,k I get: select * from misty order by a limit 25; Time: 302.068 ms And: select c,b,j,k,a,d,i,g,f,e,h from misty order by a limit 25; Time: 1258.451 ms ...
9
votes
3answers
7k views

How to convert mysql to postgresql? [closed]

I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be: Free Works under Linux Simple to use and not buggy You actually tried and ...
9
votes
1answer
4k views

Can listen_addresses really be set to a list?

I have a VM with IP address 192.168.0.192 running postgreSQL. If I specify listen_addresses = '*' then I can connect from another VM at 192.168.0.191 and from localhost. But I can't seem to use a ...
9
votes
3answers
28k views

How to solve UTF8 invalid byte sequence copy errors on a restore, when the source database is encoded in UTF8?

I was given the task to migrate a PostgreSQL 8.2.x database to another server. To do this I'm using the pgAdmin 1.12.2 (on Ubuntu 11.04 by the way) and using the Backup and Restore using the ...
9
votes
1answer
393 views

Why are the plans different if the queries are logically alike?

I wrote two functions to answer Day 3's first homework question from Seven Databases in Seven Weeks. Create a stored procedure where you can input a movie title or actor's name you like, and it ...
9
votes
3answers
390 views

How-to implement an entity with an unknown maximum number of attributes?

I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
9
votes
1answer
350 views

How do I 'hint' the cardinality of a recursive CTE?

I'm using the following recursive CTE as a minimal example, but in general, the optimizer has to use default 'guessed' cardinalities for recursive CTEs: with recursive w(n) as ( select 1 union all ...
9
votes
1answer
2k views

Why would you index text_pattern_ops on a text column?

Today Seven Databases in Seven Weeks introduced me to per-operator indexes. You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as ...
9
votes
2answers
9k 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) ...
9
votes
2answers
3k views

Load balancer versus connection pooling — is there a difference?

I am working on a project that is expected to serve millions of users shortly after launch. The database is postgres, and for now I am assuming at least two servers will be needed. A sys admin (who is ...
9
votes
6answers
7k views

SQL Query Formatter [closed]

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 ...
9
votes
1answer
6k views

PostgreSQL Database Data File Integrity Check

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption? In the ...
9
votes
1answer
283 views

Busy table is not getting vacuumed

We're using Postgres 9.2 on Windows to store low-frequency timeseries data: we're inserting around 2000 rows per second every second 24 hours, 7 days a week with no downtime. There is a DELETE that ...
9
votes
0answers
269 views

How to handle bad query plan caused by exact equality on range type?

I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
8
votes
3answers
62k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
8
votes
6answers
5k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
8
votes
1answer
2k views

Is PostgreSQL replication production ready?

How does PostgreSQL native replication compare to MySQL? I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects? ...
8
votes
2answers
8k 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 ...
8
votes
3answers
529 views

What is retrieved from disk during a query?

Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google... Do the number of columns in a particular table affect the performance of a query, ...
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 ...
8
votes
4answers
2k views

How to check if a subquery has exactly one distinct result and a specified value concisely?

I found myself writing the following: select 'yes' where exists(select * from foo where val=1) and not exists(select * from foo where val<>1); and wondering if there is a more concise way ...
8
votes
2answers
32k 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
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
36k views

How do I reset the postgres password for PostgreSQL on Windows?

I have installed PostgreSQL on my Windows machine for development. Now I haven't used it for a while, and have forgotten the password for user postgres (default admin user in PostgreSQL). How can I ...
8
votes
2answers
657 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
6k 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
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
2answers
617 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
1answer
3k views

Foreign key constraint on array member?

Suppose I have a table containing job roles: CREATE TABLE roles ( "role" character varying(80) NOT NULL, CONSTRAINT "role" PRIMARY KEY (role) ); Suppose I further have a table, users, and each ...
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 ...
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 ...