PostgreSQL: CheckConstraint based on another Column

Question

In PostgreSQL, I want a check constraint if column "Art" (this never is NULL) is == 'Typ 3' and three other columns either all NULL or all NOT NULL. My code fails for the first record having 'Type 2', NULL, NULL, NULL, NULL.

ALTER TABLE "SandBox"."T Test" 
ADD CONSTRAINT  "T Test_Serie_check" CHECK (
    "Art" = 'Type 3' 
    AND (
         ("ID-Serie_fkey" IS NULL     
          AND "Type" IS NULL     
          AND "Nr" IS NULL     
          AND "NrTotal" IS NULL)
        OR
         ("ID-Serie_fkey" IS NOT NULL 
          AND "Type" IS NOT NULL 
          AND "Nr" IS NOT NULL 
          AND "NrTotal" IS NOT NULL)
        )
    );

How to solve this?

Your constraint only allows entering the value 'Type 3' for Art. If you want to be able to insert 'Type 2', then that is not the constraint you want. What do you actually want? — Bergi
– Bergi, Commented Nov 14, 2024 at 10:10
Thanks, I wand t a constraint to check only records having Art = 'Type 3'; if so, the other three columns need to be either all NULL or all NOT NULL. If Art = 'Type 1' or Art = 'Type 2' there should be no check. — StOMicha
– StOMicha, Commented Nov 14, 2024 at 10:17
Like this? ALTER TABLE "SandBox"."T Test" ADD CONSTRAINT "T Test_Serie_check" CHECK ( IF "Art" = 'Type 3' THEN ( ("ID-Serie_fkey" IS NULL AND "Type" IS NULL AND "Nr" IS NULL AND "NrTotal" IS NULL) OR ("ID-Serie_fkey" IS NOT NULL AND "Type" IS NOT NULL AND "Nr" IS NOT NULL AND "NrTotal" IS NOT NULL) ) ); NO: SyntaxError at »IF« — StOMicha
– StOMicha, Commented Nov 14, 2024 at 10:26

Belayer · Accepted Answer · 2024-11-15 19:19:44Z

0

In cases like this Postgres provides a quite useful pair of functions: num_nulls() and num_nonnulls(). Given a list of columns they return the number of columns that are either null or not null as appropriate. This may make you code easier to read by elminating case structure. (see demo here)

constraint serie_check check (    art <> 'Type 3' 
                              or  art = 'Type 3' and 
                                  num_nulls (fkey, type, nrtotal) in (0,3) 
                             )

answered Nov 15, 2024 at 19:19

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: CheckConstraint based on another Column

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