PostgreSQL ARRAY Constructor

The ARRAY[] constructor in PostgreSQL creates an array value from a list of elements. Arrays in PostgreSQL allow storing multiple values in a single column, making them useful for tags, lists, and multi-valued attributes.

Syntax

-- Array constructor syntax
ARRAY[value1, value2, ...]

-- Array literal syntax (alternative)
'{value1, value2, ...}'

-- Array from subquery
ARRAY(SELECT column FROM table)

-- Multidimensional arrays
ARRAY[[1,2], [3,4]]

Official Documentation

Example usage

-- Create simple array
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;
-- Result: {1,2,3,4,5}

-- Create array of text
SELECT ARRAY['apple', 'banana', 'orange'] AS fruits;
-- Result: {apple,banana,orange}

-- Array literal syntax
SELECT '{10, 20, 30}'::INT[] AS numbers;

-- Create table with array column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[],
    prices NUMERIC[]
);

-- Insert data with arrays
INSERT INTO products (name, tags, prices)
VALUES
    ('Laptop', ARRAY['electronics', 'computers', 'sale'], ARRAY[999.99, 1299.99]),
    ('Phone', ARRAY['electronics', 'mobile'], ARRAY[699.99]);

-- Array from subquery
SELECT ARRAY(
    SELECT category
    FROM products
    WHERE active = true
) AS active_categories;

-- Build array with aggregation
SELECT
    department,
    ARRAY_AGG(employee_name) AS employees
FROM employees
GROUP BY department;

-- Empty array
SELECT ARRAY[]::TEXT[] AS empty_array;

-- Multidimensional arrays
SELECT ARRAY[[1,2,3], [4,5,6], [7,8,9]] AS matrix;

-- Array concatenation
SELECT ARRAY[1,2] || ARRAY[3,4] AS combined;
-- Result: {1,2,3,4}

-- Append element to array
SELECT ARRAY[1,2,3] || 4 AS appended;
-- Result: {1,2,3,4}

-- Prepend element to array
SELECT 0 || ARRAY[1,2,3] AS prepended;
-- Result: {0,1,2,3}

-- Array of dates
SELECT ARRAY[CURRENT_DATE, CURRENT_DATE + 1, CURRENT_DATE + 7] AS dates;

-- Array with NULL values
SELECT ARRAY[1, 2, NULL, 4] AS with_null;

-- Conditional array building
SELECT
    CASE
        WHEN premium THEN ARRAY['email', 'sms', 'push']
        ELSE ARRAY['email']
    END AS notification_methods
FROM users;

-- Array from JSON
SELECT ARRAY(
    SELECT jsonb_array_elements_text('["tag1", "tag2", "tag3"]'::JSONB)
) AS tags;

Common issues

1. Type specification: Empty arrays need type casting: ARRAY[]::TEXT[] not just ARRAY[].
2. NULL vs empty: NULL is different from empty array ARRAY[] or '{}'.
3. Inconsistent dimensions: Multidimensional arrays must have consistent dimensions.
4. Mixed types: All elements must be the same type or convertible to array's declared type.

Best practices

1. Use TEXT[] or INTEGER[] type declarations for clarity in table definitions.
2. For aggregating values into arrays, use ARRAY_AGG function.
3. Consider GIN indexes for array containment queries: CREATE INDEX ON table USING GIN (array_column).
4. Use array operators like @>, &&, and ANY() for efficient querying.

Frequently Asked Questions

Q: How do I create an empty array?
A: Use ARRAY[]::TYPE[] with explicit type casting: ARRAY[]::INT[] or ARRAY[]::TEXT[]. Without the cast, PostgreSQL doesn't know the array type.

Q: Can arrays contain NULL values?
A: Yes, array elements can be NULL: ARRAY[1, NULL, 3] is valid. Use array functions like array_remove to filter out NULLs if needed.

Q: What's the difference between ARRAY[] and '{}'?
A: ARRAY[1,2,3] is the constructor syntax. '{1,2,3}' is the literal syntax that must be cast: '{1,2,3}'::INT[]. ARRAY[] is more flexible and easier to read.

Q: How do I create a multidimensional array?
A: Use nested ARRAY constructors: ARRAY[[1,2], [3,4]] creates a 2D array. All sub-arrays must have the same length.

Q: Can I create an array from a SELECT query?
A: Yes, use ARRAY(SELECT ...): ARRAY(SELECT id FROM users WHERE active = true). This is useful for collecting query results into an array.