[SOLVED] Does PostREST have a null-safe not-equal operator, like IS DISTINCT FROM?

Does PostREST have a null-safe not-equal operator, like IS DISTINCT FROM?

I'm making some simple PostgREST queries on a table with network device properties. It seems that eq and not.eq both exclude null values. I've learned that that's a “feature” of PostgreSQL, and can be worked around with the IS DISTINCT FROM operator, which treats null as a comparable value.

I can't find an equivalent (null-safe not-equals) operator in PostgREST. Is there one?

Simplified example:

# https://example.com/api?select=*&name=like.spam-*
[{
  "name":"spam-eggs",
  "type":"router",
  "sector":"cheese"
},{
  "name":"spam-ham",
  "type":"router",
  "sector":null
}]

not.eq.cheese excludes cheese and null:

# https://example.com/api?select=*&name=like.spam-*&sector=not.eq.cheese
[]

My awkward workaround is using or to include nulls:

# https://example.com/api?select=*&name=like.spam-*&or=(sector.not.eq.cheese,sector.is.null)
[{
  "name":"spam-ham",
  "type":"router",
  "sector":null
}]

Am I stuck with that workaround, or is there an operator like isdistinctfrom, neq-or-null, etc. that I've missed?

Solution

Current answer

Since PostgREST version 11.0.0, the isdictinct operator is translated to IS DISTINCT FROM in PostgreSQL. Using your example, the new query would be:

https://example.com/api?select=*&name=like.spam-*&sector=isdistinct.cheese

Original answer (Oct 2022)

I've checked the code and there's no implementation of IS DISTINCT FROM right now. That is, no PostgREST operator translates to it. The only reference I found is in a comment in this file, but it's for a different issue.

So, yes, right now, your workaround would be the closest you can get to the behavior you want. Creating a FUNCTION with a custom query using IS DISTINCT FROM is another alternative, although it requires more heavy lifting.