CODEWITHSUNDEEP
sqlnode.jspostgresqlsql-insertnode-pg-pool
embu
embu

Reputation: 1

How to insert multiple objects into one table?

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!

Upvotes: 0

Views: 841

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 0

embu
embu

Reputation: 1

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[];

Upvotes: 0

Related Questions