Which partition contains a specific row in my PostgreSQL database?

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.