0
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 {}?

2
  • Strange. If you just omit NULLs, how do you know which array member is what? Mar 30, 2020 at 19:05
  • 1
    Please remember to lead with Postgres version and table definitions. Mar 31, 2020 at 0:33

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.