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;

4 Answers 4


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

select schema_name
from information_schema.schemata;

More details in the manual


select nspname
from pg_catalog.pg_namespace;

More details about pg_catalog in the manual


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

  • 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, 2013 at 20:03
  • 1
    what are schemas, that \dn lists, as opposed to tables that \dt lists?
    – Tommy
    Apr 29, 2016 at 15:29
  • 24
    @Tommy \dt lists tables for public schema. To show tables of all schemas use \dt *.* and for a particular schema use \dt schema_name.*.
    – Serious
    Nov 22, 2017 at 6:34
  • 2
    @Tommy, schemas are namespaces: you may have different tables with same name in different namespaces.
    – eppesuig
    Nov 30, 2017 at 8:48

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.


Beginning On postgres 9.3, One trick you can use in postgres to get the exact sql of informational command (such as \d, \du, \dp, etc) in psql is by using a transaction. Here's how the trick goes. Open one postgres session, then type your command :


While the transaction still running, open another postgres session, and query the pg_stat_activity and you can get the exact sql.

postgres=# select query from pg_stat_activity ;
 SELECT n.nspname AS "Name",                                          +
   pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",                 +
   pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",+
   pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description" +
 FROM pg_catalog.pg_namespace n                                       +
 WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'      +
  • 24
    You don't need to trick it; just run \set ECHO_HIDDEN on Sep 11, 2016 at 1:22
  • 19
    or run it as psql -E Dec 22, 2016 at 17:46

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