Batch upserts in child tables of a partition set created using pg partman

This post is in continuation to the previous post. This post deals with the problem of batch upserts in different child tables of same partition set created using pg_partman.

This uses ON CONFLICT clause. This clause specifies an alternative action for raising a unique violation or exclusion constraint violation error. The _createparent() function in partman schema allows us to specify the alternative action that ON CONFLICT clause will use in the case when insertion returns a violation error.

Specify an action for ON CONFLICT while creating partition set

following function creates partition set as in the previous post, but here an action is passed for ON CONFLICT to handle the case of unique constraint violation.

The column on which ON CONFLICT is used must have unique constraint defined on it. In the above example, the column id is the primary key of the users table defined in the previous post.

Insert statement that will cause violation error

Let’s say if records corresponding to above insert statement already exist in different/same child tables then it will end up using the ON CONFLICTclause and will do the defined action. As per this example, it will update the email, last_name and updated_at columns of users table. If there is any suggestion please reach out to me.

Product Engineer at Gojek.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store