On Fri, Sep 11, 2020 at 1:31 PM Harmen <harmen@xxxxxxxxx> wrote:
Hi all,
Are there any known problems with this strategy? Are they any other methods of
inserting lots of records in a nicer way?
I do this all the time with insert and it's wonderful. It can get tricky if you need to do UPDATEs.
You can avoid the unnest and just use value rows directly if you want (which can be easier to read)l
with ids(id) as
( values (1),
(2),
(3)
) select id from ids;
Further, you can use this technique with multiple CTEs to insert into completely different tables all in the same query saving lots of round trips.
Alternatives I know of, and the only options I could find documented:
- create a huge custom insert statement
- use COPY, but that doesn't work with 'ON CONFLICT' as far as I can see
COPY is great for large amounts of data. If it's a huge amount of data, you can load it into a temp table with COPY, and then use your ON CONFLICT insert to implement your logic.
