Treat Null as a Known Value
We take a look at using `is distinct from` and it's counterpart `is not distinct from` to treat nulls as known values. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
Transcript:
(Note, this transcript is condensed without code snippets. For the full transcript, visit www.pgcasts.com)
Treat Nulls as Known Values
Consider the following scenario - you have a table of users with a nullable
field that tracks whether a user is signed up for the mailing list.
In our example table some of the records have a `subscribed` value of `true`
meaning they have agreed to subscribe to our mailing list, `false` if they did
not.
If the users never completed a form that asks them if they want to subscribe
they will have their `subscribed` column set to `null`. In this case `null` has
a special meaning for us and we would want to treat it as a known value.
If we wanted to get all the users who have not yet subscribed to our awesome
mailing list, so that we can show them a prompt the next time they sign in, we
would likely write a query like this one.
You may be surprised that the result of the query above will only yield the
following records
What's going on here?
By default the equality operators do not treat null as a known value and
therefore assume it cannot be compared directly to actual values.
If you want to treat null values as known values you can query them explicitly
like so...
Which results in...
This will work, but it is not scalable. When you are doing it once in a small
query it may be OK but as queries get bigger this makes the query messy and
harder to read.
Fortunately Postgres offers a more idiomatic way to check if a value does not
equal to something, including null values: `is distinct from` and `is not distinct
from` query parts.
We transform our example to use this syntax...
And sure enough we get the correct results...
Conversely, we can use the `is not distinct from` syntax to get records where
the result is equal to the passed value...
This query will return all the rows containing subscribed with a value of true...
This same syntax will also work for querying null...
Where this syntax becomes particularly useful is when you pass parameters to
your SQL query from your programming language of choice. For the sake of
example we will use `prepared statements` which are a way of reusing SQL
statements in Postgres with different passed parameters, and are similar to
what your programming language is doing behind the scenes.
We will start by testing what will happen when using a normal equality operator
This query we just prepared will not work if we try to pass it null, it will
also not treat null as a known value.
Result...
Now let's test the same query with the non-equal operator...
It would be really hard to rewrite these types of reusable queries with the
explicit test for null. Instead if we rewrite them with `is distinct from` and
`is not distinct from` it works flawlessly:
First let's discard all of our previous prepared statements...
And then prepare them again with `is distinct from` and `is not distinct from...
These updated prepared statements now support both null and actual boolean values. Let's test them...
Watch video Treat Null as a Known Value online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 30 August 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 464 once and liked it 10 people.