How to insert multiple rows in a single PostgreSQL query?

[namndev]

Hi @kyleconroy,

The first, I really like this repo. Thank for your effort,

Now, I want inserting multiple rows in a single query like:

INSERT INTO public."Item" ("Id", name)
VALUES  ('1', 'name1'),
        ('2', 'name2'),
        ('3','name3')

I don't know how to write in sqlc?

[namndev]

Hi,
I came up with a solution to the above problem:

-- name: AddItems :execrows

INSERT INTO Item (Id, name) VALUES (UNNEST(@ids::INT8[]) , UNNEST(@names::varchar[]));

However, with where clause like:

SELECT * FROM item WHERE (id = '1' AND name = 'name1') OR (id = '2' AND name = 'name2') OR (id = '3' AND name = 'name3')

I don't know how to write in sqlc?

[kyleconroy]

Sadly, this isn't currently supported. Your unnest solution is interesting, I wonder how well it performs for a large number of records. How many records are your trying to insert? PostgreSQL has a guide for inserting large amounts of data. If you aren't inserting many records, I'd just use a transaction. Sorry that there isn't a better answer.

This has also been discussed in #216 and #218, which you may find helpful.

[namndev]

Thank @kyleconroy .