Take the 2-minute tour ×

When using PostgreSQL v9.1, how do I list all of the schemas using SQL?

I was expecting something along the lines of:

SELECT something FROM pg_blah;
share|improve this question

3 Answers 3

up vote 32 down vote accepted

To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA

select schema_name
from information_schema.schemata

More details in the manual: http://www.postgresql.org/docs/current/static/information-schema.html


select nspname
from pg_catalog.pg_namespace;

More details about pg_catalog in the manual: http://www.postgresql.org/docs/current/static/catalogs.html

share|improve this answer

When using the psql command line, you may list all schema with command \dn.

share|improve this answer
Thanks. It would be nice to have just the schemas returned by \dn, but in this case I'm writing a bootstrap app that connects using libpq/libpqxx, so I don't have CLI access. –  Stéphane Apr 15 '13 at 20:03

Connect to the psql command --> psql --u {userName} {DBName} then you can type the below command to check how many schemas are present in the DB

DBName=# \dn

Else you can check the syntax by the below steps easily-

  1. After connecting the the DB, press

DBName=# help

You will get the below options- You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit

Then press

DBName=# \?

You will get all the options very easily.

share|improve this answer
Well, those are not options but commands. Other than that, the same answer is here for about 9 months now... Also, please have a look at the formatting possibilities (available by clicking on the ? icon in the top right corner of the answer panel). –  dezso Jan 20 at 10:33

Your Answer


