CODEWITHSUNDEEP
sqlpostgresqlboolean
b00sted 'snail'
b00sted 'snail'

Reputation: 612

PostgreSQL boolean comparison

Is there a difference in the following comparisons

#1

where x = true

#2

where x is true

#3

where x = '1'

#4

where x

Upvotes: 13

Views: 15471

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246403

  1. This is exactly the same as

    WHERE x

    It is TRUE, FALSE or NULL exactly when x is.

  2. This is the same as the first case, except when x is NULL, in which case it will be FALSE. So it is the same as

    WHERE coalesce(x, FALSE)

  3. This happens to be the same as the first case, since '1' is interpreted as TRUE. See the documentation:

    The datatype input function for type boolean accepts these string representations for the “true” state:

    true
    yes
    on
    1

My preferred way is the simplest:

WHERE x

Upvotes: 13

JGH
JGH

Reputation: 17846

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)

Upvotes: 6

Related Questions