snippetsqlCritical
How do I list all tables in all schemas owned by the current user in Postgresql?
Viewed 0 times
postgresqltablesthealluserschemasownedcurrenthowlist
Problem
I can list all tables in all schemas using
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:
And now the following command gives me what I wanted:
> \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;Solution
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
I you really need the owner information, you probably need to use
Edit: this is the query that includes the owner information:
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;Code Snippets
select *
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema')
and table_schema not like 'pg_toast%'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;Context
StackExchange Database Administrators Q#30061, answer score: 52
Revisions (0)
No revisions yet.