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 tableoid.
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 collections:
tableoid | collection_id | ...
----------------+---------------+ ...
collections_26 | 2 | ...
As you can see, the record is stored, based on the naming I adopted, in the 27th partition: collections_26.
In order to adapt it to your case, you just need to change the names of tables and columns.