Insert…on conflict with partitions finally works in PostgreSQL 11

Another partitioning improvement for PostgreSQL 11: Insert…on conflict is now supported (for most cases) in PostgreSQL 11 thanks to this commit. Lets see how it works.

We’ll again use the slightly modified little list partitioned table from the last post, here in PostgreSQL 10:

postgres=# select version();
                                                          version                                       Insert                    
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
 
postgres=# create table part ( a int, b int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# create unique index iu1 on part_1 (a);
CREATE INDEX
postgres=# create unique index iu2 on part_2 (a);
CREATE INDEX
postgres=# d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = ANY (ARRAY['beer'::character varying(5)])))
Indexes:
    "iu1" UNIQUE, btree (a)

postgres=# d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = ANY (ARRAY['wine'::character varying(5)])))
Indexes:
    "iu2" UNIQUE, btree (a)

Insert some sample data:

postgres=# insert into part(a,b,list) values (1,1,'beer');
INSERT 0 1
postgres=# insert into part(a,b,list) values (2,2,'beer');
INSERT 0 1
postgres=# select * from part;
 a | b | list 
---+---+------
 1 | 1 | beer
 2 | 2 | beer
(2 rows)

When you try the following in PostgreSQL 10 it will fail:

postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2;
ERROR:  ON CONFLICT clause is not supported with partitioned tables

Doing that directly on the partition is working:

postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2;
ERROR:  ON CONFLICT clause is not supported with partitioned tables
postgres=# insert into part_1(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2;
INSERT 0 1
postgres=# select * from part;
 a | b | list 
---+---+------
 2 | 2 | beer
 1 | 2 | beer
(2 rows)

But who wants to do that directly on the partition? For sure this is a big limitation and not very useful. That got fixed now with PostgreSQL 11:

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, b int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# alter table part add constraint pk1 primary key (a,list);
ALTER TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "pk1" PRIMARY KEY, btree (a, list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')


postgres=# insert into part(a,b,list) values (1,1,'beer');
INSERT 0 1
postgres=# insert into part(a,b,list) values (2,2,'wine');
INSERT 0 1
postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set b = 2;
INSERT 0 1

Cool. But be aware that there still is a case which is not working:

postgres=# select * from part;
 a | b | list 
---+---+------
 1 | 2 | beer
 2 | 2 | wine
(2 rows)

postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set list='wine';
ERROR:  invalid ON UPDATE specification
DETAIL:  The result tuple would appear in a different partition than the original tuple.

When you do an on conflict clause that would require to move the row into another partition (updating the partition key) it will fail.