Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
47
votes
1answer
23k views

Best practices for schema changes and data migrations to a live database without downtime?

How do you make schema changes to a live database without downtime? For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
46
votes
6answers
98k views

How to list all views in SQL in PostgreSQL?

How do I list all views for a database using an SQL command in PostgreSQL? I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g., SELECT ...
46
votes
4answers
49k views

Created user can access all databases in PostgreSQL without any grants

I must be missing something with regards to setting up PostgreSQL. What I'd like to do is create multiple databases and users that are isolated from each other so that a specific user only has access ...
46
votes
3answers
48k 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 ...
46
votes
7answers
90k views

EXISTS (SELECT 1 …) vs EXISTS (SELECT * …) One or the other?

Whenever I need to check for the existence of some row in a table, I tend to write always a condition like: SELECT a, b, c FROM a_table WHERE EXISTS (SELECT * -- This is what I normally ...
45
votes
6answers
98k views

How to create a temporary table using VALUES in PostgreSQL

I am learning PostgreSQL and trying to figure out how to create a temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes. I looked at the ...
45
votes
2answers
35k views

How to view the current settings of Autovacuum in Postgres?

I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration. Postgres 9.1 is the version I'm most interested in.
45
votes
4answers
20k views

Monitoring Progress of Index Construction in PostgreSQL

Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring. Is there a way to monitor ...
45
votes
2answers
75k views

Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding ...
44
votes
4answers
33k views

What is the best way to store an email address in PostgreSQL?

What would be the right datatype to store email addresses in PostgreSQL? I can use varchar (or even text), but I wonder if there is a more specific data type for emails.
44
votes
3answers
68k views

How to run recurring tasks on Postgresql without an external 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. ...
44
votes
2answers
75k views

ERROR: no schema has been selected to create in

I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped). But apparently the schema exists, and anyway the problem is not ...
43
votes
5answers
43k views

Aggressive Autovacuum on PostgreSQL

I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows: autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum ...
43
votes
4answers
33k 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? Edit: Not a dupe of: ...
43
votes
6answers
139k views

How to insert (file) data into a PostgreSQL bytea column?

This question is not about bytea v. oid v. blobs v. large objects, etc. I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This ...
43
votes
2answers
37k views

Configuring PostgreSQL for read performance

Our system writes a lots of data (kind of Big Data system). Write performance is good enough for our needs but read performance is really too slow. The primary key (constraint) structure is similar ...
43
votes
4answers
73k views

Getting last modification date of a PostgreSQL database table

I'm trying to get when my table was modified by checking its file modification date as it is described in this answer. But the result is not always correct. The file modification date updates in ...
43
votes
1answer
35k 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, ...
42
votes
4answers
30k views

Does running pg_dump on live db produce consistent backups?

I have a 3GB database that is constantly modified and I need to make backups without stopping the server (Postgres 8.3). My pg_dump runs for 5 minutes. What if the data is modified during the process?...
42
votes
2answers
32k views

Can't rename columns in PostgreSQL views with CREATE OR REPLACE

In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names. This works CREATE OR REPLACE VIEW gfam.nice_builds AS SELECT (family_tree....
42
votes
2answers
49k views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
41
votes
5answers
56k views

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
41
votes
6answers
59k views

Export Postgres table as json

Is there a way to export postgres table data as json to a file? I need the output to be line by line, like: {'id':1,'name':'David'} {'id':2,'name':'James'} ... EDIT: postgres version: 9.3.4
41
votes
2answers
55k views

Optimizing bulk update performance in PostgreSQL

Using PG 9.1 on Ubuntu 12.04. It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = ...
41
votes
2answers
21k views

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

In PostgreSQL 9.2.3 I am trying to create this simplified table: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (user_id WITH =, startend WITH &&) ); ...
40
votes
7answers
38k views

What are the differences between “Stored Procedures” and “Stored Functions”?

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't ...
40
votes
3answers
46k views

