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 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

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 or 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');


Useful links

171962 questions
30 answers

PostgreSQL: Show tables in PostgreSQL

What's the equivalent to show tables (from MySQL) in PostgreSQL?
  • 261,858
  • 191
  • 397
  • 503
24 answers


How do you perform the equivalent of Oracle's DESCRIBE TABLE in PostgreSQL with psql command?
Mr. Muskrat
  • 22,772
  • 3
  • 20
  • 21
9 answers

How to exit from PostgreSQL command line utility: psql

What command or short key can I use to exit the PostgreSQL command line utility psql?
App Work
  • 21,899
  • 5
  • 25
  • 38
20 answers

Select first row in each GROUP BY group?

I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My…
David Wolever
  • 148,955
  • 89
  • 346
  • 502
28 answers

How can I change a PostgreSQL user password?

How do I change the password for a PostgreSQL user?
  • 26,316
  • 15
  • 48
  • 69
31 answers

How can I drop all the tables in a PostgreSQL database?

How can I drop all tables in PostgreSQL, working from the command line? I don't want to drop the database itself, just all tables and all the data in them.
  • 89,519
  • 86
  • 202
  • 261
15 answers

How to switch databases in psql?

In MySQL, I used use database_name; What's the psql equivalent?
  • 259,732
  • 324
  • 769
  • 1,199
21 answers

Which version of PostgreSQL am I running?

I'm in a corporate environment (running Debian Linux) and didn't install it myself. I access the databases using Navicat or phpPgAdmin (if that helps). I also don't have shell access to the server running the database.
Highly Irregular
  • 38,000
  • 12
  • 52
  • 70
38 answers

How can I start PostgreSQL server on Mac OS X?

Final update: I had forgotten to run the initdb command. By running this command ps auxwww | grep postgres I see that postgres is not running > ps auxwww | grep postgres remcat 1789 0.0 0.0 2434892 480 s000 R+ 11:28PM 0:00.00…
  • 20,541
  • 41
  • 103
  • 153
28 answers

Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails

I'm getting the error: FATAL: Peer authentication failed for user "postgres" when I try to make postgres work with Rails. Here's my pg_hba.conf, my database.yml, and a dump of the full trace. I changed authentication to md5 in pg_hba and tried…
  • 11,831
  • 4
  • 18
  • 18
20 answers

Save PL/pgSQL output from PostgreSQL to a CSV file

What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file? I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.
  • 38,776
  • 17
  • 74
  • 99
19 answers

PostgreSQL error: Fatal: role "username" does not exist

I'm setting up my PostgreSQL 9.1. I can't do anything with PostgreSQL: can't createdb, can't createuser; all operations return the error message Fatal: role h9uest does not exist h9uest is my account name, and I sudo apt-get install PostgreSQL 9.1…
  • 10,958
  • 3
  • 18
  • 24
23 answers

psql: FATAL: database "" does not exist

I'm using the PostgreSql app for mac ( I've used it in the past on other machines but it's giving me some trouble when installing on my macbook. I've installed the application and I ran: psql -h localhost It returns: psql:…
Ryan Rich
  • 11,637
  • 8
  • 22
  • 31
49 answers

Can't find the 'libpq-fe.h header when trying to install pg gem

I am using the Ruby on Rails 3.1 pre version. I like to use PostgreSQL, but the problem is installing the pg gem. It gives me the following error: $ gem install pg Building native extensions. This could take a while... ERROR: Error installing pg: …
  • 11,091
  • 6
  • 21
  • 25
13 answers

Difference between text and varchar (character varying)

What's the difference between the text data type and the character varying (varchar) data types? According to the documentation If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL…
Adam Matan
  • 128,757
  • 147
  • 397
  • 562
2 3
99 100