sql - How to check if unique column does not already contain data being inserted, preventing error

Question

I'm working with this database... basically I have to create a "promote_cyclist" trigger, to memorize the eligible athletes (who are ranked first on a stage of the Giro d'Italia) in the mesh table.

The tables are:

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) );

this is the trigger I load on the shell ...

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;

This is the command I pass to the shell:

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

what I would like is that there were no repetitions of the name of an athlete in a pink jersey (for this I put UNIQUE)... the trigger, however, does not understand it... the result is therefore an error since you try to insert more same names... can you help me? I do not know how to do it...

question from:https://stackoverflow.com/questions/65929046/how-to-check-if-unique-column-does-not-already-contain-data-being-inserted-prev

Answer 1

Your query in that function may look like this:

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 keyword will make sure that there will be no repetitions in result when selecting c.name_cyclist and another condition c.name_cyclist NOT IN (SELECT magliarosa.name FROM magliarosa) will make sure you will be not inserting values that are already in magliarosa table.