Stack Overflow
Stack Overflow

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

How to ask good questions

For performance questions consider instructions for .

For questions targeting a specific version add a version tag: , 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
636
votes
9 answers

What is the format for the PostgreSQL connection string / URL?

What is the format for the PostgreSQL connection string (URL postgres://...) when the host is not the localhost?
JII
  • 6,457
  • 3
  • 19
  • 9
630
votes
13 answers

Copying PostgreSQL database to another server

I'm looking to copy a production PostgreSQL database to a development server. What's the quickest, easiest way to go about doing this?
Robin Barnes
  • 13,133
  • 15
  • 44
  • 45
609
votes
12 answers

Generating a UUID in Postgres for Insert statement?

My question is rather simple. I'm aware of the concept of a UUID and I want to generate one to refer to each 'item' from a 'store' in my DB with. Seems reasonable right? The problem is the following line returns an error: honeydb=# insert into items…
fIwJlxSzApHEZIl
  • 11,861
  • 6
  • 62
  • 71
604
votes
12 answers

How do you create a read-only user in PostgreSQL?

I'd like to create a user in PostgreSQL that can only do SELECTs from a particular database. In MySQL the command would be: GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy'; What is the equivalent command or series of commands in…
Ethan
  • 57,819
  • 63
  • 187
  • 237
590
votes
31 answers

'password authentication failed for user "postgres"'

I have installed PostgreSQL 8.4, Postgres client and Pgadmin 3. Authentication failed for user "postgres" for both console client and Pgadmin. I have typed user as "postgres" and password "postgres", because it worked before. But now authentication…
I159
  • 29,741
  • 31
  • 97
  • 132
585
votes
29 answers

Where does PostgreSQL store configuration/conf files?

I have recently installed PostgreSQL on Ubuntu with the EnterpriseDB package. I can connect to the database locally, but I can't configure it because I can't find config files. I searched through entire hard drive and found only samples like…
Timur Sadykov
  • 10,859
  • 7
  • 32
  • 45
569
votes
21 answers

How do you find the row count for all your tables in Postgres

I'm looking for a way to find the row count for all my tables in Postgres. I know I can do this one table at a time with: SELECT count(*) FROM table_name; but I'd like to see the row count for all the tables and then order by that to get an idea…
mmrobins
  • 12,809
  • 7
  • 41
  • 42
568
votes
7 answers

"use database_name" command in PostgreSQL

I am beginner to PostgreSQL. I want to connect to another database from the query editor of Postgres - like the USE command of MySQL or MS SQL Server. I found \c databasename by searching the Internet, but its runs only on psql. When I try it from…
sam
  • 5,793
  • 2
  • 14
  • 9
563
votes
15 answers

How to make "case-insensitive" query in Postgresql?

Is there any way to write case-insensitive queries in PostgreSQL, E.g. I want that following 3 queries return same result. SELECT id FROM groups where name='administrator' SELECT id FROM groups where name='ADMINISTRATOR' SELECT id FROM groups…
Jame
  • 21,150
  • 37
  • 80
  • 107
554
votes
5 answers

How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales that are unique based on day and price will get…
sheats
  • 33,062
  • 15
  • 45
  • 44
542
votes
25 answers

Kill a postgresql session/connection

How can I kill all my postgresql connections? I'm trying a rake db:drop but I get: ERROR: database "database_name" is being accessed by other users DETAIL: There are 1 other session(s) using the database. I've tried shutting down the processes I…
DanS
  • 17,550
  • 9
  • 53
  • 47
526
votes
6 answers

List tables in a PostgreSQL schema

When I do a \dt in psql I only get a listing of tables in the current schema (public by default). How can I get a list of all tables in all schemas or a particular schema?
Nyxynyx
  • 61,411
  • 155
  • 482
  • 830
525
votes
31 answers

Restore a postgres backup file using the command line?

Locally, I use pgadmin3. On the remote server, however, I have no such luxury. I've already created the backup of the database and copied it over, but is there a way to restore a backup from the command line? I only see things related to GUI or to…
TwixxyKit
  • 9,953
  • 9
  • 31
  • 32
524
votes
17 answers

Connecting to Postgresql in a docker container from outside

I have Postgresql on a server in a docker container. How can I connect to it from the outside, that is, from my local computer? What setting should I apply to allow that?
Sojo
  • 5,455
  • 3
  • 10
  • 11
514
votes
8 answers

updating table rows in postgres using subquery

I have this table in a postgres 8.4 database: CREATE TABLE public.dummy ( address_id SERIAL, addr1 character(40), addr2 character(40), city character(25), state character(2), zip character(5), customer boolean, supplier boolean, …
stackover
  • 6,275
  • 6
  • 22
  • 20
1 2
3
…
99 100