Add IS DISTINCT FROM and IS NOT DISTINCT FROM operators for Postgres

[icopp]

These are functionally identical to <> and = respectively, except that they treat NULL as a known value (e.g. NULL IS DISTINCT FROM 'value' returns TRUE, NULL IS NOT DISTINCT FROM 'value' returns FALSE).

[koskimas]

How's that different from NULL IS NOT 'value' and NULL IS 'value' that are already supported?

You can already use any operator like this:

where('foo', sql`is distinct from`, bar) 

[icopp]

How's that different from NULL IS NOT 'value' and NULL IS 'value' that are already supported?

Like this:

SELECT NULL IS DISTINCT FROM NULL; -- false
SELECT NULL IS NOT DISTINCT FROM NULL; -- true

SELECT 'value' IS DISTINCT FROM NULL; -- true
SELECT 'value' IS NOT DISTINCT FROM NULL; -- false

SELECT 'value' IS DISTINCT FROM 'value'; -- true
SELECT 'value' IS NOT DISTINCT FROM 'value'; -- false

The key difference is that it handles NULL and non-NULL values in a single statement, without needing to do things like a = b OR (a IS NULL AND B IS NULL) OR (a IS NOT NULL AND B IS NOT NULL).

You can already use any operator like this:

where('foo', sql`is distinct from`, bar) 

This was what I initially tried, but the TypeScript inference here breaks badly in some situations, like if you have a .where() on a .doUpdateSet().

[koskimas]

Breaks how?

[koskimas]

@icopp What do you mean by

This was what I initially tried, but the TypeScript inference here breaks badly in some situations, like if you have a .where() on a .doUpdateSet().

Could you provide the code you're running and the error you get.