How do I list all tables in all schemas owned by the current user in Postgresql?
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;
The short answer to the question would be:
SELECT *
FROM pg_tables t
WHERE t.tableowner = current_user;