How to list schemas in PostgreSQL?

Question

How to fetch a list of schema from the current database. The result which we get using \dn. This query fetches all schema

SELECT table_schema,table_name 
FROM information_schema.tables 
ORDER BY table_schema,table_name;

But I want only the schema which is defined in the current database. And then how to fetch all tables corresponding to that particular schema?

Myth

That query will give you the schemas of the current database – Seedbed

This query (SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;) gives me all default schemas also. I want only my created schema from current database – Myth

What do you mean with "default schemas"? – Seedbed

like pg_toast, pg_internal, pg_temp_1,pg_catalog,public,information_schema.. and i have only public and myank_sing named schema in my current database. I'm getting only this two schemas when I run \dn, which is correct – Myth

Then add where table_schema not in (...) to your query – Seedbed

Of course you can: where table_schema not in ('pg_catalog', 'pg_temp_1', 'information_schema', 'public', 'pg_internal'); – Seedbed

Answer 1

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

\dnS

\dn *

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

\dnS+

\dn+ *

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

\dn

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

\dn+

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

SELECT * FROM pg_namespace;

SELECT * FROM information_schema.schemata;

Punctilious

Answer 2

several points:

1. If you want to see what query is used for psql shortcut, run psql -E (with -E key)
2. select *from pg_namespace will give you list of schemas
3. select * from pg_class limit where relnamespace = 'your_schema'::regnamespace will give you all schema relations
4. select * from pg_class limit where relnamespace = 'your_schema'::regnamespace and relkind = 'r' will limit the list to tables only

5. to limit list of schemas to owned ones only, use

   select *from pg_namespace where nspowner = current_user::regrole; 
   

Vacancy

select *from pg_namespace will give me all schema I want the schema name related to current database only. I database which I'm currently connected and not the defaults one also. – Myth

no its not. postgresql.org/docs/current/static/catalog-pg-namespace.html – Vacancy

if i run that i'm getting pg_toast, pg_internal, pg_temp_1,pg_catalog,public,information_schema, myank_sing but i want only public and myank_sing which i got using \dn – Myth

then run the query you see with psql -E (point one in my answer) – Vacancy

SELECT * FROM pg_namespace will give you list of schemas ... isn't that \dn+? – Hedgehop

yes, \dn is a psql meta-command - very comfortable – Vacancy