CODEWITHSUNDEEP
postgresql
AppleBud
AppleBud

Reputation: 1541

On Conflict Do Update not working on partitioned table POSTGRES

i am trying to perform an Upsert on a partitioned table with postgres 12.8 . The query is using CTE to perform the operation with insert followed by update but i get the following error :

     [23505] ERROR: duplicate key value violates 
unique constraint "books_20221201_pkey" Detail: 
Key (book_id, book_name)=(1,"abcd") already exists.
 Where: SQL statement "INSERT INTO public.books_20221201 VALUES (NEW.*)"

below is the query :

WITH bk_temp(book_id, book_name) AS (
            SELECT * FROM ( VALUES
                (1,
                 "abcd"
                )
            ) AS a(book_id, book_name))

            INSERT INTO books
            (book_id,
            book_name)
            (Select t.book_id, t.book_name
             FROM bk_temp t
            ON CONFLICT(book_id, book_name)
            DO Update
            SET book_name="defg");

Upvotes: 1

Views: 557

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246848

The documentation leaves no doubt:

INSERT statements with ON CONFLICT clauses are unlikely to work as expected, as the ON CONFLICT action is only taken in case of unique violations on the specified target relation, not its child relations.

The partitioned table itself does not contain any data...

Upvotes: 3

Related Questions