Search code examples
sqlarrayspostgresqlaggregation

Aggregate single array of distinct elements from array column, excluding NULL

I'm trying to roll up the distinct non-null values of timestamps stored in a PostgreSQL 9.6 database column.

So given a table containing the following:

date_array
------------------------
{2019-10-21 00:00:00.0}
{2019-08-06 00:00:00.0,2019-08-05 00:00:00.0}
{2019-08-05 00:00:00.0}
(null)
{2019-08-01 00:00:00.0,2019-08-06 00:00:00.0,null}

The desired result would be:

{2019-10-21 00:00:00.0, 2019-08-06 00:00:00.0, 2019-08-05 00:00:00.0, 2019-08-01 00:00:00.0}

The arrays can be different sizes so most solutions I've tried end up running into a Code 0:

SQL State: 2202E  
ERROR: cannot accumulate arrays of different dimensionality.

Some other caveats:

The arrays can be null, the arrays can contain a null. They happen to be timestamps of just dates (eg without time or timezone). But in trying to simplify the problem, I've had no luck in changing the sample data to strings (e.g {foo, bar, (null)}, {foo,baz}) - just to focus on the problem and eliminate any issues I miss/don't understand about timestamps w/o timezone.

This following SQL is the closest I've come (it resolves all but the different dimensionality issues):

SELECT 
   ARRAY_REMOVE ( ARRAY ( SELECT DISTINCT UNNEST ( ARRAY_AGG ( CASE WHEN ARRAY_NDIMS(example.date_array) > 0 AND example.date_array IS NOT NULL THEN example.date_array ELSE '{null}' END ) ) ), NULL) as actualDates
FROM example;

I created the following DB fiddle with sample data that illustrates the problem if the above is lacking: https://www.db-fiddle.com/f/8m469XTDmnt4iRkc5Si1eS/0

Additionally, I've perused stackoverflow on the issue (as well as PostgreSQL documentation) and there are similar questions with answers, but I've found none that are articulating the same problem I'm having.

Solution

  • Plain array_agg() does this with arrays:

    Concatenates all the input arrays into an array of one higher dimension. (The inputs must all have the same dimensionality, and cannot be empty or null.)

    Not what you need. See:

    You need something like this: unnest(), process and sort elements an feed the resulting set to an ARRAY constructor:

    SELECT ARRAY(
   SELECT DISTINCT elem::date
   FROM  (SELECT unnest(date_array) FROM example) AS e(elem)
   WHERE  elem IS NOT NULL
   ORDER  BY elem DESC
   );

    db<>fiddle here

    To be clear: we could use array_agg() (taking non-array input, different from your incorrect use) instead of the final ARRAY constructor. But the latter is faster (and simpler, too, IMO).

    They happen to be timestamps of just dates (eg without time or timezone)

    So cast to date and trim the noise.

    Should be the fastest way:

    • A correlated subquery is a bit faster than a LATERAL one (and does the simple job).
    • An ARRAY constructor is a bit faster than the aggregate function array_agg() (and does the simple job).
    • Most importantly, sorting and applying DISTINCT in a subquery is typically faster than inline ORDER BY and DISTINCT in an aggregate function (and does the simple job).

    See:

    Performance comparison:

    db<>fiddle here