Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

alternatively:

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.