-
Notifications
You must be signed in to change notification settings - Fork 923
How to insert multiple rows in a single PostgreSQL query? #730
-
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?
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 4
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.
Replies: 2 comments 1 reply
-
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?
Beta Was this translation helpful? Give feedback.
All reactions
-
🚀 1
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
Thank @kyleconroy .
Beta Was this translation helpful? Give feedback.