I can list all tables in all schemas using
> \dt *.*
but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by me in the public schema and any schemas I've defined.
I'm hoping to find a way to do this without having to explicitly add schemas to the search path as I create them as described here:
https://stackoverflow.com/a/12902069
EDIT:
Based on the accepted answer, I've created the following View:
create view my_tables as
select table_catalog, table_schema, table_name, table_type
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema');
And now the following command gives me what I wanted:
select * from my_tables;
Best Answer
This will list all tables the current user has access to, not only those that are owned by the current user:
(I'm not entirely sure the
not like 'pg_toast%'
is actually needed though.)I you really need the owner information, you probably need to use
pg_class
and related tables.Edit: this is the query that includes the owner information: