Re: Inserting many rows using "with"

Поиск
Список
Период
Сортировка
От Brian Dunavant
Тема Re: Inserting many rows using "with"
Дата
Msg-id CAJ2+uGU5WvJ2eME+Pppe=wam5Bp-x4=gW_O_WyPL4Lsz3UjWiw@mail.gmail.com
обсуждение исходный текст
Ответ на Inserting many rows using "with"  (Harmen <harmen@lijzij.de>)
Список pgsql-general
Дерево обсуждения


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

 

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Feature Request pgadmin4 support more storage backends for servers/user data
Следующее
От: Alexander Farber
Дата:
Сообщение: How to call JSONB_INSERT with integer as the new to-be-inserted value?