'CREATE Rule insert in postgresql

I have three tables. I need the id value not to intersect between two tables (relative one to one). For example user => student and lecturer. the user cannot be both a teacher and a student. decided to implement it through the rule insert, but there is a problem with infinite recursion.

In the example I try to find new.IdPersonal in the lecturer table and if there is no it then to add new record to the student:

CREATE OR REPLACE RULE "InsertId" AS ON INSERT TO "Student" 
    WHERE (NOT (NEW."IdPersonal" IN ( SELECT "Lecturer"."IdPersonal" FROM public."Lecturer")))
    DO INSERT INTO "Student"("Id", "IdPersonal", "IdGroup") 
    OVERRIDING SYSTEM VALUE VALUES (null, New."IdPersonal", New."IdGroup");
sqlpostgresql


Solution 1:[1]

The simple solution for that is to use the same sequence to fill the id for both tables.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Laurenz Albe

Related Questions