Re: Inserting many rows using "with"

On Fri, Sep 11, 2020 at 1:31 PM Harmen <harmen(at)lijzij(dot)de> 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.