0

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:

  1. Array of tags like ['mytag1', 'mytag2, ...];
  2. id of an entity related to the tags (post for instance).

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!

embu
  • 1
  • 2

2 Answers2

0

Is this what you want?

INSERT INTO entitlyLinks (id, tag)
    SELECT 1, tag
    FROM unnest($1::text[]) t(tag);

Note: I would recommend calling id post_id, so it is clear what it refers to.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your reply! But what does "t(tag)" mean? Where did the "t" came from? – embu Dec 29 '20 at 13:36
  • @embu . . . It is an optional table alias. However, it is not really needed in this context, so you can also use `unnest($1::text[]) tag`. – Gordon Linoff Dec 29 '20 at 14:13
0

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[];
embu
  • 1
  • 2