ruk·si
/notes

🐘 PostgreSQL
Arrays

Updated at 2015-09-26 17:36

PostgreSQL has good support for array types.

SELECT array_prepend(1, ARRAY[2,3]);        -- => {1,2,3}
SELECT array_append(ARRAY[1,2], 3);         -- => {1,2,3}
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);   -- => {1,2,3,4}
SELECT array_length(ARRAY[1,2,3,4], 1);     -- => 4 (1 is dimension)

SELECT 1 = any(ARRAY[1,2,3]);               -- => true
SELECT 4 = any(ARRAY[1,2,3]);               -- => false
SELECT 4 != all(ARRAY[1,2,3]);              -- => true
SELECT 4 != all(ARRAY[1,2,3,4]);            -- => false

SELECT ARRAY[1,2]   && ARRAY[2,4];          -- => true,  overlaps
SELECT ARRAY[1,2]   && ARRAY[3,4];          -- => false, doesn't overlap
SELECT ARRAY[1,4,3] @> ARRAY[3,1];          -- true, contains
SELECT ARRAY[2,7]   <@ ARRAY[1,7,4,2,6];    -- true, contained by

SELECT json_agg(customer_id) FROM customer;  -- turn rows to JSON array
SELECT array_agg(customer_id) FROM customer; -- turn rows to array
SELECT unnest(ARRAY[1,2]);                   -- expand array to rows
-- 1
-- 2

You can use array types as column. They are not as performant as joined tables but might be better for some cases.

CREATE TABLE employee (
    name            text,
    pay_by_quarter  integer[],      -- array
    schedule        text[][]        -- multi-dimensional array
);

Input and output syntax is '{{1,2,3},{4,5,6},{7,8,9}}'. Input can also be done using ARRAY-syntax.

INSERT INTO employee VALUES (
    'Bill',
    '{10000, 10000, 11000, 11000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}'
);

INSERT INTO employee VALUES (
    'Bill',
    ARRAY[10000, 10000, 11000, 11000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]
);

Array elements can be accessed with []. Note that indexing starts at 1, not 0.

SELECT pay_by_quarter[3] FROM employee;
-- => 11000

SELECT name
FROM employee
WHERE pay_by_quarter[1] = pay_by_quarter[2];
-- => Bill

Arrays support slicing. Creates a subarray.

SELECT pay_by_quarter[2:3]
FROM employee
WHERE name = 'Bill';
-- => {10000,11000}

Editing arrays is pretty straight forward.

UPDATE employee
SET pay_by_quarter = '{10000,10000,11000,13000}'
WHERE name = 'Bill';
-- => {10000,10000,11000,13000}

UPDATE employee
SET pay_by_quarter[2:3] = '{11000, 12000}'
WHERE name = 'Bill';
-- => {10000,11000,12000,13000}

UPDATE employee
SET pay_by_quarter[4] = 14000
WHERE name = 'Bill';
-- => {10000,11000,12000,14000}

UPDATE employee
SET pay_by_quarter[5] = 17000
WHERE name = 'Bill';
-- => {10000,11000,12000,14000,17000}

UPDATE employee
SET pay_by_quarter[7] = 20000
WHERE name = 'Bill';
-- => {10000,11000,12000,14000,17000,NULL,20000}

generate_subscripts is handly for complex array lookups.

SELECT *
FROM (
  SELECT
    name,
    pay_by_quarter,
    generate_subscripts(pay_by_quarter, 1) AS found_in
  FROM employee
) AS foo
WHERE pay_by_quarter[found_in] = 12000;
-- name    pay_by_quarter                                found_in
-- "Bill", "{10000,11000,12000,12000,17000,NULL,20000}", 3
-- "Bill", "{10000,11000,12000,12000,17000,NULL,20000}", 4

Sources