PostgreSQL - Check for equal values in two attributes

Question

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

Laurenz Albe · Accepted Answer · 2021-05-07 15:38:10Z

2

Use a check constraint:

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

answered May 7, 2021 at 15:38

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - Check for equal values in two attributes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related