0

I need to insert into partitioned tables using a select, how do I handle conflicts? Postgres complains this: [0A000] ERROR: ON CONFLICT clause is not supported with partitioned tables

My query looks like this:

INSERT INTO table_partition SELECT * FROM old_table WHERE start_time >= '2014-12-01 00:00:00' AND start_time < '2015-01-01 00:00:00' ON CONFLICT ON CONSTRAINT table_partition_201412_pkey DO NOTHING ;

Currently, I have created the partition tables with the old table having triggers to copy insert/update/delete onto the partition tables. I've to backfill the older data onto partitions, once done, I can drop the older tables and start using the partitioned tables, but there's a chance some of the data with few date ranges might overlap. It's a problem for me. Could you please help?

1

Do the same "by hands" like

WITH cte AS ( INSERT INTO dest
              SELECT FROM src
              WHERE (non-matching condition)
            )
UPDATE dest
SET dst.fieldset = src.fieldset
FROM src
WHERE (matching condition)

Dependent by the data and matched/non-matched relation you may swap inserting and updating parts or return processed records identifiers from CTE and use it in main query.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.