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.

select partman.create_parent(p_parent_table := 'public.users', 
	p_control := 'created_at', 
	p_type := 'partman', 
	p_interval := 'daily', 
	p_automatic_maintenance := 'on',
	p_upsert := 'ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email, last_name = EXCLUDED.last_name, updated_at = EXCLUDED.updated_at',
	p_debug := TRUE);

Note
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

insert into users 
        (id, first_name, last_name, email, created_at, updated_at)
	values (1, 'foo', 'bar', 'foo@exmplae.com', now(), now()),
	       (2, 'abc', 'pqr', 'abc@example.com', now(), now());

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.