Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
70 votes
1 answer
90k views

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go. The application currently consists of a command ...
Jim Dennis's user avatar
70 votes
3 answers
110k 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 ...
Garrett's user avatar
  • 1,033
70 votes
4 answers
93k views

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
Alireza's user avatar
  • 3,636
69 votes
4 answers
76k 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 ...
mikeplate's user avatar
  • 793
68 votes
3 answers
100k 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 ...
andreas-h's user avatar
  • 813
67 votes
5 answers
166k 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 ...
tinlyx's user avatar
  • 3,540
67 votes
4 answers
144k 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. ...
Stephan's user avatar
  • 1,463
67 votes
3 answers
109k 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 ...
Alexis K's user avatar
  • 789
65 votes
3 answers
54k 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....
Aleksandr Levchuk's user avatar
65 votes
8 answers
171k 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 ...
Emanuele Paolini's user avatar
64 votes
7 answers
78k views

What are the differences between "Stored Procedures" and "Stored Functions"?

So a comment to this question mentions, that there is a slight difference between "Stored Procedures" and "Stored Functions" in PostgreSQL. The comment links to a Wikipedia article ...
DrColossos's user avatar
  • 7,187
64 votes
4 answers
49k 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?...
Roman's user avatar
  • 789
64 votes
3 answers
89k views

How to install the additional module pg_trgm

I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index. WHERE foo LIKE '%bar%';
Lizardie's user avatar
  • 773
64 votes
8 answers
44k views

Is it possible to quickly create/restore database snapshots with PostgreSQL?

First of all, I'm a developer, not a DBA or sysadmin; please be gentle :) I'm working on an application workflow where a single user action will trigger complex changes in the database - creating ...
Zilk's user avatar
  • 1,111
63 votes
2 answers
72k 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.
Peter Groves's user avatar
  • 1,115
63 votes
2 answers
74k 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, ...
wizpig64's user avatar
  • 733
62 votes
4 answers
118k views

Find IDs from a list that don't exist in a table

Say I have the following schema and data: create table images( id int not null ); insert into images values(1), (2), (3), (4), (6), (8); I want to perform a query like: select id from images ...
Patrick D'appollonio's user avatar
62 votes
5 answers
61k views

How should I index a UUID in Postgres?

I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUID values in Postgres? I'm split between using hashing and using a trie, unless ...
sudo's user avatar
  • 1,011
61 votes
1 answer
106k views

How to get a working and complete PostgreSQL DB backup and test

I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users. From what I've read, and it ...
Ali Razeghi - AWS's user avatar
60 votes
2 answers
29k 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....
Jorge Vega Sánchez's user avatar
60 votes
2 answers
39k 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 &&) ); ...
Ian Timothy's user avatar
59 votes
7 answers
171k views

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
Yanick Rochon's user avatar
59 votes
6 answers
144k 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 ...
hank's user avatar
  • 889
59 votes
1 answer
29k 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 ...
Dan Leary's user avatar
  • 693
58 votes
2 answers
57k views

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
user avatar
58 votes
1 answer
145k views

Size limit of character varying Postgresql

What is the size limit of various data types in postgresql? I saw somewhere that for character varying(n), varchar(n) n must be between 1 to 10485760. Is that true? What are the valid sizes for ...
Fredy De La Cruz's user avatar
57 votes
6 answers
213k 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 ...
SabreWolfy's user avatar
57 votes
3 answers
192k 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 mytable;...
Jonas's user avatar
  • 33k
57 votes
7 answers
155k 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 ...
joanolo's user avatar
  • 13.4k
57 votes
3 answers
50k 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 ...
bobocopy's user avatar
  • 765
56 votes
4 answers
48k views

Does updating a row with the same value actually update the row?

I have a performance-related question. Let's say I have a user with first name Michael. Take the following query: UPDATE users SET first_name = 'Michael' WHERE users.id = 123 Will the query actually ...
OneSneakyMofo's user avatar
55 votes
5 answers
143k 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. ...
Jonas's user avatar
  • 33k
55 votes
4 answers
77k views

How to turn off header only in psql (postgresql)

I'm using PostgreSQL 9.1.X I am trying to build psql script to print results without a header but including a footer. http://www.postgresql.org/docs/9.1/static/app-psql.html From the document ...
skong's user avatar
  • 651
54 votes
1 answer
79k 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 "...
Rob Bednark's user avatar
  • 2,163
54 votes
1 answer
54k views

Indexes: integer vs string performance if the number of nodes is the same

I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is ...
Chris Cirefice's user avatar
53 votes
6 answers
209k views

Troubleshooting high CPU usage from postgres and postmaster services?

I'm using an open source (RHEL 6.2) based machine running SIEM software. When I run the top command, I see postgres and postmaster both with 96% CPU usage. Is there a way to pin-point or see what ...
asadz's user avatar
  • 635
53 votes
5 answers
100k 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 ...
Milovan Zogovic's user avatar
53 votes
2 answers
87k 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 = ...
xyzzyrz's user avatar
  • 651
53 votes
2 answers
59k views

What is the Maintenance DB asked by pgAdmin?

pgAdmin dialog for adding new database connections asks for a "Maintenance DB". In order to be able to connect, I set it to the database I want to connect (and also, I have the rights to connect). So,...
jgomo3's user avatar
  • 664
52 votes
2 answers
60k 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 ...
Collin Peters's user avatar
51 votes
7 answers
84k views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My ...
orokusaki's user avatar
  • 1,179
51 votes
2 answers
69k views

Convert right side of join of many to many into array

When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row. Example with 3 tables:...
Ced's user avatar
  • 724
50 votes
5 answers
54k 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 ...
CadentOrange's user avatar
50 votes
3 answers
35k 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?
Nick Ginanto's user avatar
50 votes
1 answer
40k 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?
Kannan Ramamoorthy's user avatar
50 votes
6 answers
112k views

pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists

I am using pg_dump / pg_restore to backup and restore a PostgreSQL database, but am getting some error messages (and a non-zero exit status) from pg_restore. I tried a super simple base case (outlined ...
KSletmoe's user avatar
  • 657
50 votes
2 answers
34k 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 ( ...
user4150760's user avatar
  • 1,099
49 votes
6 answers
139k views

How to get all roles that a user is a member of (including inherited roles)?

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; ...
Neil McGuigan's user avatar
49 votes
2 answers
80k views

Replace multiple columns with single JSON column

I am running PostgreSQL 9.3.4. I have a table with 3 columns: id name addr 1 n1 ad1 2 n2 ad2 I need to move the data to a new table with a JSON column like: id data 1 {"name": "n1&...
AliBZ's user avatar
  • 1,789
49 votes
2 answers
55k 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 ...
JPelletier's user avatar