Postgres concatenation of a column for a given group

Question

I currently have a Postgres DB with a SHOWS table that looks like this:

id      date        venue                       price  is_sold  ages    time    multi_day   pit
13      2016-01-02  924 Gilman Street, Berkeley $8      FALSE    a/a     7:30pm  FALSE       FALSE

I have a query that orders everything in this table by date (which is of timestamptz type):

SELECT shows.* FROM shows ORDER BY date

Right now, running this query with the pg-promise library produces an array of objects that look like this:

[   
    { id: 1,
    date: Thu Oct 20 2016 17:00:00 GMT-0700 (PDT),
    venue: 'Regency Ballroom, S.F.',
    price: null,
    is_sold: false,
    ages: 'a/a',
    time: null,
    multi_day: false,
    pit: false },
  { id: 2,
    date: Thu Oct 20 2016 17:00:00 GMT-0700 (PDT),
    venue: 'Great American Music Hall.',
    price: null,
    is_sold: false,
    ages: 'a/a',
    time: null,
    multi_day: false,
    pit: false } ... ]

I want to return an array that groups this data by date column. Example output:

[ 
    {date: Thu Oct 20 2016 17:00:00 GMT-0700 (PDT),
     shows: [1, 2]}, // or however this should be structured; I just want a way to get multiple show ids from a single date.
    ...
]

which I assume I could then INNER JOIN to get the rest of the show details that I need.

How do I adjust my query to return this sort of grouping?

Try read this topic : stackoverflow.com/questions/43870/… It may help you... — Markus Werner
– Markus Werner, Commented Jan 4, 2016 at 9:45

dashnick · Accepted Answer · 2016-01-04 20:23:45Z

1

Just to answer the question, use

SELECT date, string_agg(id, ', ') AS show_ids
FROM shows
GROUP BY date

answered Jan 4, 2016 at 20:23

dashnick

2,0781 gold badge22 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Postgres concatenation of a column for a given group

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related