The IS
Operator
Predicates can use the operator Boolean IS [NOT]
for matching.For example, field1 IS FALSE
, or field1 IS NOT TRUE
.
Note
|
|
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;
Although BOOLEAN
is not inherently convertible to any other data type, the strings 'true'
and 'false'
(case-insensitive) will be implicitly cast to BOOLEAN
in value expressions.For example:
if (true > 'false') then ...
The value 'false'
is converted to BOOLEAN
.Any attempt to use the Boolean operators AND
, NOT
, OR
and IS
will fail.NOT 'False'
, for example, is invalid.
A BOOLEAN
can be explicitly converted to and from string with CAST
.UNKNOWN
is not available for any form of casting.
Note
|
Other Notes
|
Note
|
The types [fblangref50-datatypes-chartypes-binary] and [fblangref50-datatypes-chartypes-varbinary] are covered earlier in section [fblangref50-datatypes-chartypes]. |
BLOB
s (Binary Large Objects) are complex structures used to store text and binary data of an undefined length, often very large.
BLOB [SUB_TYPE <subtype>] [SEGMENT SIZE <segment size>] [CHARACTER SET <character set>] [COLLATE <collation name>]
If the SUB_TYPE
and CHARACTER SET
clauses are absent, then subtype BINARY
(or 0
) is used.If the SUB_TYPE
clause is absent and the CHARACTER SET
clause is present, then subtype TEXT
(or 1
) is used.
BLOB (<segment size>) BLOB (<segment size>, <subtype>) BLOB (, <subtype>)
Note
|
Formally, the |
Specifying the BLOB segment size is a throwback to times past, when applications for working with BLOB data were written in C (Embedded SQL) with the help of the gpre pre-compiler.Nowadays, it is effectively irrelevant.The segment size for BLOB data is determined by the client side and is usually larger than the data page size, in any case.