My Query is
SELECT
id,
ARRAY_AGG(session_os)::integer[]
FROM
t
GROUP BY id
HAVING ARRAY_AGG(session_os)::integer[] && ARRAY[1,NULL]
It's giving ERROR: array must not contain nulls
Actually I want to get rows like
id | Session_OS
-------|-------------
641 | {1, 2}
642 | {NULL, 2}
643 | {NULL}
Kindly check the sample data here
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=7793fa763a360bf7334787e4249d6107
The && operator does not support NULL values. So, you need another approach. For example you could join the data to the table first. This gives you the ids which are linked to your required data. At the second step you are able to arregate all values using these ids.
SELECT
id,
ARRAY_AGG(session_os) -- 4
FROM t
WHERE id IN ( -- 3
SELECT
id
FROM
t
JOIN (
SELECT unnest(ARRAY[1, null]) as a -- 1
)s ON s.a IS NOT DISTINCT FROM t.session_os -- 2
)
GROUP BY id
NULL value.NULL value, using the operator IS NOT DISTINCT FROM, which considers the NULL.id values which can be used in the WHERE filter
The extension intarray installs its own && operator for int[], and this doesn't allow NULLs and it takes precedence over the built-in && operator.
If you are not using intarray, you can just uninstall it (except for in dbfiddle, where you can't). If you are using it occasionally, I think it is best to install it in its own schema which is not in your search path. Then you need to schema qualify its operators when you do need them.
Alternatively, you can leave intarray in place and schema qualify the normal built-in operators when you need those ones specifically, as shown here.
