Posted 12/21/2024

ARRAY

Declaration

column_name datatype []

Use in Query

select * from table t where t.id = ANY (ARRAY[1,2,3])

Indexing

SELECT
  name,
  phones [ 1 ]
FROM
  contacts;

Construction

SELECT ARRAY[1,2,3,4] AS numbers;
SELECT ARRAY['apple','banana','cherry'] AS fruits;

Functions

• unnest() – expand array into rows:

  SELECT unnest(ARRAY[1,2,3]) AS number;
  

• array_prepend() – adds the element to the beginning of the array:

  SELECT array_prepend(ARRAY[1,2], 3);
  

• array_append() – adds the element to the end of the array:

  SELECT array_append(ARRAY[1,2], 3);
  

• array_agg(expr) – aggregate values into an array.

  SELECT array_agg(id) FROM users;
  

  IN for compare

  SELECT * FROM table t WHERE t.id IN (1, 2, 3);
  

Performance Notes

• Arrays are stored as a single value → updating one element rewrites the whole array.
• Indexing inside arrays is limited → use GIN indexes for faster lookups with operators like @>.

  CREATE INDEX idx_tags_gin ON posts USING gin(tags);
  SELECT * FROM posts WHERE tags @> ARRAY['sql'];
  

• Best for small sets (tags, phone numbers). For large or frequently updated collections, use a separate table instead.
• unnest() can be expensive on large arrays since it expands rows.
• array_agg() is convenient but may use lots of memory for huge result sets.

Array Functions and Operators