25

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;
32

This will list all tables the current user has access to, not only those that are owned by the current user:

select *
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema')
and table_schema not like 'pg_toast%'

(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:

select nsp.nspname as object_schema,
       cls.relname as object_name, 
       rol.rolname as owner, 
       case cls.relkind
         when 'r' then 'TABLE'
         when 'm' then 'MATERIALIZED_VIEW'
         when 'i' then 'INDEX'
         when 'S' then 'SEQUENCE'
         when 'v' then 'VIEW'
         when 'c' then 'TYPE'
         else cls.relkind::text
       end as object_type
from pg_class cls
  join pg_roles rol on rol.oid = cls.relowner
  join pg_namespace nsp on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
  and nsp.nspname not like 'pg_toast%'
  and rol.rolname = current_user  --- remove this if you want to see all objects
order by nsp.nspname, cls.relname;
  • This is good enough. I'm going to create a View called my_tables from this. – Peter Groves Dec 9 '12 at 20:24
  • Great answer, add a when 'm' then 'MATERIALIZED_VIEW' to show that new type. – Forbesmyester Apr 16 '18 at 9:26
  • While another answer is concise, this might be relevant when excluding namespaces. – mlt Feb 9 at 2:29
18

The short answer to the question would be:

SELECT *
FROM pg_tables t
WHERE t.tableowner = current_user;
-3

See this. All tables:

SELECT relname FROM pg_class WHERE relname !~ '^(pg_|sql_)' AND relkind = 'r';

Not the answer you're looking for? Browse other questions tagged or ask your own question.