How can I get the name of all the non partitioned tables in my DB ? I use PostgresSQL 9.6
And how do I get the names of the partitioned tables only ?
For now I have the names of the partions in my DB, specifying a table name, but I need to do that dinamically.
<code>SELECT i.inhrelid::regclass AS child
FROM pg_inherits i
WHERE i.inhparent = 'public.documento'::regclass;
</code>
<code>SELECT i.inhrelid::regclass AS child
FROM pg_inherits i
WHERE i.inhparent = 'public.documento'::regclass;
</code>
SELECT i.inhrelid::regclass AS child
FROM pg_inherits i
WHERE i.inhparent = 'public.documento'::regclass;
0
Updated
for partitionned tables:
<code>select distinct inhparent::regclass from pg_inherits
</code>
<code>select distinct inhparent::regclass from pg_inherits
</code>
select distinct inhparent::regclass from pg_inherits
and not partitions smthlike:
<code>select oid::regclass::text relation
from pg_class where relkind = 'r'
except
select distinct inhparent::regclass::text
from pg_inherits
except
select distinct inhrelid::regclass::text
from pg_inherits
;
</code>
<code>select oid::regclass::text relation
from pg_class where relkind = 'r'
except
select distinct inhparent::regclass::text
from pg_inherits
except
select distinct inhrelid::regclass::text
from pg_inherits
;
</code>
select oid::regclass::text relation
from pg_class where relkind = 'r'
except
select distinct inhparent::regclass::text
from pg_inherits
except
select distinct inhrelid::regclass::text
from pg_inherits
;
note: to filter results, just use where, like
<code>with l as (
<code above>
)
select * from l where relation not like 'pg_%';
</code>
<code>with l as (
<code above>
)
select * from l where relation not like 'pg_%';
</code>
with l as (
<code above>
)
select * from l where relation not like 'pg_%';
9
pg_inherits records information about table and index inheritance hierarchies.
If you need to get all partitioned tables only(exclude partitioned indexes), we can use:
<code>select relnamespace::regnamespace::text schema_name, oid::regclass::text table_name from pg_class
where relkind = 'p' and oid in (select distinct inhparent from pg_inherits)
order by schema_name, table_name;
</code>
<code>select relnamespace::regnamespace::text schema_name, oid::regclass::text table_name from pg_class
where relkind = 'p' and oid in (select distinct inhparent from pg_inherits)
order by schema_name, table_name;
</code>
select relnamespace::regnamespace::text schema_name, oid::regclass::text table_name from pg_class
where relkind = 'p' and oid in (select distinct inhparent from pg_inherits)
order by schema_name, table_name;