PostgreSQL - treat array values as records in a query

Question

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?

Craig Ringer · Accepted Answer · 2013-07-08 05:45:07Z

2

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.

edited Jul 8, 2013 at 5:45

answered Jul 8, 2013 at 5:15

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

2 Comments

William Wino Over a year ago

It doesn't recognize the arraycontent function, I can't use the column as a normal column which I can use in WHERE clause.

Craig Ringer Over a year ago

@william arraycontent isn't a function, it's a table-alias. "label the results of the unnest function 'arraycontent' with single column 'arraycol' for reference elsewhere"

Collectives™ on Stack Overflow

PostgreSQL - treat array values as records in a query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related