Partitioning is one of the most desired features of PostgreSQL, widely adopted by developers. This is not only true for in PostgreSQL 15, but also for older versions which did not provide as many features as the latest version of the database. That’s why you should know not only how to properly partition tables, but how to find out which partition a piece of data belongs to.

So, there’s a small question which often arises: How DO I figure out which partition I just inserted my data into? Some small “old consultant’s trickery” can help to find out.

Creating partitioned tables in PostgreSQL

To get started, you first have to create a partitioned table:

CREATE TABLE t_turnover (
	id          serial, 
	country     text, 
	t           timestamptz, 
	task        text, 
	turnover    numeric
) PARTITION BY LIST (country);

CREATE TABLE t_austria 
	PARTITION OF t_turnover FOR VALUES IN ('Austria');


CREATE TABLE t_ger_swiss 
	PARTITION OF t_turnover FOR VALUES IN ('Germany', 'Switzerland');

In this case, you’ve created a simple table to store turnover. Currently, 4 languages are supported. However, you will often want to tell PostgreSQL that all other data should go to a default partition. Here’s how to create such a default partition:


This table will contain all data that is not supposed to go to any other partition.

Inserting into a partitioned table

Now, let’s insert into this table and see what happens:

sample=# \x
Expanded display is on.

sample=# INSERT INTO t_turnover (country, t, task, turnover) 
VALUES ('Uganda', now(), 'Some task', 200) 
RETURNING tableoid::regclass, *;
-[ RECORD 1 ]---------------------------
tableoid | t_rest
id       | 1
country  | Uganda
t        | 2022-11-01 09:35:38.991547+01
task     | Some task
turnover | 200

We’ve applied a little trick here: In the case of INSERT, you can use RETURNING *, which is a commonly used and simple way to see what you’ve just inserted. However, there is more: There is a hidden column called “tableoid”: It represents the object ID of the table you’ve just inserted. Note that this is the object ID of the partition – not of the parent table.

Selecting from a partitioned table

So far, you’ve seen that you can use the tableoid to determine the partition on INSERT. However, you can also do the same thing when you query the table:

sample=# SELECT tableoid, tableoid::regclass, * FROM t_turnover ORDER BY id;
 tableoid | tableoid  | id | country |               t               |        task        | turnover 
    27616 | t_rest    |  1 | Uganda  | 2022-11-01 09:35:30.193676+01 | Some task          |      200
    27597 | t_austria |  3 | Austria | 2022-11-01 11:11:40.583728+01 | PostgreSQL support |      900
(3 rows)

The regclass data type is a convenient way to turn an object ID into a human readable string. If you use this trick to determine the partition the data is in, you can apply even more trickery: Nobody stops you from using aggregate functions on this string to generate some statistics about the content of your partitions:

sample=# 	SELECT 	tableoid::regclass, count(*) 
FROM 		t_turnover 
 tableoid  | count 
 t_austria |     1
 t_rest    |     1
(2 rows)

The advantage over a query which focuses on the system catalog is that you can get a real count and not just estimates. It’s also a lot easier than unioning to the result of those individual partitions.

Finally …

If you want to learn more about PostgreSQL and if you happen to be interested in how to modify data structures efficiently, I want to recommend my post about “ALTER TABLE Done Right” on our website.

Also, if you want to learn more about PostgreSQL and if you are interested in other topics, feel free to leave a comment. We’re always eager to post more useful content that’s relevant to as many people out there as possible.