CODEWITHSUNDEEP
postgresql
Zangador
Zangador

Reputation: 3

How to properly write a tuple constraint in PostgreSQL

I am learning Databases with the PostgreSQL technology. Currently I have created a database and a few constrains with it. The problem is with creating a Tuple constraint. The exercise is saying : Tuple constraint: for all presidents born after 1800, the party can never be ’WHIG’.

I will kindly appreaciate you help ! :)

I`ve tried writing the code for the constraint, but I got into a syntax error. I am aware that I should use 'then' keyword. 

alter table president
    add constraint PresidentBornAfter1800
        check((when party in 'WHIG') then (birth_year > 1800));

SQL Error [42601]: ERROR: syntax error at or near "when" Position: 71

Upvotes: 0

Views: 349

Answers (1)

user330315
user330315

Reputation:

IN requires a list of values, but as you only want to check a single one, = is enough. But you also need to include the "other" case in the boolean expression.

So you need something like this:

alter table president
    add constraint PresidentBornAfter1800
        check( (party = 'WHIG' and birth_year > 1800) or (party <> 'WHIG') );

You could also express that using a CASE expression:

alter table president
    add constraint PresidentBornAfter1800
        check(case when party = 'WHIG' then birth_year > 1800 else true end));

Again, the result of the expression needs to be a boolean value, that's why you need the else true part - otherwise it would be null

Upvotes: 1

Related Questions