Idiomatic way to implement UPSERT in PostgreSQL

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example). And I'm just not a psql expert ...
39
votes
2answers
14k views

Are WHERE clauses applied in the order they are written?

I'm trying to optimize a query which looks into a big table (37 millions rows) and have a question about what order the operations are executed in a query. select 1 from workdays day where day....
39
votes
5answers
64k views

How to efficiently copy millions of rows from one table to another in Postgresql?

I have two database tables. One contains hundreds of millions of records. Lets call that one history. The other one is calculated on daily basis and I want to copy all of its records into the history ...
39
votes
3answers
24k views

What is the optimal data type for an MD5 field?

We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute). There is a table names that serves as a sort of central registry. Each row has a ...
38
votes
2answers
35k views

Best way to populate a new column in a large table?

We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL ...
38
votes
3answers
36k views

Are regular VACUUM ANALYZE still recommended under 9.1?

I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs? If the answer is "it depends", then: I have a largish database (...
38
votes
1answer
18k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
37
votes
5answers
24k views

Locking issue with concurrent DELETE / INSERT in PostgreSQL

This is pretty simple, but I'm baffled by what PG does (v9.0). We start with a simple table: CREATE TABLE test (id INT PRIMARY KEY); and a few rows: INSERT INTO TEST VALUES (1); INSERT INTO TEST ...
37
votes
4answers
26k views

How do I find out how far along my PostgreSQL query is?

I have a pretty decent idea of how many rows my SELECT...INTO query will actually process (e.g. I know how many will materialize). I understand Postgres won't tell me percentage completeness, is ...
37
votes
5answers
103k views

How can I use a default value in a Select query in PostgreSQL?

I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this? E.g. ...
36
votes
6answers
39k views

PostgreSQL: Force data into memory

Is there a systematic way to force PostgreSQL to load a specific table into memory, or at least read it from disk so that it will be cached by the system?
36
votes
3answers
168k views

Forgotten PostgreSQL Windows password

This morning I’ve been trying to connect the PostgreSQL database on my Windows 7 Professional desktop. The default value is ‘postgres’, but sure enough I forgot what password I used when I originally ...
36
votes
2answers
22k views

Refresh materalized view incrementally in PostgreSQL

Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed? Consider this table & materialized view: CREATE TABLE graph ( ...
36
votes
2answers
45k views

Reference column alias in same SELECT list

I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this: SELECT samples.id, ...
36
votes
1answer
26k views

How can I benchmark a PostgreSQL query?

I want to benchmark a query containing a user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark? I know that timing can be turned on with \timing at ...
35
votes
3answers
45k 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 ...
35
votes
2answers
32k 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 ...
35
votes
3answers
18k views

How do I know what indexes to create for a table?

Is there a way I can figure out the best way to know which indexes to create for a table?
34
votes
2answers
31k views

Differences between MATCH FULL, MATCH SIMPLE, and MATCH PARTIAL?

I've noticed a MATCH SIMPLE and MATCH FULL, but I don't understand what they do. I see default is MATCH SIMPLE; but, how do the other MATCH clauses to the FOREIGN KEY constraint function?
34
votes
1answer
67k views

Postgres function assign query results to multiple variables

I need to assign values to 2 variable as below in Postgres function. a := select col1 from tbl where ... b := select col2 from tbl where ... How can I assign 2 values to 2 variables in one line ...
34
votes
1answer
20k views

Is there a maximum length constraint for a postgres query?

The app we are building might execute quite a big insert queries. Is there limit that my postgres query can have only a certain number of characters?
34
votes
1answer
21k 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?
34
votes
1answer
27k views

Need for indexes on foreign keys

I'm struggling with indexes, primary keys and foreign keys... And the need of having them all. If I have two tables, both of them have an integer as a primary key. The first table references ...
33
votes
4answers
78k 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 ...