Questions tagged [postgresql]

PostgreSQL is an open-source, relational database management system (RDBMS) available for all major platforms, including Linux, UNIX, Windows and OS X. Mention your version of Postgres when asking questions. Consider dba.stackexchange.com for questions concerning the administration or advanced features.

PostgreSQL (often "Postgres", never ~~"Postgre"~~), is an object-relational database management system (ORDBMS) available for all major operating systems. It is free and open-source software released under the PostgreSQL License, an MIT-style license. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of volunteers employed by companies such as Red Hat and EnterpriseDB.

PostgreSQL is pronounced as "post-grez-q-l". Postgres is pronounced as "post-grez".

Numerous forks of Postgres exist for specialized tasks, such as Greenplum Database, Amazon Redshift, ParAccel, Postgres-XC, Postgres-XL, PPAS, etc. Their features and syntax differ from stock PostgreSQL. Declare what you are using and add a tag.

PostgreSQL Features

Implements most of the major features of SQL:2016
ACID-compliant
Fully transactional (including all DDL statements)
Extensible data types, operators, indexing, functions, aggregates, procedural languages
A large number of extensions, both bundled and supplied by third parties
Supported operating systems include: Linux, FreeBSD, Solaris, MS Windows, Mac OS X
Detailed, comprehensive and precise documentation

How to ask good questions

For performance questions consider instructions for postgresql-performance.

For questions targeting a specific version add a version tag: postgresql-9.6, postgresql-13 etc.

Questions concerning the administration or advanced features are best directed to dba.StackExchange.com.

The Guide to Reporting Problems on the PostgreSQL wiki is helpful reading.

Show at least your major PostgreSQL version from SELECT version().
Include the full text of relevant error messages, SQL queries, etc.
Where appropriate, supply a test case with CREATE TABLE and INSERT statements with sample data and expected results. A fiddle site like dbfiddle.uk or sqlfiddle.com is typically useful.
If you've asked previous, related questions, add a link.
If you re-post a mailing list question, link to the page in the mailing list archives. And vice versa.

Know about PostgreSQL current settings/backend config with:

 SELECT name, current_setting(name), source FROM pg_settings
 WHERE  source NOT IN ('default', 'override');

Resources

Useful links

171962 questions

882

votes

22 answers

Creating a copy of a database in PostgreSQL

What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin?

postgresql

asked May 18 '09 at 07:00

egaga

21,042
10
46
60

820

votes

12 answers

How to drop a PostgreSQL database if there are active connections to it?

I need to write a script that will drop a PostgreSQL database. There may be a lot of connections to it, but the script should ignore that. The standard DROP DATABASE db_name query doesn't work when there are open connections. How can I solve the…

postgresql

asked Mar 23 '11 at 16:03

Roman Prykhodchenko

12,655
8
29
33

786

votes

18 answers

Insert, on duplicate update in PostgreSQL?

Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax: INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z) ON DUPLICATE KEY UPDATE…

postgresql upsert

asked Jul 10 '09 at 11:38

Teifion

108,121
75
161
195

776

votes

21 answers

How to import CSV file data into a PostgreSQL table

How can I write a stored procedure that imports data from a CSV file and populates the table?

postgresql csv postgresql-copy

asked Jun 07 '10 at 06:19

vardhan

7,769
3
16
3

754

votes

17 answers

How to do an update + join in PostgreSQL?

Basically, I want to do this: update vehicles_vehicle v join shipments_shipment s on v.shipment_id=s.id set v.price=s.price_per_vehicle; I'm pretty sure that would work in MySQL (my background), but it doesn't seem to work in postgres. The…

postgresql

asked Oct 23 '11 at 22:09

mpen

272,448
266
850
1,236

738

votes

17 answers

Run a PostgreSQL .sql file using command line arguments

I have some .sql files with thousands of INSERT statements in them and need to run these inserts on my PostgreSQL database in order to add them to a table. The files are that large that it is impossible to open them and copy the INSERT statements…

postgresql command-line psql

asked Mar 16 '12 at 11:01

CSharpened

11,674
14
52
86

729

votes

11 answers

postgres: upgrade a user to be a superuser?

In postgres, how do I change an existing user to be a superuser? I don't want to delete the existing user, for various reasons. # alter user myuser ...?

sql postgresql

asked May 25 '12 at 15:43

flossfan

10,554
16
42
53

715

votes

8 answers

Insert text with single quotes in PostgreSQL

I have a table test(id,name). I need to insert values like: user's log, 'my user', customer's. insert into test values (1,'user's log'); insert into test values (2,''my users''); insert into test values (3,'customer's'); I am getting an error…

sql postgresql quotes string-literals

asked Sep 07 '12 at 11:11

MAHI

9,263
11
36
47

713

votes

34 answers

How to reset Postgres' primary key sequence when it falls out of sync?

I ran into the problem that my primary key sequence is not in sync with my table rows. That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists. It seems…

sql postgresql primary-key ddl database-sequence

asked Oct 28 '08 at 18:08

meleyal

32,252
24
73
79

695

votes

11 answers

What's the PostgreSQL datatype equivalent to MySQL AUTO INCREMENT?

I'm switching from MySQL to PostgreSQL and I was wondering how can I have an INT column with AUTO INCREMENT. I saw in the PostgreSQL docs a datatype called SERIAL, but I get syntax errors when using it.

postgresql auto-increment

asked Apr 24 '09 at 22:00

Ian

24,116
22
58
96

691

votes

35 answers

psql: FATAL: role "postgres" does not exist

I'm a postgres novice. I installed the postgres.app for mac. I was playing around with the psql commands and I accidentally dropped the postgres database. I don't know what was in it. I'm currently working on a tutorial:…

macos postgresql terminal

asked Mar 08 '13 at 19:31

user805981

9,979
8
44
64

671

votes

20 answers

Import SQL dump into PostgreSQL database

We are switching hosts and the old one provided a SQL dump of the PostgreSQL database of our site. Now, I'm trying to set this up on a local WAMP server to test this. The only problem is that I don't have an idea how to import this database in the…

database postgresql

asked Jul 27 '11 at 09:50

dazz

8,162
9
31
41

653

votes

22 answers

Postgres: INSERT if does not exist already

I'm using Python to write to a postgres database: sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES (" sql_string += hundred + ", '" + hundred_slug + "', " + status + ");" cursor.execute(sql_string) But because some of my rows are…

postgresql sql-insert upsert

asked Nov 01 '10 at 14:24

AP257

89,519
86
202
261

648

votes

12 answers

How do I specify a password to 'psql' non-interactively?

I am trying to automate database creation process with a shell script and one thing I've hit a road block with passing a password to psql. Here is a bit of code from the shell script: psql -U $DB_USER -h localhost -c"$DB_RECREATE_SQL" How do I pass…

postgresql bash command-line psql

asked Jun 19 '11 at 21:06

Alex N.

14,805
10
46
54

638

votes

66 answers

Postgres could not connect to server

After I did brew update and brew upgrade, my postgres got some problem. I tried to uninstall postgres and install it again, but it didn't work as well. This is the error message. (I also got this error message when I try to do rake db:migrate) $…

ruby-on-rails database postgresql macos homebrew

asked Nov 16 '12 at 04:53

Gary Lai

6,543
3
17
16

Prev 1

…

99 100 Next