I create programmatically sql queries to Postgresql like this:
SELECT * FROM table WHERE column IN (1, 2, 3);
SELECT * FROM table WHERE column IN (1);
SELECT * FROM table WHERE column IN ();
The last query generates a syntax error. How should I handle this special case and write an empty array here?
Internally,
col IN (1, 2, 3)
is converted to
col = ANY (ARRAY[1,2,3]::integer[])
That syntax will also work for empty arrays:
col = ANY (ARRAY[]::integer[])
Use a subquery that returns no results:
select null where 0 = 1
The null here should be implicitly cast-able to any type as well. It's only significant that the query returns no results. For you case it would work just as well to use select 0 or some other integer if that seems clearer to you.
While you could insert a literal null value into the list I wouldn't recommend that. There are some platforms that have server settings which do allow for null equality tests. And I suspect the optimizer will handle the subquery form better. While this wouldn't work on Oracle without adding from dual this it also generally portable.
Whenever possible, we try to handle this case (empty array) explicitly in the code and then we avoid the database call.
However, when we need a quick workaround we add a readable string condition that is always false:
'COLNAME array' = 'is empty'
where COLNAME is replaced with the real column-name
Details:
col = ANY (ARRAY[]::integer[]) from this answer requires us to know the column type0 = 1 from this answer works, but makes debugging tricky, because we don't know which column had the empty array
0=1 --COLUMN array is empty
0=1 --COLUMN array is empty AND OTHER_COL > 2
0=1 --COLUMN array is empty\n, but this is brittle and changes the desired format'COLNAME' = 'array is empty', because this may return true, when the column really contains the string array is empty