如何检查唯一列是否已经包含要插入的数据，以防止错误

Question

我正在使用这个数据库…基本上我需要创建一个"promote_cyclist"触发，在网格表中记忆符合条件的运动员(在环意大利的一个阶段中排名第一)。

表是:

cyclist (cyclist_id: Int, name_cyclist: string, team: string of three letters, country: three-letter string)
tape (_name: string, km: int, type: 'flat' or 'high_mountain' or 'medium_mountain' or 'chronometro_a_team' or 'chronometro_individual' or 'time_trial')
arrival_order (cyclist_id:int ; tape_name:string, order: int): where cyclist_id (resp, tape_name) is an external key that refers to cyclist (resp, tape);
magliarosa( name:string (UNIQUE) );

这是我加载在外壳上的触发器…

CREATE FUNCTION promote_cyclist()
RETURNS TRIGGER AS
$$
BEGIN
INSERT INTO magliarosa
SELECT c.name_cyclist
FROM cyclist c
WHERE new.cyclist_id = c.cyclist_id AND
new.order = 1;
RETURN NEW;
END ;
$$
LANGUAGE plpgsql;

这是我传递给shell的命令:

CREATE TRIGGER promote_cyclist
BEFORE INSERT ON arrival_order
EXECUTE PROCEDURE promote_cyclist ();

我想要的是没有重复穿粉红色球衣的运动员的名字(这里我用UNIQUE)…然而，触发器不理解它……因此，结果是一个错误，因为您试图插入更多相同的名称…你能帮我吗?我不知道该怎么做……

Answer 1

该函数中的查询可能如下所示:

INSERT INTO magliarosa
SELECT DISTINCT(c.name_cyclist)
FROM cyclist c
WHERE new.cyclist_id = c.cyclist_id AND
new.order = 1 AND 
c.name_cyclist NOT IN (SELECT magliarosa.name FROM magliarosa);

DISTINCT关键字将确保在选择c.name_cycling时结果不会重复，另一个条件c.name_cyclist NOT IN (SELECT magliarosa.name FROM magliarosa)将确保您不会插入已经在magliarosa表中的值。

Answer 2

也许你想要INSERT ... ON CONFLICT，以便在它已经存在时更新名称:

CREATE FUNCTION promote_cyclist() RETURNS TRIGGER AS
$$BEGIN
INSERT INTO magliarosa (name)
SELECT c.name_cyclist
FROM cyclist c
WHERE NEW.cyclist_id = c.cyclist_id AND
NEW.order = 1
ON CONFLICT ON (name)
DO UPDATE SET name = EXCLUDED.name;
RETURN NEW;
END;$$
LANGUAGE plpgsql;