时间戳

首页 > 解决方案 > 跳过修改 CTE 并返回现有键而不是插入新键（如果值已存在）

php - 跳过修改 CTE 并返回现有键而不是插入新键（如果值已存在）

问题描述

我正在 PostgreSQL 中编写一个查询，我在我的 php 代码中使用该查询，用户应该能够通过输入演员名称将演员添加到演员列表中。我正在使用修改 CTE 来执行此操作，因为它需要更新数据库中的三个表：

到目前为止的查询：

WITH person_cte AS (
     INSERT INTO Person(Name)
     SELECT :actorname
     WHERE NOT EXISTS (
         SELECT 1 FROM person WHERE name = :actorname2)
     RETURNING person_id
     ),
     personnel_cte AS (
     INSERT INTO Personnel(role, person_id)
     SELECT 'Actor', person_id FROM person_cte
     RETURNING personnel_id
     )
     INSERT INTO FilmPeople(Personnel_ID, Media_ID)
     SELECT personnel_id, :id from personnel_cte¨

所以你们可能看到的问题是，到目前为止，我的查询仅在数据库中根本不存在参与者时才有效。如果在 Person 表中找到演员，它将失败。我真正想要的是如果演员亲自存在，那么不要插入任何东西，而是将已经存在的 person_id 返回到下一个 cte。然后同样的事情，如果该演员的人员（角色）已经存在，则继续使用personal_id，仅将演员添加到最后一个表以将他/她连接到电影。然后即使在这里，如果它已经存在，那么不要做任何事情。

编辑：这是现在创建表的方式：

CREATE TABLE Person(
Person_ID serial,
Name varchar(255),
DateOfBirth date,
Gender varchar(45),
CONSTRAINT PK_Person PRIMARY KEY (Person_ID)
);

CREATE TABLE Personnel(
Personnel_ID serial,
Role varchar(255),
Person_ID bigint,
CONSTRAINT PK_Personnel PRIMARY KEY (Personnel_ID),
CONSTRAINT FK_PersonPersonnel FOREIGN KEY (Person_ID)
REFERENCES Person(Person_ID)
);

CREATE TABLE FilmPeople(
Personnel_ID bigint,
Media_ID int,
CONSTRAINT PK_FilmPeople PRIMARY KEY (Personnel_ID, Media_ID),
CONSTRAINT FK_PersonnelFilmPeople FOREIGN KEY (Personnel_ID)
REFERENCES Personnel(Personnel_ID),
CONSTRAINT FK_MediaFilmPeople FOREIGN KEY (Media_ID)
REFERENCES Media(Media_ID)
);

标签： phpsqlpostgresqlsql-insertcommon-table-expression

解决方案

当没有插入任何内容时，该returning子句返回为空 - 所以这不是您想要的方法。相反，我会建议加入表格。为了使其正常工作，我强烈建议在每个表上使用唯一键，如下所示：

person(name)
personnel(role,person_id)
filmpeople(personnel_id, media_id)

然后你可以使用on conflict：

with 
    person_cte as (
        insert into person(name)
        values (:actorname)
        on conflict (name) do nothing
    ),
    personnel_cte as (
        insert into personnel(role, person_id)
        select 'actor', p.person_id 
        from person p
        where p.name = :actorname
        on conflict (role, person_id) do nothing
     )
insert into filmpeople(personnel_id, media_id)
select personnel_id, :id 
from pl.personnel pl
inner person p on p.person_id = pl.person_id
where p.name = :actorname and pl.role = 'actor'
on conflict (personnel_id, media_id) do nothing

推荐阅读