All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

10
votes
1answer
605 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 ...
10
votes
2answers
6k 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
3answers
4k views

VACUUM FREEZE vs. VACUUM FULL

Can someone explain the difference between these types of VACUUM in PostgreSQL? I read the doc but it just says that FULL locks the tables and FREEZE "freezes" the tuples. I think that's the same. Am ...
10
votes
2answers
15k 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) ...
10
votes
2answers
4k 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 ...
10
votes
1answer
3k 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 ...
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
16k 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 ...
10
votes
6answers
10k 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 ...
10
votes
2answers
5k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

http://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works ...
10
votes
2answers
462 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 ...
10
votes
1answer
8k 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 ...
10
votes
1answer
15k views

PostgreSQL: permission denied for relation

I'm a bit confused about setting permissions in PostgreSQL. I have these roles: List of roles Role name | Attributes | Member of ...
10
votes
1answer
183 views

Why is query thousands of times slower with DESC?

I have a PostgreSQL database (AWS RDS) and ran the following queries in psql: livenet=> EXPLAIN ANALYZE SELECT * FROM outputs ORDER BY height LIMIT 10; Limit (cost=0.57..36.82 rows=10 ...
10
votes
1answer
680 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
874 views

Avoiding “Row by row” fetch method when dealing with source LOB columns

I have a legacy PostgreSQL database source (ODBC) that I'm attempting to migrate to new SQL Server schema using SSIS. I'm getting a warning saying: 'Row by Row' fetch method is enforced because ...
9
votes
6answers
8k 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 ...
9
votes
4answers
2k 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), ...
9
votes
2answers
7k views

Multicolumn index and performance

I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries. The scenario: PostgreSQL 8.4, table with about one ...
9
votes
3answers
8k 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
6k views

How to duplicate huge postgres table?

I have huge postgres table (10GB of data - 160M records). Table is static and there are no write operations on it performed. I want to duplicate it, perform writes, reindex it and then with single ...
9
votes
7answers
26k views

Why won't PostgreSQL 9.3 start on Ubuntu?

I have successfully installed PostgreSQL 9.3 from the APT repository on 2 VM's running Ubuntu 12.04 and 13.04...however, I cannot get it to install properly on my host machine running Ubuntu 12.04. ...
9
votes
2answers
10k views

Why is a new user allowed to create a table?

I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core: postgres=# \l List of ...
9
votes
4answers
3k 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 ...
9
votes
2answers
12k 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 > ...
9
votes
2answers
9k views

Preventing PostgreSQL from starting on boot in Ubuntu

I am using PostgreSQL 9.2 and it runs automatically when I start the system (Kubuntu). How can I avoid this? I need to start it manually, and I don't want it to start unattended because I need to ...
9
votes
2answers
5k 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 ...
9
votes
4answers
5k views

Finding empty columns of a table in PostgreSQL

What query would return the name of the columns of a table where all rows are NULL?
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
386 views

What is the overhead for varchar(n)?

I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type: The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which ...
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
1answer
2k views

EC2 - How to correctly back up PostgreSQL data?

Here is the setup: 1 small Amazon Linux (EBS-backed) EC2 instance with 3 additional volumes. This is both a web server and database server. One volume for code, one for the PostgreSQL (8.4) data ...
9
votes
1answer
46k 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 ...
9
votes
1answer
7k 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 ...
9
votes
4answers
4k views

Determine when a PostgreSQL database was last changed

I'm looking at altering how backups are done and am wondering if there is a way to determine which databases in a postgreql cluster have not been recently changed? Instead of using pg_dumpall, I'd ...
9
votes
1answer
3k 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 ...
9
votes
3answers
411 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
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 ...
9
votes
1answer
419 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
3answers
17k 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 ...
9
votes
1answer
10k views

How to run recurring tasks on a Postgresql database without a cron-like tool?

I would like to call a stored procedure on a regular basis. On Oracle, I would create a job for this. I have found that Postgresql can mimic this well by using an external tool (cron etc) and PgAgent. ...
9
votes
1answer
846 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 ...
9
votes
2answers
4k 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 ...
9
votes
2answers
5k 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 ...
9
votes
1answer
1k views

PostgreSQL exit status when running a file

When running PostgreSQL with a single SQL command error codes are returned as expected: % psql -c "SELECT * FROM AWDASDASDASDAS" my_db ERROR: relation "awdasdasdasdas" does not exist LINE 1: SELECT ...
9
votes
1answer
101 views

How can I see a query after rules have been applied?

From docs - 37.3.1.1. "A First Rule Step by Step" CREATE TABLE shoelace_log ( sl_name text, -- shoelace changed sl_avail integer, -- new available value log_who ...
9
votes
1answer
120 views

Huge mismatch between reported index size and number of buffers in execution plan

The issue We have a query like SELECT COUNT(1) FROM article JOIN reservation ON a_id = r_article_id WHERE r_last_modified < now() - '8 weeks'::interval AND r_group_id = 1 AND ...
9
votes
2answers
194 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition are: CREATE TABLE sequences ( id ...
9
votes
1answer
337 views

In Postgres, how to get a list of current defined savepoint?

I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection. To be more precise: I'd ...
9
votes
2answers
4k 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 ...