How do I list all schemas in PostgreSQL?

Question

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;

score 401 · Answer 1 · answered Apr 15 '13 at 19:59

401

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

answered Apr 15 '13 at 19:59

eppesuig

4,622
1
14
14

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
1

what *are* schemas, that `\dn` lists, as opposed to tables that `\dt` lists? – Tommy Apr 29 '16 at 15:29
26

@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 '17 at 06:34
2

@Tommy, schemas are namespaces: you may have different tables with same name in different namespaces. – eppesuig Nov 30 '17 at 08:48

score 394 · Accepted Answer · edited May 07 '19 at 18:06

394

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

select schema_name
from information_schema.schemata;

More details in the manual

alternatively:

select nspname
from pg_catalog.pg_namespace;

More details about pg_catalog in the manual

edited May 07 '19 at 18:06

Felipe Augusto

105
4

answered Apr 15 '13 at 19:48

a_horse_with_no_name

76,712
14
153
191

score 59 · Answer 3 · edited May 06 '15 at 13:18

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-

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.

score 29 · Answer 4 · answered Jan 29 '16 at 00:02

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 :

begin;
\dn+

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 ;
                                 query                                 
-----------------------------------------------------------------------
 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'      +
 ORDER BY 1;

You don't need to trick it; just run `\set ECHO_HIDDEN on` – Nick Barnes Sep 11 '16 at 01:22 — Nick Barnes, Sep 11 '16 at 01:22

How do I list all schemas in PostgreSQL?

4 Answers4

Linked