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;

5 Answers 5


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
  • 3
    what are schemas, that \dn lists, as opposed to tables that \dt lists?
    – Tommy
    Apr 29, 2016 at 15:29
  • 31
    @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
  • 4
    @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 with command psql --username={userName} {DBName} then you can type the below command to check how many schemas are present in the database:

DBName=# \dn

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

  1. After connecting the the DB, type

     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

  2. Then type

     DBName=# \?

    You will get all the options very easily.

  • psql --u login is wrong CLI attribute, you should use psql --username=login or psql -U login
    – AntonioK
    Aug 1, 2023 at 7:27

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'      +
  • 27
    You don't need to trick it; just run \set ECHO_HIDDEN on Sep 11, 2016 at 1:22
  • 21
    or run it as psql -E Dec 22, 2016 at 17:46

These list all schemas including system's in the current database:

\dn *

These list all schemas including system's in the current database in detail:

\dn+ *

This lists all schemas excluding system's in the current database:


This lists all schemas excluding system's in the current database in detail:


These also list all schemas including system's in the current database:

SELECT * FROM pg_namespace;
SELECT * FROM information_schema.schemata;

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