PostgreSQL: Slice Array

Extract a subset of elements from an array using slicing syntax.

Problem

Array: {Python, SQL, Java, C++, Go} - need first 3 elements: {Python, SQL, Java}.

Solution: Array Slicing Syntax

SELECT
    emp_id,
    first_name,
    skills,
    skills[1:3] as first_three,
    skills[2:4] as middle_three,
    skills[3:] as from_third_onward
FROM employees
WHERE skills IS NOT NULL
LIMIT 5;

Result:

emp_id | first_name | skills              | first_three     | middle_three   | from_third_onward
-------+------------+---------------------+-----------------+----------------+-----------------
     1 | James      | {Python,SQL,Java}   | {Python,SQL,Java}| {SQL,Java}     | {Java}
     2 | Mary       | {Java,C++,Go}       | {Java,C++,Go}   | {C++,Go}       | {Go}

Slice Syntax Explained

array[start:end] extracts elements from position start to end (inclusive).

-- First 2 elements
SELECT skills[1:2] FROM employees;  -- {elem1, elem2}

-- Last 2 elements
SELECT skills[-2:] FROM employees;  -- {last-1, last}

-- All except first
SELECT skills[2:] FROM employees;   -- {elem2, elem3, ...}

-- All except last
SELECT skills[1:-1] FROM employees; -- {elem1, ..., last-1}

Real-World Examples

Get first element only:

SELECT skills[1] FROM employees WHERE skills IS NOT NULL;

Get last element:

SELECT skills[ARRAY_LENGTH(skills, 1)] as last_skill FROM employees;

Get middle section:

SELECT
    skills[2:ARRAY_LENGTH(skills, 1)-1] as middle_elements
FROM employees
WHERE ARRAY_LENGTH(skills, 1) > 2;

Test multiple slices:

SELECT
    skills,
    skills[1:1] as first,
    skills[-1:] as last,
    skills[2:-1] as middle
FROM employees
WHERE array_length(skills, 1) > 2;

Performance

  • Fast: O(n) where n = slice size
  • No copy overhead: Returns reference to array slice
  • Memory efficient: Doesn’t duplicate data

Common Variations

-- Get every other element (manual)
SELECT array_agg(elem) FROM UNNEST(skills[1:]) WITH ORDINALITY AS t(elem, i)
WHERE i % 2 = 1;

-- Get first N elements
SELECT skills[1:n];

-- Slice with LIMIT
SELECT (UNNEST(skills))[1:5] FROM employees;

FAQ

Q: Can I use negative indices? A: Yes, -1 is last element, -2 is second-to-last, etc.

Q: What if slice goes beyond array bounds? A: PostgreSQL returns what’s available (no error).

Q: Can I modify array slice? A: No, but you can rebuild: array_cat(skills[1:1], new_elements, skills[3:])