8.6. Boolean Type#
Postgres Pro provides the standardSQL type Table 8.19. Boolean Data Type Boolean constants can be represented in SQL queries by the SQL key words The datatype input function for typeboolean; seeTable 8.19. Theboolean type can have several states:“true”,“false”, and a third state,“unknown”, which is represented by theSQL null value.Name Storage Size Description boolean1 byte state of true or false TRUE,FALSE, andNULL.boolean accepts these string representations for the“true” state:
true |
yes |
on |
1 |
and these representations for the“false” state:
false |
no |
off |
0 |
Unique prefixes of these strings are also accepted, for examplet orn. Leading or trailing whitespace is ignored, and case does not matter.
The datatype output function for typeboolean always emits eithert orf, as shown inExample 8.2.
Example 8.2. Using theboolean Type
CREATE TABLE test1 (a boolean, b text);INSERT INTO test1 VALUES (TRUE, 'sic est');INSERT INTO test1 VALUES (FALSE, 'non est');SELECT * FROM test1; a | b---+--------- t | sic est f | non estSELECT * FROM test1 WHERE a; a | b---+--------- t | sic est
The key wordsTRUE andFALSE are the preferred (SQL-compliant) method for writing Boolean constants in SQL queries. But you can also use the string representations by following the generic string-literal constant syntax described inSection 4.1.2.7, for example'yes'::boolean.
Note that the parser automatically understands thatTRUE andFALSE are of typeboolean, but this is not so forNULL because that can have any type. So in some contexts you might have to castNULL toboolean explicitly, for exampleNULL::boolean. Conversely, the cast can be omitted from a string-literal Boolean value in contexts where the parser can deduce that the literal must be of typeboolean.