Announcing our $3.4M seed round 🚀 Read more →

How to Query Arrays in PostgreSQL

You can retrieve the contents of an array by specifying it in the select clause like any other column:

select
  first_name,
  last_name,
  phone_numbers
from contacts;
 first_name | last_name |        phone_numbers        
------------+-----------+-----------------------------
 John       | Doe       | {999-876-5432,999-123-4567}
 Bob        | Parr      | {555-INC-RDBL}

You can also specify which element of an array to retrieve by specifying its position inside the square brackets. By default PostgreSQL uses 1 as the first position, though this can be overridden as shown in the array_fill() example. The example below shows the first round scores of a player:

select
  player_number,
  round_scores[1]
from player_scores;
 player_number | round_scores
---------------+--------------
         10001 |           95
         10002 |           91

You can also check against the value of a specific element in the where clause:

select *
from player_scores
where round_scores[1] >= 95;
 player_number |   round_scores   
---------------+------------------
         10001 | {95,92,96,97,98}

To compare all elements of an array to a value, you can use ANY/SOME and ALL . ANY and its synonym SOME will return a row if at least one element satisfies the condition. ALL requires all elements to satisfy the condition for a row to be returned. See the examples below:

Show records where there's at least one score above 95. This is best read as "where 95 is lower than ANY of the scores":

select *
from player_scores
where 95 < any (round_scores);
 player_number |   round_scores   
---------------+------------------
         10001 | {95,92,96,97,98}
         10002 | {91,92,93,95,99}

Only show records where 92 is lower or equal to ALL the scores:

select *
from player_scores
where 92 <= all (round_scores);
 player_number |   round_scores   
---------------+------------------
         10001 | {95,92,96,97,98}         

Using unnest() expands an array to multiple rows. The non-array columns get repeated for each row.

select
  first_name,
  last_name,
  unnest(phone_numbers)
from contacts;
 first_name | last_name |    unnest    
------------+-----------+--------------
 John       | Doe       | 999-876-5432
 John       | Doe       | 999-123-4567
 Bob        | Parr      | 555-INC-RDBL

Ready for a modern SQL editor?