How to identify the partitioned tables in getTables?

[buremba]

We're using the PgDatabaseMetadata in order to list the tables in a given schema but since we're extensively using partitioned tables, it returns hundreds of tables. However; we only need the BASE table and discard the PARTITIONED tables under the BASE table but couldn't find any info about the relkind in PgDatabaseMetadata.

• bug report
• feature request

Describe the issue
A clear and concise description of what the issue is.

Driver Version?
42.2.6

Java Version?
8

PostgreSQL Version?
12

Expected behavior
Ideally, we should either expose relkind in PgDatabaseMetadata or provide a way for the developer to identify the partitioned tables.

[davecramer]

Seems like something that would be useful. Are you up to providing a PR ?

[buremba]

@davecramer Which approach do you prefer, exposing relkind in ResultSet or something like is_partitioned?

[davecramer]

I think I'd like to see relkind and then have it return an enum of the different kinds.

[jorsol]

I would not said "expose" relkind, but extend current tableTypeClauses so the TABLE_TYPE of getTables shows PARTITIONED TABLE.

[buremba]

Thanks!

[gbhat618]

Hi All,
I want to ask how to list all the root tables (base tables in case of partition tables + regular tables) using the jdbc driver?

In the feature implemented 1708, we can do getTables for PARTITIONED TABLE, which returns the list of base tables which are partitioned. It returns the tables that are created by syntax like,

CREATE TABLE t (
...
) PARTITION BY RANGE (col_name);

While to get the regular tables we need to use getTables for TABLE, which returns regular tables, but also returns the partitions of the base table as well.

But I want to get --> regular tables (non-partitions) + base tables.

In the output of getTables, seems no way to find whether it is a partition or not, as the available columns are only,

table_cat
table_schem
table_name
table_type
remarks
type_cat
type_schem
type_name
self_referencing_col_name
ref_generation

So it is not possible to get regular tables (but not paritions) using JDBC metadata apis alone right?
Probably need to make an SQL query to the db and do some filtering in the getTables output.

It seems from the postgres documentation, pg_class column relispartition is the one to determine.
References

1. PG Doc: pg_class
2. SO: Show only list of tables without child partitions
3. SO: How to list all tables in postgres without partitions

[davecramer]

There is no JDBC API that provides this information as far as I know so as you surmised you would have to query pg_class on your own

[gbhat618]

ok, thank you so much for the confirming, also thanks for the quick reply 🙏