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;
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 only takes a minute to sign up.
Sign up to join this communityWhen 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;
When using the psql
command line, you may list all schema with command \dn
.
\dn
lists, as opposed to tables that \dt
lists?
\dt
lists tables for public schema. To show tables of all schemas use \dt *.*
and for a particular schema use \dt schema_name.*
.
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.
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;