Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
16,831
questions
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 ...
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 ...
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?
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 ...
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 ...
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 ...
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.
...
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 ...
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....
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 ...
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 ...
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?...
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%';
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 ...
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.
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,
...
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 ...
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 ...
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 ...
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....
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 &&)
);
...
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 ...
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 ...
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 ...
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?
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 ...
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 ...
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;...
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 ...
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 ...
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 ...
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. ...
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 ...
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 "...
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 ...
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 ...
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 ...
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 = ...
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,...
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 ...
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 ...
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:...
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
...
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?
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?
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 ...
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 (
...
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;
...
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&...
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 ...