CODEWITHSUNDEEP
sqlpostgresql
AnTiX
AnTiX

Reputation: 1

Control 3 conditions on a IF in Postgresql

I need to control 3 conditions on a IF incoming from a trigger IF one of three is null. Follow code only controls the first value (puntuacions) but no next.

create or replace function p_controla() returns trigger as $controla_puntuacions$
begin
IF new.puntuacio or new.contingut or new.data IS NULL
THEN
RAISE 'ONE of the columns is empty';
ELSE
RAISE ' ITS OK! ';
END IF;
END;
$controla_puntuacions$ language plpgsql;

create trigger controla_puntuacions
before insert on puntuacions
for each row execute procedure p_controla();

Upvotes: 0

Views: 110

Answers (2)

eshirvana
eshirvana

Reputation: 24568

try to test concatenate them , if any of them is null the result would be null:

create or replace function p_controla() returns trigger as $controla_puntuacions$
begin
IF (new.puntuacio::text || new.contingut::text || new.data::text) IS NULL
THEN
RAISE 'ONE of the columns is empty';
ELSE
RAISE ' ITS OK! ';
END IF;
END;
$controla_puntuacions$ language plpgsql;

Upvotes: 0

maksymsan
maksymsan

Reputation: 66

IS NULL has a higher precedence than OR: https://www.postgresql.org/docs/11/sql-syntax-lexical.html#SQL-PRECEDENCE

So your IF statement works like this:

IF new.puntuacio or new.contingut or (new.data IS NULL)

Correct IF statement:

IF new.puntuacio IS NULL or new.contingut IS NULL or new.data IS NULL

Upvotes: 2

Related Questions