Apple
March 14, 2022, 10:22pm
1
Goal: Select all rows whose column “enabled” (boolean, nullable) is different than true
Pick your first attempt:
SELECT * FROM table WHERE enabled != true
SELECT * FROM table WHERE enabled IS NOT true
SELECT * FROM table WHERE enabled <> true
Other
I checked the spoiler before answering (don’t do it!)
This is on a PostgreSQL database, don’t know if there’s any difference to MySQL/MariaDB
Spoiler (only click this after answering the poll)
SELECT * FROM table WHERE enabled != true
Nope
SELECT * FROM table WHERE enabled IS NOT true
Nope
SELECT * FROM table WHERE enabled <> true
Yes
Postgresql is weired than my/maria coz it is ACID complaint.
Why did you not use = false?
Apple
March 15, 2022, 2:23am
3
This is a very specific case of mine where a boolean may be null by intention, hence I'd need to use = false OR = null if I were to go that route
My first attempt was != true, and didn't return what I wanted, and I didn't want to use =false OR =null without knowing why != true failed... I still don't know why, but now I know I have to use <> instead
JonL
March 15, 2022, 9:40am
4
Are you sure != doesn’t work? In Pg <> and != are the same. <> is a standard SQL operator and Pg actually converts != to <> when parsing.
2 Likes
Apple
March 15, 2022, 10:10am
5
I don’t know what to tell you - I’ve justed tested your theory, and it’s correct
Ghosts
I don’t remember the exact query I experienced this issue - as far as I remember, simply swapping != to <> things started working as I wanted
1 Like
JonL
March 15, 2022, 10:27am
6
We’ve all been there buddy…we’ve all been there.
According to this link below:
using = would only work for PostgreSQL if you treated true like a string value with single or double quotes
AND
Comparing a string with IS
won't work. For example, WHERE a IS 'true'
, will cause an error.
Supposedly working --
SELECT * FROM table WHERE enabled = 'true'
or using LIKE
SELECT * FROM table WHERE enabled LIKE 'true'
(I haven't opened up an example yet to test)
TRUE, FALSE, and NULL are the possible boolean values in PostgreSQL. Surprisingly, there are a bunc...
1 Like
This guy has some good SQL query lessons
“ The very first thing, we must do when writing SQL queries, is to understand the underlying data. Once we understand the data and how this data is stored across different tables, it becomes much simpler to write SQL Queries to retrieve any...
following his suggestion I also went to Kaggle, signed up (free) and I can download realworld datasets to use for testing
Download Open Datasets on 1000s of Projects + Share Projects on One Platform. Explore Popular Topics Like Government, Sports, Medicine, Fintech, Food, More. Flexible Data Ingestion.
Apple
March 15, 2022, 3:02pm
9
NewMedia’s here!
I’ve tested here a bit, looks like != true or != ‘true’ has the same behaviour
Although, I’ve discovered none of them pick NULL values!
Correct way is
SELECT * FROM table WHERE enabled <> true OR enabled IS NULL
or
SELECT * FROM table WHERE enabled != true OR enabled IS NULL
The <>
and !=
do the same but will never include the null values, so you have to add the null check also.
However, you need to use WHERE enabled IS to test against
NULL` options.
From the article I linked to.
With MS SQL Server you can change the behavior of NULL values
SET ANSI_NULLS OFF
SELECT * FROM table WHERE enabled <> true
By setting ANSI_NULLS to OFF it will return also the NULL values with the query above. There is not something like that in Postgres as far I know.
SQL Server ANSI_NULLS Explained (database.guide)
Imo, if you want three things: true/false/null better use int or enum. boolean, as the name suggests is for two things: true/false. I keep my bool columns default false/true and use then fir false or true only. My thought.