CODEWITHSUNDEEP
postgresql
user3750809
user3750809

Reputation:

PostgreSQL List Tables without Schema Tables

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

Upvotes: 1

Views: 4428

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 2

Related Questions