PostgreSQL List Tables without Schema Tables

Question

After reading through the PostgreSQL documentation I determined that I can create an absolute list of table names in PostgreSQL using the following query...

SELECT tablename FROM pg_catalog.pg_tables ORDER BY tablename ASC;

This generates something alone the lines of (reduced example list)...

my_table_1

my_table_1

pg_aggregate

pg_am

pg_amop

sql_features

sql_implementation_info

sql_languages

Unfortunately this also lists tables with prefixes such as pg_ and sql_ which I presume are part of the database schema.

How do I list tables using a SELECT query that are not part of the schema?

I already know how to do this using \dtM command in PSQL command line though it doesn't serve my purposes in this instance.

In example I would like only the following to be returned instead...

my_table_1

my_table_1

user330315 · Accepted Answer · 2014-12-02 22:16:50Z

2

It's easier to use the information_schema for queries like this:

select *
from information_schema.tables
where table_schema = 'public';

Alternatively:

select *
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema');

information_schema.tables filters out the temp schemas already.

answered Dec 2, 2014 at 22:16

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

user3750809 Over a year ago

Thanks @a_horse_with_no_name, SELECT * FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema'); worked great. Wish my laptop screen was HD so when psql outputs a command (so I can use it visually) I could see the data without wrap enabled. Using that to analyze differences between schema and public for example.

Collectives™ on Stack Overflow

PostgreSQL List Tables without Schema Tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related