Postgres - insert with manytoone relation

Question

I am using Postgres 14 and writing an sql query so I can insert a new row into tag table:

id	team_id	name
2	4	London On

As simple as:

 INSERT INTO tag (team_id, name) SELECT :teamId, :name;

    $stmt->bindValue("teamId", 4, PDO::PARAM_INT);
    $stmt->bindValue("name", 'London On', PDO::PARAM_STR);
    $stmt->bindValue("teamMemberId", 428, PDO::PARAM_STR);

In tag table I have ManyToOne relation with team_member_tag table that should look like:

id	team_member_id	tag_id
314	428	2

I want to implement INSERT of the data into team_member_tag table when doing an insert into tag table.. I have all params needed as I wrote in an example.

Tried:

$stmt = $this->getConnection()->prepare(
        '
            INSERT INTO tag
            (
             team_id,
             name
            ) VALUES (
               :teamId,
               :name,
            ) RETURNING tag.id
            '
    );

    $stmt->executeQuery();

    $stmt2 = $this->getConnection()->prepare('
    INSERT INTO team_member_tag (team_member_id, tag_id)
        VALUES
        ( :workspaceMemberId,
          (select id from tag)
        )
    ');

    $stmt2->bindValue('workspaceMemberId', $workspaceMemberId);
    $stmt2->executeQuery();

And now the error:

Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression

Can you please help? Thanks

Ramin Faracov · Answer

This is incorrect syntax:

INSERT INTO team_member_tag (team_member_id, tag_id)
VALUES
( :workspaceMemberId,
  (select id from tag)
)

Because select id from tag may be gets many records but inside values must be only one value.

For inserting multiple rows to table you can use this syntax, for example:

INSERT INTO team_member_tag (tag_id)
select id from tag

But if you need insert into team_member_tag table only inserted tag values you can use this syntax:

with t1 as (
    INSERT INTO tag (team_id, name) VALUES (:teamId,:name)
    RETURNING id
) 
insert into team_member_tag (team_member_id, tag_id)
select 10, t1.id from t1

10 - sample value for team_member_id field.

Postgres - insert with manytoone relation

1 Answers