You can do it the slow and verbose way:
SELECT name, description, odobj, defval, datatypeid
, CASE WHEN arrayofdatatypevalues = '{{NULL,NULL,NULL,NULL,NULL}}'::text[]
THEN '{}' ELSE arrayofdatatypevalues END AS arrayofdatatypevalues
FROM (
-- your org. query here
) sub;
My educated guess, though: you don't actually want FULL [OUTER] JOIN
to begin with. Looks like a LEFT [OUTER] JOIN
to me. (Basics in the manual here.)
...
FROM public.datatype dt
LEFT JOIN datatypevalue dtv USING (datatypeid)
...
If so, transform it into into a LATERAL
subquery or just a correlated subquery:
SELECT dt.name, dt.description, dt.odobj, dt.defval, dt.datatypeid
, ARRAY(
SELECT ARRAY[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]
FROM public.datatypevalue dtv
WHERE dtv.datatypeid = dt.datatypeid
) AS arrayofdatatypevalues
FROM public.datatype dt
WHERE dt.name <> ALL ('{Integer,String,Boolean}'::text[]);
Returns {}
instead of {{NULL,NULL,NULL,NULL,NULL}}
out of the box if no matching row is found in table datatypevalue
. But still returns {{NULL,NULL,NULL,NULL,NULL}}
for a single rows with all retrieved columns holding an actual NULL
value, which can be a very important distinction to make. (Probably not possible with your setup - another guess in want of actual table definitions.)
The need for GROUP BY
goes away.
While being at it, use a faster ARRAY constructor instead of array_agg()
. See:
db<>fiddle here for Postgres 12.