There's a table entityLinks and I would like to insert multiple values by one query. The table was defined using this query:
CREATE TABLE entityLinks(
id INTEGER NOT NULL references posts(id) ON DELETE CASCADE,
tag VARCHAR(255) NOT NULL references tags(tag) ON DELETE CASCADE);
Data intended to be used for insertion looks like this:
I can combine them into array of objects ( [{id: 1, tag: 'mytag1'}, {id:1, tag: 'mytag2'}, ... ] ) if needed. Id is the same for all the tags in this query, but is different from one query to another.
I know how to insert multiple tags
INSERT INTO tags(tag)
SELECT * FROM unnest($1::text[])
($1 - is a variable, passed as value like this this.pool.query(query, [tags]) );
... but when I tried the same, the unnest
has unnested all levels of arraying ([ [1, 'mytag1'], [1, 'mytag2'],... ] => "1, 'mytag1', 1, 'mytag2', ... ".
And the error was : error: malformed record literal: "1" (1 - is the id)
I tried using an array of objects, but got this error: malformed record literal: "{"id":179,"tag":"myTag1"}"
Basically I would like to insert different tags linked with the same id (the same for one query), but also would be interested to understand how to insert multiple objects at once (probably will be useful in the future).
Thanks in advance!
with help of @Gordon Linoff I've composed the right query
INSERT INTO entityLinks(post_id, tag)
SELECT $1, tag
FROM unnest($2::text[]) as tag;
maybe will be useful for someone in the future. the data is passed like this:
this.pool.query(queries.addLinks, [post_id, tags]);
post_id: number, tags: string[];