I made a constraint where to mark the column completed to true some of the other columns would have to have a value.
But for some reason the constraint does not complain when I leave a specified column blank when completed is marked true. I have also purposely inserted NULL a specified column and still no constraint.
Any ideas?
CREATE TABLE info (
id bigserial PRIMARY KEY,
created_at timestamptz default current_timestamp,
posted_by text REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE,
title character varying(31),
lat numeric,
lng numeric,
contact_email text,
cost money,
description text,
active boolean DEFAULT false,
activated_date date,
deactivated_date date,
completed boolean DEFAULT false,
images jsonb,
CONSTRAINT columns_null_check CHECK (
(completed = true
AND posted_by != NULL
AND title != NULL
AND lat != NULL
AND lng != NULL
AND contact_email != NULL
AND cost != NULL
AND description != NULL
AND images != NULL) OR completed = false)
);
In Chapter 9. Functions and Operators:
To check whether a value is or is not null, use the predicates:
expression IS NULL expression IS NOT NULLor the equivalent, but nonstandard, predicates:
expression ISNULL expression NOTNULL
Therefore you can not use value != NULL to check null values, you can only use value IS NULL and value IS NOT NULL.
For boolean values they are the same:
Boolean values can also be tested using the predicates
boolean_expression IS TRUE boolean_expression IS NOT TRUE boolean_expression IS FALSE boolean_expression IS NOT FALSE boolean_expression IS UNKNOWN boolean_expression IS NOT UNKNOWN