CODEWITHSUNDEEP
postgresqlpartitioningupsertpostgresql-10
DWilches
DWilches

Reputation: 23035

Insert multiple rows with onConflict clause in partitioned table in Postgres 10

I have a statement like the following in Postgres 10:

INSERT INTO my_table(A, B)
       VALUES (val_A1, val_B1),
              ...
              (val_A99, val_B99)
       ON CONFLICT DO NOTHING;

Which was working well, but later I partitioned my table and then this statement started to fail:

org.postgresql.util.PSQLException: ERROR: 
ON CONFLICT clause is not supported with partitioned tables

As Postgres 10 doesn't support ON CONFLICT clauses on partitioned tables:

Using the ON CONFLICT clause with partitioned tables will cause an error, because unique or exclusion constraints can only be created on individual partitions. There is no support for enforcing uniqueness (or an exclusion constraint) across an entire partitioning hierarchy.

If I were inserting a single row I could attempt the INSERT and control the error with a try-catch if the record was already there.

But being that I'm inserting multiple values, what alternatives do I have besides this one:

INSERT INTO my_table
       SELECT T.A, T.B
       FROM (VALUES(val_A1, val_B1), 
                   ...,
                   (val_A99, val_B99)) AS T(A, B),
       WHERE NOT EXISTS (
           SELECT 1 FROM my_table AS T2
                    WHERE T2.A = T.A
                      AND T2.B = T.B
       );

Upvotes: 0

Views: 2083

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246848

Your best option is to upgrade to PostgreSQL v11, where this will work.

PostgreSQL v11 supports PRIMARY KEY and UNIQUE constraints on partitioned tables (if the partition key is part of the index), and that is a requisite for INSERT ... ON CONFLICT.

Upvotes: 1

Related Questions