Concatenate date values in Postgres

Question

I have a table that contains duplicate values in the columns c1, c2, c3, c4, c5.
I want to list all the dates in the row that contain the values generated the group by function.

datum          c1    c2    c3    c4    c5    number
2001-04-22     1     2     3     4     5     2
1999-08-24     2     3     4     5     6     2
2005-11-08     2     4     5     6     7     2
1998-03-20     1     2     3     4     5     2
2009-07-02     2     3     4     5     6     2
1996-05-21     2     4     5     6     7     2

Result should look like this:

datum                      c1    c2    c3    c4    c5    number
1998-03-20 , 2001-04-22    1     2     3     4     5     2
1999-08-24 , 2009-07-02    2     3     4     5     6     2
1996-05-21 , 2005-11 08    2     4     5     6     7     2

in the source table can be duplicate values in all columns, what will be the solution?

mu is too short · Accepted Answer · 2013-10-07 18:50:45Z

5

select string_agg(to_char(datum, 'yyyy-mm-dd'), ','),
       c1, 
       c2,
       c3, 
       c4, 
       c5, 
       number
from some_table
group by c1, c2,c3, c4, c5, number

edited Oct 7, 2013 at 18:50

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered Oct 7, 2013 at 18:34

user330315

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2024-08-26 15:34:39Z

1

If you are satisfied with an array of date in the result you can use the simple and fast array_agg().

To get the array in a specific order add ORDER BY in the aggregate function. The manual:

This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call [...]

SELECT array_agg(datum ORDER BY datum) AS datum_arr
     , c1, c2, c3, c4, c5
     , min(number) AS number  -- you did not specify how to aggregate number
FROM   tbl
GROUP  BY c1, c2,c3, c4, c5;

For simple queries, it's typically faster to order in a subquery. See:

Create two arrays for two fields, keeping sort order of arrays in sync (without subquery)

edited Aug 26, 2024 at 15:34

answered Oct 8, 2013 at 1:36

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Concatenate date values in Postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related