CODEWITHSUNDEEP
sqlpostgresqlcreate-tablecheck-constraints
Thomas Lang
Thomas Lang

Reputation: 1495

Conditional Not Null Constraint

Is there a way to model the following behaviour with constraint checks (or other functions) in postgres SQL:

I have two columns:

If oncost is true, i want oncostdescription to be mandatory (NOT NULL, NOT EMPTY)

If concost is false, i want oncostdescription not to be mandatory (NULL, EMPTY)

EDIT:
I forgot to state my postgres version being lower than 12.
Some folks here came up with a cool feature called generated columns.
Seems pretty cool. But only if you have 12+

Upvotes: 1

Views: 985

Answers (3)

wildplasser
wildplasser

Reputation: 44250

The oncost column is redundant (fully dependant on oncostdescription), so it can be computed when desired. Postgres-12 supports generated always as ... columns:

CREATE TABLE omg
        ( seq integer not null generated always as identity
        , oncostdescription varchar
        , oncost boolean NOT NULL generated always as ( oncostdescription IS NOT NULL AND oncostdescription > '') STORED
        );

insert into omg(oncostdescription) VALUES ( NULL), (''), ('a'), (' ');

select * from omg;

Result:

CREATE TABLE
INSERT 0 4
 seq | oncostdescription | oncost 
-----+-------------------+--------
   1 |                   | f
   2 |                   | f
   3 | a                 | t
   4 |                   | t
(4 rows)

And sice the oncost field is computable, it could also be packed in a view: (with exactly the same result)

CREATE VIEW vomg AS
SELECT seq, oncostdescription
        , ( oncostdescription IS NOT NULL AND oncostdescription > '')::boolean AS oncost
FROM omg
        ;

SELECT * FROM vomg;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If I understand correctly, a generated column may do what you want:

create table t (
     . . . 
     oncostdescription varchar,
     oncost boolean generated always as (oncostdescription is not null)
);

This assumes that you want oncost as a boolean flag indicating whether or not oncostdescription has a value -- which is consistent with how the question is phrased.

Upvotes: 1

GMB
GMB

Reputation: 222722

You can use a check constraint:

create table mytable (
    oncost boolean
    oncostdescription varchar(50),
    constraint ck_mytable check(
        not oncost 
        or not (oncostdescription is null or oncostdescription = '')
    )
)

This phrases as: either the boolean flag is false, or the description is neither null nor empty.

You could also express this as: 

create table mytable (
    oncost boolean
    oncostdescription varchar(50),
    constraint ck_mytable check(
        not (
            oncost 
            and (oncostdescription is null or oncostdescription = '')
        )
    )
)

Upvotes: 2

Related Questions