Code golf for handling subtraction with two null operands in PostgreSQL

Question

All these 3 requests return NULL with MySQL 8 and SQLite 3.39:

SELECT 1 - NULL;
SELECT NULL - 1;
SELECT NULL - NULL;

But with PostgreSQL 15, the last one fails with: error: operator is not unique: unknown - unknown

What is the shortest code to have PostgreSQL return NULL in last case?

(Note that with SELECT a.x - a.y FROM a;, x and y may both be NULL. PostgreSQL will not complain since it can determine the type from the column definitions.)

A simple solution is just:

SELECT CASE
 WHEN x IS NULL THEN NULL
 WHEN y IS NULL THEN NULL
 ELSE x - y
END;

• Laurent Lyaudet: I tested the results for MySQL and SQLite here since I only had a PostgreSQL at hand: db-fiddle.com

Answer 1

When sending the query as a string, postgres infers argument types from their text representation. If both arguments of "+" are NULL then it can't infer that your arguments are actually nullable integers.

Thus:

select null::int + null;
 Null

All these 3 requests return NULL with MySQL 8 and SQLite 3.39

Type of returned value is part of the result. If you use the database from a language with strong typing, it's important to know the return type. The first two should return nullable int:

SELECT 1 - NULL;
SELECT NULL - 1;

But I don't know what the last one will return:

SELECT NULL - NULL;

In MySQL you can set an option in the client to display the returned type.

• Laurent Lyaudet: Thanks :) You solution is slightly shortest that the equivalent SELECT CAST (NULL AS integer) - NULL that someone suggested before removing his answer.

Answer 2

I think I found a very short way that avoids duplicating the calculus of x and y, if there is one:

SELECT 0 + NULL - NULL;

• Laurent Lyaudet: I accepted my answer because technically it is still one character less than SELECT NULL::int - NULL;, and I must respect the code golf rules :).