Search code examples
sqlpostgresqlsql-insql-null

PostgreSQL how to treat null as a value

Let us say:

  1. I have a table with the name "movie" and it contains a field with the name "categoryid" which points to another "category" table.

  2. The field "categoryid" can be null in case no category was selected for a movie. Otherwise it's a number.

  3. I have a user interface for a movies search page, that lists all categories in the form of a list of checkboxes so the user can select multiple categories. And this list of checkboxes also includes an "Uncategorised" option to select movies where "categoryid" is null

Now the user is now selecting null and multiple other checkboxes, this reflects into an SQL code that would look something like this:

SELECT * FROM movie WHERE categoryid in (1, 5, 9, NULL);

Apparently, the above code doesn't work. I guess null is not treated as a value is SQL like it is in other languages! Uncategorized movies were filtered out. The below code works however

SELECT * FROM movie WHERE categoryid IN (1, 5, 9) OR categoryid IS NULL;

But unfortunately, the framework I work with can't make this exception, and I have no flexibility over the structure of the database as it's generated by another software. So, is there any way I can use the NULL value with the IN operator? or any other operator that will accept a list of allowed values?

Solution

  • My recommendation is not to use NULL for this. NULL works best if you interpret it as “unknown”, but in your case the value is known: it is “uncategorized”.

    So it would be best if you create a special category for that and handle it like all others. Don't allow NULLs here, and everything becomes simple.

    Alternatively, you can replace NULL with a value that doesn't otherwise exist in the query using something like

    coalesce(category, '')

    That would create the “uncategorized” category on the fly.