Sometimes we want to extract a literal value with a
SELECT. This works perfectly fine if the literal value is
'a', but it fails with an error if the value is
NULL. Let’s discuss why and how to solve the problem.
Consider the following query:
SELECT 1 AS a, -1 AS b, 'abc' AS c FROM dummy_table;
It works. So it would be reasonable to think that this query will also work:
SELECT column1, NULL AS column2 FROM some_table;
But this query will fail with the infamous error:
201: A syntax error has occurred.
The reason why the query fails is that every column returned by a query must have a type. But
NULL, when specified as a literal as we did in the example, has no type.
One could think that
NULL should never have a value because it represents (according to one interpretation) an absence of values. But this is not correct in SQL, since columns always have a type and disregarding their type they can contain
NULL. So we need to assign a value to the returned column! There are a couple of ways to do so.
Casting the value type
The following syntaxes are equivalent, and they both will serve the purpose:
SELECT CAST(NULL AS INT) AS x FROM t; SELECT NULL::INT AS x FROM t;
Use a variable
This is preferable if you want to add some logic that could change the variable value before the query:
DEFINE var1 INT; LET var1 = NULL; SELECT var1 FROM t;