If you are enjoying working with PostgreSQL declarative partitioning, you might be wondering how to check which partition contains a specific record. While it is quite obvious in the cases of list or range partitioning, it is a bit trickier with hash partitioning.
Don’t worry. Here you can find a quick way to determine which partition contains a given row, by simply taking advantage of PostgreSQL’s system columns – specifically
The example below assumes we have a partitioned table called
collections (parent table) which is partitioned by hash based on the value of its primary key, a serial field called
collection_id (the number of partitions is irrelevant, but in the example I set up 32).
SELECT tableoid::pg_catalog.regclass, * FROM collections WHERE collection_id = 2;
The above query retrieves the name of the partition that contains the record with
collection_id = 2 in the hash partitioned table called
tableoid | collection_id | ... ----------------+---------------+ ... collections_26 | 2 | ...
As you can see, the record is stored, based on the naming I adopted, in the 27th partition:
In order to adapt it to your case, you just need to change the names of tables and columns.