5

I have a column in our database called min_crew that has varying character arrays such as '{CA, FO, FA}'.

I have a query where I'm trying to get aggregates of these arrays without success:

SELECT use.user_sched_id, array_agg(se.sched_entry_id) AS seids
     , array_agg(se.min_crew) 
FROM base.sched_entry se
   LEFT JOIN base.user_sched_entry use ON se.sched_entry_id = use.sched_entry_id
WHERE se.sched_entry_id = ANY(ARRAY[623, 625])
GROUP BY user_sched_id;

Both 623 and 625 have the same use.user_sched_id, so the result should be the grouping of the seids and the min_crew, but I just keep getting this error:

ERROR:  could not find array type for data type character varying[]

If I remove the array_agg(se.min_crew) portion of the code, I do get a table returned with the user_sched_id = 2131 and seids = '{623, 625}'.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Darin Peterson
  • 1,262
  • 2
  • 15
  • 25
  • possible duplicate of [array\_agg for Array Types](http://stackoverflow.com/questions/6782268/array-agg-for-array-types) or [Can't aggregate arrays](http://stackoverflow.com/q/15947943/479863) – mu is too short Nov 22 '13 at 23:30

1 Answers1

9

The standard aggregate function array_agg() only works for base types, not array types as input. (But Postgres 9.5+ has a new variant of array_agg() that can!)

You could use the custom aggregate function array_agg_mult() as defined in this related answer:
Selecting data into a Postgres array

Create it once per database. Then your query could work like this:

SELECT use.user_sched_id, array_agg(se.sched_entry_id) AS seids
      ,array_agg_mult(ARRAY[se.min_crew]) AS min_crew_arr
FROM   base.sched_entry se
LEFT   JOIN base.user_sched_entry use USING (sched_entry_id)
WHERE  se.sched_entry_id = ANY(ARRAY[623, 625])
GROUP  BY user_sched_id;

There is a detailed rationale in the linked answer.

Extents have to match

In response to your comment, consider this quote from the manual on array types:

Multidimensional arrays must have matching extents for each dimension. A mismatch causes an error.

There is no way around that, the array type does not allow such a mismatch in Postgres. You could pad your arrays with NULL values so that all dimensions have matching extents.

But I would rather translate the arrays to a comma-separated lists with array_to_string() for the purpose of this query and use string_agg() to aggregate the text - preferably with a different separator. Using a newline in my example:

SELECT use.user_sched_id, array_agg(se.sched_entry_id) AS seids
      ,string_agg(array_to_string(se.min_crew, ','), E'\n') AS min_crews
FROM   ...

Normalize

You might want to consider normalizing your schema to begin with. Typically, you would implement such an n:m relationship with a separate table like outlined in this example:
How to implement a many-to-many relationship in PostgreSQL?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • One issue that I'm having is that when the min_crew is not the same for two entries (e.g. {CA, FO, FA} and {CA, FO}, i get the following error: "Detail: Arrays with differing element dimensions are not compatible for concatenation." It works great when the arrays are the same. Is there a simple way to fix this? – Darin Peterson Nov 25 '13 at 17:47
  • 1
    @DarinPeterson: I added another answer to address that. – Erwin Brandstetter Nov 25 '13 at 18:38
  • 1
    I modified the answer to use array_agg() in place of string_agg(), b/c the intent is to maintain separation of the min_crew across seids, so "array_agg(array_to_string(se.min_crew, ',')) AS min_crews" does exactly what is needed. – Darin Peterson Nov 25 '13 at 20:37
  • @ErwinBrandstetter is it possible to add the padding NULL value in custom aggrigate function if its a mismatch ? – Sarath Sep 24 '14 at 12:21
  • @Sarath: Yes, but it gets significantly more expensive. Use a custom function for `SFUNC` in the definition of the aggregate, where you check dimensions with [`array_dims()` or `array_ndims()`](http://www.postgresql.org/docs/current/interactive/functions-array.html) ... That's really a *new question*, not a comment. You can always reference this one for context. – Erwin Brandstetter Sep 24 '14 at 12:29
  • added as aquestion http://stackoverflow.com/questions/26017890/postgresql-how-to-add-padding-null-in-custom-aggregate-function can you give the syntax – Sarath Sep 24 '14 at 13:14