SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid
,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as arrayofdatatypevalues
FROM datatypevalue dtv
FULL JOIN public.datatype as dt on dt.datatypeid = dtv.datatypeid
WHERE dt.name !='Integer' and dt.name !='String' and dt.name !='Boolean'
GROUP BY dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid;
Returns output like (example with two result rows):
{{NULL,NULL,NULL,NULL,NULL}}
{{NULL,10,xxs,1,21},{NULL,9,xyz,0,21}}
How do I replace this {{NULL,NULL,NULL,NULL,NULL}}
with {}
?
Best Answer
You can do it the slow and verbose way:
My educated guess, though: you don't actually want
FULL [OUTER] JOIN
to begin with. Looks like aLEFT [OUTER] JOIN
to me. (Basics in the manual here.)If so, transform it into into a
LATERAL
subquery or just a correlated subquery:Returns
{}
instead of{{NULL,NULL,NULL,NULL,NULL}}
out of the box if no matching row is found in tabledatatypevalue
. But still returns{{NULL,NULL,NULL,NULL,NULL}}
for a single rows with all retrieved columns holding an actualNULL
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, use a faster ARRAY constructor instead of
array_agg()
. See:db<>fiddle here for Postgres 12.