Is there a difference in the following comparisons
#1
where x = true
#2
where x is true
#3
where x = '1'
#4
where x
Add a comment
|
2 Answers
This is exactly the same as
WHERE xIt is
TRUE,FALSEorNULLexactly whenxis.This is the same as the first case, except when
xis NULL, in which case it will beFALSE. So it is the same asWHERE coalesce(x, FALSE)This happens to be the same as the first case, since
'1'is interpreted asTRUE. See the documentation:The datatype input function for type
booleanaccepts these string representations for the “true” state:true
yes
on
1
My preferred way is the simplest:
WHERE x
4 Comments
The Impaler
"...except when x is NULL, in which case it will be FALSE" -- Shouldn't that result in UNKNOWN rather than FALSE?
The Impaler
I'm not sure the
WHERE clause in SELECT behaves the same way as in a CREATE INDEX... One filters when is true, while the other when is different from "false"... not sure anymore.
Laurenz Albe
@TheImpaler
x IS TRUE will return FALSE if x is NULL. My numbering corresponds to the numbering in the question.Using is or is not will handle the case when the value is null. If you rely on =, the result of the comparison will also be null
select null is true as "is", null = true as "equal";
is | equal
----+-------
f |
(1 row)
