BOOLEAN
BOOLEAN
The SQL-compliant BOOLEAN
data type (8 bits) comprises the distinct truth values TRUE
and FALSE
.Unless prohibited by a NOT NULL
constraint, the BOOLEAN
data type also supports the truth value UNKNOWN
as the null value.The specification does not make a distinction between the NULL
value of this data type, and the truth value UNKNOWN
that is the result of an SQL predicate, search condition, or Boolean value expression: they may be used interchangeably to mean the same thing.
As with many programming languages, the SQL BOOLEAN
values can be tested with implicit truth values.For example, field1 OR field2
and NOT field1
are valid expressions.
IS
OperatorPredicates can use the operator Boolean IS [NOT]
for matching.For example, field1 IS FALSE
, or field1 IS NOT TRUE
.
Note
|
|
BOOLEAN
ExamplesInserting and selecting
CREATE TABLE TBOOL (ID INT, BVAL BOOLEAN);
COMMIT;
INSERT INTO TBOOL VALUES (1, TRUE);
INSERT INTO TBOOL VALUES (2, 2 = 4);
INSERT INTO TBOOL VALUES (3, NULL = 1);
COMMIT;
SELECT * FROM TBOOL;
ID BVAL
============ =======
1 <true>
2 <false>
3 <null>
Test for TRUE
value
SELECT * FROM TBOOL WHERE BVAL;
ID BVAL
============ =======
1 <true>
Test for FALSE
value
SELECT * FROM TBOOL WHERE BVAL IS FALSE;
ID BVAL
============ =======
2 <false>
Test for UNKNOWN
value
SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN;
ID BVAL
============ =======
3 <null>
Boolean values in SELECT
list
SELECT ID, BVAL, BVAL AND ID < 2
FROM TBOOL;
ID BVAL
============ ======= =======
1 <true> <true>
2 <false> <false>
3 <null> <false>
PSQL declaration with start value
DECLARE VARIABLE VAR1 BOOLEAN = TRUE;
Valid syntax, but as with a comparison with NULL
, will never return any record
SELECT * FROM TBOOL WHERE BVAL = UNKNOWN; SELECT * FROM TBOOL WHERE BVAL <> UNKNOWN;