NULL are the possible boolean values in PostgreSQL.
Surprisingly, there are a bunch of different values you can use for
FALSE - and one alternative for
NULL. Also surprisingly, some values you'd expect might work, don't work.
Let's check out
The following literal values evaluate to
TRUE. Note that case doesn't matter.
Other similar options will cause a syntax error, such as
Now let's look at
Here are literal values that will evaluate to
Other similar options that throw syntax errors include
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
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."
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. 😁
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
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
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
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! 👍