Insert into partitioned tables with select, handle conflicts

Question

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?

Answer 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.