PostgreSQL: check if attribute is in table

Question

I'm writing some triggers to check if an arrtibute of a newly create tuple is present in another table. What is the best way to check this?

IF (SELECT * FROM TABLEB where NEW.Attribute = Attribute) = NULL THEN
return NULL
END IF

Is there a better way? And does a Select that returns nothing = NULL or empty set?

Paul Bellora · Accepted Answer · 2011-09-15 03:41:19Z

1

Use IF EXISTS (SELECT * FROM TABLEB where NEW.Attribute = Attribute).

An empty rowset isn't the same as NULL, which represents an empty cell of a row. Your syntax would only be valid if the query only selected one row, in which case PostgreSQL will allow use of comparison operators against the top cell returned, which counts as NULL if none are returned.

edited Sep 15, 2011 at 3:41

answered Sep 15, 2011 at 3:08

Paul Bellora

55.4k18 gold badges135 silver badges185 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: check if attribute is in table

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