PostgreSQL - Check for equal values in two attributes

user_15864561 0
Viewed 77

I have a PostgreSQL 11.6 server. On this server, I have a database with table users with attributes id_user, id_manager, displayName, givenName, surname, active. I would like somehow to check if the row is inserted or updated that id_user and id_manager are not equal.

I created a trigger:

create trigger "Test"
    before insert or update
        of id_user, id_manager
    on users
    for each row
execute procedure "Test"();

and trigger function:

create function "Test"() returns trigger
    language plpgsql
as
$func$
BEGIN
  IF (SELECT id_user FROM users)
   = (SELECT id_manager FROM users) 
  THEN
    RAISE EXCEPTION 'User ID and Manager ID cannot be the same values!';
  END IF;
  RETURN NEW;
END
$func$;

alter function "Test"() owner to xxxxxx;

When I executed insert where id_user and id_manager have the same values, the insert was completed with no error.

Could anyone please help with that?

Thanks

triggers postgresql database
1 Answers

Use a check constraint:

ALTER TABLE users ADD CHECK (user_id <> manager_id);
user_64643080
Related