CODEWITHSUNDEEP
postgresql
William Wino
William Wino

Reputation: 3819

PostgreSQL - treat array values as records in a query

I want to treat an array of timestamps as a set of records that could be related with other tables.

For example:

SELECT array[0], COUNT(b.id) FROM array, B WHERE B.date > array[0]

What's the best way for achieving something like this?

Upvotes: 1

Views: 533

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324325

It sounds like you are looking for the unnest function.

regress=> SELECT arraycol 
          FROM unnest(ARRAY[1,2,3,4,5]) arraycontent(arraycol);
 arraycol 
--------
      1
      2
      3
      4
      5
(5 rows)

You can join on the array's contents; unnest, being a set-returning function, can be used like any other FROM term.

If your PostgreSQL is too old to have unnest then it's too old to run, too. Start planning an upgrade.

Upvotes: 2

Related Questions