List tables in PostgreSQL

Unlike MySQL, Apache Hive, and Apache Impala, we can’t acquire a list of tables in PostgreSQL using the SHOW TABLES command.

Use the below command instead. This will avoid system tables as well.
SELECT * FROM pg_catalog.pg_tables
WHERE schemaname != ‘pg_catalog’
AND schemaname != ‘information_schema’;

Or you can use the below if
SELECT table_schema || ‘.’ || table_name
FROM information_schema.tables
WHERE table_type = ‘BASE TABLE’
AND table_schema NOT IN (‘pg_catalog’, ‘information_schema’);

Hope you find this article helpful.

Please subscribe for more interesting updates.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s