Partition PostgreSQL: which partition did I INSERT my data into?

11.2022 / Category: / Tags: |

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:

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:

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:

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:

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.

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
1 year ago

Marie Anna Baovola
Marie Anna Baovola
1 year ago

Thank you for sharing this interesting blog. Just few questions about the first command of the "Selecting from a partitioned table", I didn't catch why it is written 3 rows? However it lists only 2? And, why the id was 1 and 3? (Or maybe, you didn't show some insert and delete row). Thank you again.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram