The True Guide to True and False in PostgreSQL

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! 👍