TRUE
, FALSE
, and NULL
are the possible boolean values in PostgreSQL.
Surprisingly, there are a bunch of different values you can use for TRUE
and FALSE
- and one alternative for NULL
. Also surprisingly, some values you'd expect might work, don't work.
Let's check out TRUE
first.
TRUE
The following literal values evaluate to TRUE
. Note that case doesn't matter.
true
't'
'tr'
'tru'
'true'
'y'
'ye'
'yes'
'on'
'1'
Other similar options will cause a syntax error, such as 1
, or tru
.
Now let's look at FALSE
.
FALSE
Here are literal values that will evaluate to FALSE
:
false
'f'
'fa'
'fal'
'fals'
'false'
'n'
'no'
'of'
'off'
'0'
Other similar options that throw syntax errors include 0
, fa
, and '0.0'
.
NULL
NULL
is the value PostgreSQL uses for missing value or no value. Note that NULL
is not equal to any value. NULL
isn't even equal to itself!
UNKNOWN
evaluates to NULL
. Again, capitalization doesn't matter.
There are no string literal values that evaluate to NULL
. Similar terms throw syntax errors, including nan
, none
, and n
.
Advice
Stick with TRUE
, FALSE
, and NULL
. As the [docs]((https://www.postgresql.org/docs/12/datatype-boolean.html) state, "The key words TRUE and FALSE are the preferred (SQL-compliant) method for writing Boolean constants in SQL queries."
Use WHERE my_column IS NULL
and not WHERE my_column = NULL
to return the rows with NULL
values. Remember, NULL
is not equal to NULL
in PostgreSQL. 😁
Code
Here's the code you can use to test different values:
/* make the table and values*/
CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'I am true');
INSERT INTO test1 VALUES (FALSE, 'I am false');
INSERT INTO test1 VALUES (NULL, 'I am null');
/* see the data */
SELECT *
FROM test1;
/* test it out */
SELECT *
FROM test1
WHERE a = 'true'
You can use WHERE a =
to compare TRUE
or FALSE
booleans, strings, or numbers.
Comparing a string with IS
won't work. For example, WHERE a IS 'true'
, will cause an error.
You must use =
or LIKE
to compare string values that you want to evaluate to a boolean. For example WHERE a = 'true'
.
However, you need to use WHERE a IS
to test against NULL
options.
Fun! 😉
Wrap
I hope you found this little guide to be interesting and informative. If you did, please share it on your favorite social media so other folks can find it too. 👏
I write about Python, Data Science, and other fun tech topics. Follow me and join my Data Awesome mailing list if you're in to that stuff.
Happy PostgreSQLing! 👍
Top comments (0)