[]

PostgreSQL Array Functions

Complete reference for PostgreSQL array functions covering array construction, searching, appending, slicing, aggregation, and unnesting into rows. Every function includes syntax, multi-dimensional array examples, and common anti-patterns. Updated for PostgreSQL 16.

17 functions

Jump to function

array_agg (in array context)array_append array_cat array_dims array_fill array_length array_ndims array_position array_positions array_prepend array_remove array_replace array_sample array_to_string cardinality string_to_array unnest

What are PostgreSQL Array Functions?

PostgreSQL array functions are built-in functions for working with PostgreSQL's native array data type, which allows a single column to store multiple values. They support constructing arrays with ARRAY[], appending values with array_append(), searching with array_position(), and expanding arrays into rows with unnest(). Common PostgreSQL array functions include array_agg(), unnest(), array_length(), array_cat(), array_to_string(), and array_contains operators @> and <@.

array_agg (in array context)

PG 8.4+→ anyarray

See aggregate category. In array context: re-aggregates unnested elements back into arrays.

DeveloperDBAData Eng

Signature

array_agg ( expression ) → anyarray

Parameters

Parameter	Type	Description
expression	any	Value to aggregate into array

Examples

Deduplicate and sort array

sql

SELECT array_agg(DISTINCT x ORDER BY x) FROM unnest(ARRAY[3,1,2,1]) AS x;

→{1,2,3}

sql

SELECT array_agg(x) FILTER (WHERE x > 2) FROM unnest(ARRAY[1,2,3,4]) AS x;

→{3,4}

sql

SELECT post_id, array_agg(tag ORDER BY tag) AS sorted_tags FROM post_tags GROUP BY post_id;

→Alphabetically sorted tag array per post

sql

SELECT user_id, array_agg(order_id ORDER BY created_at DESC) AS recent_orders FROM orders GROUP BY user_id;

→Per-user array of order IDs sorted newest-first

⚠Anti-Pattern— Using array_agg without ORDER BY when element order matters

`array_agg` without an explicit `ORDER BY` clause returns elements in an indeterminate order — PostgreSQL makes no guarantee about the sequence. Any code that relies on positional array access (e.g., `arr[1]`) after an unordered `array_agg` is fragile and can produce different results across runs or plan changes.

✓ Instead: SELECT array_agg(item ORDER BY sort_key) FROM t GROUP BY group_col;

To remove duplicates from an array: `(SELECT array_agg(DISTINCT x) FROM unnest(arr) AS x)` or use `array(SELECT DISTINCT unnest(arr) ORDER BY 1)`.

example

SELECT array(SELECT DISTINCT unnest(tags) ORDER BY 1) AS unique_sorted_tags FROM posts;

→Sorted, deduplicated tag array

Common Gotchas

⚠

string_agg without ORDER BY produces non-deterministic results

string_agg(col, ',') returns results in arbitrary order unless you specify ORDER BY inside the aggregate.

⚠

COUNT(col) skips NULLs but COUNT(*) does not

COUNT(column_name) counts non-NULL values only. COUNT(*) counts all rows. These produce different results when the column has NULLs.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

array_append

PG 7.4+→ anyarray

Appends an element to the end of an array.

DeveloperData Eng

Signature

array_append ( anyarray, anyelement ) → anyarray

Parameters

Parameter	Type	Description
array	anyarray	Array to append to
element	anyelement	Element to append (NULL is allowed)

Examples

sql

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

→{1,2,3,4}

sql

SELECT array_append(ARRAY['a','b'], 'c');

→{a,b,c}

sql

SELECT array_append(tags, 'new-tag') FROM posts WHERE id = 1;

→{existing,tags,new-tag}

sql

UPDATE sessions SET event_log = array_append(event_log, $1) WHERE session_id = $2;

→Appends an event string to the session's log array in-place

sql

SELECT id, array_append(permissions, 'audit') AS updated_perms FROM roles WHERE 'admin' = ANY(permissions);

→Adds 'audit' permission to every role that already has 'admin'

⚠Anti-Pattern— Appending in application-level loops

Avoid reading an array into application code, appending elements one by one in a loop, and writing the whole array back. Each round-trip is costly. Use `array_append` or `||` in a single UPDATE instead.

✓ Instead: UPDATE t SET arr = array_append(arr, $1) WHERE id = $2;

`array_append(arr, elem)` is equivalent to `arr || elem`. The `||` operator also handles array-to-array concatenation with `array_cat`.

example

UPDATE items SET tags = tags || 'featured' WHERE id = $1;

→Appends 'featured' to the tags array

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

array_cat

PG 7.4+→ anyarray

Concatenates two arrays of the same element type.

DeveloperData Eng

Signature

array_cat ( anyarray, anyarray ) → anyarray

Parameters

Parameter	Type	Description
array1	anyarray	First array
array2	anyarray	Second array to append

Examples

sql

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);

→{1,2,3,4}

sql

SELECT ARRAY[1,2] || ARRAY[3,4];

→{1,2,3,4}

sql

SELECT array_cat(base_perms, extra_perms) AS all_perms FROM role_grants WHERE user_id = $1;

→Merged permission arrays from two sources

sql

SELECT id, array_cat(required_tags, optional_tags) AS full_tags FROM product_config;

→Full tag set combining mandatory and optional tags per product

⚠Anti-Pattern— Concatenating many arrays in a loop instead of using array_agg

Calling `array_cat` repeatedly in a PL/pgSQL loop to build a growing array is O(n²) because each call copies the entire existing array. Aggregate with `array_agg` in a subquery instead.

✓ Instead: SELECT array_agg(elem ORDER BY ...) FROM source_table WHERE ...;

`ARRAY[1,2] || ARRAY[3,4]` is equivalent to `array_cat`. Use `||` in expressions; `array_cat` is useful when passing arrays from variables in PL/pgSQL.

example

SELECT array_cat(existing_tags, new_tags) AS all_tags FROM tag_sets;

→Merged tag arrays

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

array_dims

PG 7.4+→ text

Returns a text representation of the dimensions of an array.

DeveloperDBA

Signature

array_dims ( anyarray ) → text

Parameters

Parameter	Type	Description
array	anyarray	Array to inspect

Examples

sql

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

→[1:3]

sql

SELECT array_dims(ARRAY[[1,2],[3,4]]);

→[1:2][1:2]

sql

SELECT id, array_dims(matrix) AS dims FROM spatial_data WHERE array_ndims(matrix) > 1;

→Dimension string for every multi-dimensional matrix column

Slice preserves original lower bound in the dimension string

sql

SELECT array_dims(arr[2:4]) FROM vectors;

→[2:4]

⚠Anti-Pattern— Parsing array_dims text output in application code

`array_dims` returns a text string like `[1:3][1:2]` intended for human inspection. Do not parse it with string functions in SQL or application code. Use `array_length(arr, dim)` and `array_lower`/`array_upper` for reliable programmatic access.

✓ Instead: SELECT array_lower(arr, 1), array_upper(arr, 1) FROM t;

`array_dims` returns a human-readable text like '[1:3]'. For programmatic use, call `array_ndims(arr)` to get the number of dimensions and `array_length(arr, dim)` for each dimension's size.

example

SELECT array_ndims(arr) AS dims, array_length(arr, 1) AS size FROM matrix_data;

→Dimension count and first-dimension size

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

array_fill

PG 8.4+→ anyarray

Creates an array filled with a specified value, of the given dimensions.

DeveloperData Eng

Signature

array_fill ( anyelement, integer[] [, integer[]] ) → anyarray

Parameters

Parameter	Type	Description
value	anyelement	Value to fill with (NULL is allowed)
dimensions	integer[]	Array of dimension sizes, e.g., ARRAY[3] for a 3-element 1D array
lower_bounds	integer[]	Optional lower bound for each dimension (default 1)

Examples

sql

SELECT array_fill(0, ARRAY[3]);

→{0,0,0}

sql

SELECT array_fill(NULL::text, ARRAY[5]);

→{NULL,NULL,NULL,NULL,NULL}

sql

SELECT array_fill(1, ARRAY[3,3]);

→{{1,1,1},{1,1,1},{1,1,1}}

sql

INSERT INTO user_stats (user_id, monthly_totals) SELECT id, array_fill(0, ARRAY[12]) FROM users;

→Populates a 12-slot zeroed integer array for each user to track monthly totals

sql

SELECT array_fill(false, ARRAY[7]) AS week_flags;

→{f,f,f,f,f,f,f} — boolean array representing 7 days, all initialised to false

⚠Anti-Pattern— Using array_fill as a workaround for missing rows instead of generate_series

When the goal is to produce a sequence of integers or dates (e.g., to fill time-series gaps), `generate_series` is the right tool. `array_fill` creates a flat array of identical values and is not a substitute for a sequence generator.

✓ Instead: SELECT generate_series('2024-01-01'::date, '2024-12-31', '1 day') AS day;

Use `array_fill(0, ARRAY[24])` to create a 24-slot zero-initialized array for hourly bucketing, then update slots with a loop or unnest+update approach.

example

SELECT array_fill(0.0, ARRAY[12]) AS monthly_scores FROM users;

→12-element zero array for each user

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

array_length

PG 7.4+→ integer

Returns the length of the specified dimension of an array. Dimension 1 is the outermost.

DeveloperData EngDBA

Signature

array_length ( anyarray, integer ) → integer

Parameters

Parameter	Type	Description
array	anyarray	Array to measure
dimension	integer	Array dimension to measure (1 = outermost)

Examples

sql

SELECT array_length(ARRAY[1,2,3,4], 1);

→4

sql

SELECT array_length(tags, 1) FROM posts WHERE id = 1;

→Number of tags

sql

SELECT * FROM posts WHERE array_length(tags, 1) > 3;

→Posts with more than 3 tags

Second dimension size of a 2D array

sql

SELECT array_length(ARRAY[[1,2,3],[4,5,6]], 2);

→3

sql

SELECT id, array_length(skills, 1) AS skill_count FROM candidates ORDER BY skill_count DESC LIMIT 10;

→Top 10 candidates by number of skills listed

⚠Anti-Pattern— Comparing array_length result without NULL guard

`array_length` returns NULL for an empty array, so `WHERE array_length(tags, 1) = 0` never matches. Use `cardinality(tags) = 0` or `tags = '{}'` to detect empty arrays safely.

✓ Instead: SELECT * FROM posts WHERE cardinality(tags) = 0;

`cardinality(arr)` returns the total number of elements across all dimensions. For 1D arrays, `cardinality(arr)` and `array_length(arr, 1)` are equivalent, but `cardinality` returns 0 for empty arrays instead of NULL.

example

SELECT cardinality(tags) AS tag_count, array_length(tags, 1) AS len FROM posts;

→tag_count=0 for empty array, len=NULL for empty array

Common Gotchas

⚠

COUNT(col) skips NULLs but COUNT(*) does not

COUNT(column_name) counts non-NULL values only. COUNT(*) counts all rows. These produce different results when the column has NULLs.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

array_ndims

PG 8.0+→ integer

Returns the number of dimensions of the array.

DeveloperDBA

Signature

array_ndims ( anyarray ) → integer

Parameters

Parameter	Type	Description
array	anyarray	Array to inspect

Examples

sql

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

→1

sql

SELECT array_ndims(ARRAY[[1,2],[3,4]]);

→2

sql

SELECT id FROM ingested_data WHERE array_ndims(payload_array) <> 1;

→Rows where a supposedly flat array was inadvertently nested — useful for data-quality checks

sql

DO $ DECLARE arr int[]; BEGIN arr := ARRAY[[1,2],[3,4]]; IF array_ndims(arr) > 1 THEN RAISE NOTICE 'Multi-dim: %', array_ndims(arr); END IF; END $;

→NOTICE: Multi-dim: 2

⚠Anti-Pattern— Assuming array columns are always 1-dimensional

PostgreSQL allows multi-dimensional arrays in any array-typed column. If application code or `unnest` calls assume 1D without checking, unexpected nesting from client libraries or bulk inserts can cause silent data errors. Validate with `array_ndims` in CHECK constraints or ingestion pipelines.

✓ Instead: ALTER TABLE t ADD CONSTRAINT chk_1d CHECK (array_ndims(arr) = 1);

When accepting arrays from external sources, check `array_ndims(input) = 1` to ensure it's a flat array before processing with `unnest` or array operators that expect 1D input.

example

SELECT CASE WHEN array_ndims(data) = 1 THEN unnest(data)::text ELSE 'multi-dim array' END FROM inputs;

→Element values for 1D arrays, warning for multi-dim

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

array_position

PG 9.5+→ integer

Returns the position of the first occurrence of a value in an array (1-based), or NULL if not found. Optional start position.

DeveloperData Eng

Signature

array_position ( anyarray, anyelement [, integer] ) → integer

Parameters

Parameter	Type	Description
array	anyarray	Array to search
value	anyelement	Value to find
start	integer	Position to start searching from (default 1)

Examples

sql

SELECT array_position(ARRAY['a','b','c'], 'b');

→2

Second occurrence, starting from position 3

sql

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

→4

sql

SELECT * FROM users WHERE array_position(roles, 'admin') IS NOT NULL;

→Users with admin role

sql

SELECT id, array_position(priority_queue, task_id) AS queue_pos FROM sprints WHERE task_id = $1;

→Position of a given task in each sprint's priority queue

sql

SELECT array_position(ARRAY['low','medium','high','critical'], severity) AS severity_rank FROM incidents ORDER BY severity_rank DESC;

→Incidents sorted by severity using position in a reference array as ordinal rank

⚠Anti-Pattern— Using array_position for existence checks on large arrays

`array_position` performs a linear scan. For membership checks on indexed array columns, prefer `value = ANY(arr)` or the `@>` containment operator with a GIN index, both of which can use an index scan.

✓ Instead: SELECT * FROM users WHERE 'admin' = ANY(roles); -- or roles @> ARRAY['admin']

Use `value = ANY(arr)` for existence check (faster). Use `array_position` when you need the index. The `@>` containment operator is faster for GIN-indexed arrays.

example

SELECT array_position(ordered_steps, current_step) AS step_number FROM workflows;

→Current step number in the workflow

Common Gotchas

⚠

LIKE is case-sensitive; ILIKE is not — and LIKE is faster

LIKE 'hello%' will not match 'Hello'. Use ILIKE for case-insensitive pattern matching, but expect a performance cost.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

⚠

Implicit type casts in indexes — your index may not be used

WHERE col = 5 may not use an index on col (text type) because the integer 5 is cast to text, preventing index use.

array_positions

PG 9.5+→ integer[]

Returns an array of all positions where the value appears in the array.

DeveloperData Eng

Signature

array_positions ( anyarray, anyelement ) → integer[]

Parameters

Parameter	Type	Description
array	anyarray	Array to search
value	anyelement	Value to find all occurrences of

Examples

sql

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

→{2,4}

sql

SELECT array_positions(tags, 'featured') FROM posts;

→Positions of 'featured' in each tags array

sql

SELECT id, cardinality(array_positions(votes, 'yes')) AS yes_count FROM polls;

→Count of 'yes' votes per poll without unnesting

sql

SELECT id, array_positions(pipeline_stages, 'review') AS review_indices FROM workflows WHERE 'review' = ANY(pipeline_stages);

→All stage indices where review occurs in each workflow

⚠Anti-Pattern— Unnesting to count duplicates instead of using array_positions

Avoid `SELECT count(*) FROM unnest(arr) AS x WHERE x = 'val'` just to count occurrences. `cardinality(array_positions(arr, 'val'))` achieves the same with no row expansion.

✓ Instead: SELECT cardinality(array_positions(arr, 'val')) FROM t;

Use `cardinality(array_positions(arr, val))` to count how many times a value appears in an array — cleaner than `array_length(array_positions(...), 1)`.

example

SELECT id, cardinality(array_positions(votes, 'yes')) AS yes_count FROM polls;

→Count of yes votes per poll

Common Gotchas

⚠

LIKE is case-sensitive; ILIKE is not — and LIKE is faster

LIKE 'hello%' will not match 'Hello'. Use ILIKE for case-insensitive pattern matching, but expect a performance cost.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

⚠

Implicit type casts in indexes — your index may not be used

WHERE col = 5 may not use an index on col (text type) because the integer 5 is cast to text, preventing index use.

array_prepend

PG 7.4+→ anyarray

Prepends an element to the beginning of an array.

DeveloperData Eng

Signature

array_prepend ( anyelement, anyarray ) → anyarray

Parameters

Parameter	Type	Description
element	anyelement	Element to prepend
array	anyarray	Array to prepend to

Examples

sql

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

→{1,2,3,4}

sql

SELECT array_prepend('first', ARRAY['second','third']);

→{first,second,third}

sql

UPDATE breadcrumbs SET path = array_prepend('home', path) WHERE root_missing(path);

→Inserts 'home' at the front of every incomplete breadcrumb path

sql

SELECT array_prepend(NOW()::text, history) AS updated_history FROM change_log WHERE id = $1;

→Adds current timestamp as newest entry at position 1 of a text history array

⚠Anti-Pattern— Using array_prepend to build ordered history without a size cap

Repeatedly prepending to an array to maintain a changelog creates an ever-growing array column. PostgreSQL stores the entire array inline up to a TOAST threshold, then separately — but there is no automatic trimming. Add a slice to cap length.

✓ Instead: UPDATE t SET history = (array_prepend($1, history))[1:100] WHERE id = $2;

`array_prepend` takes the element as the first argument and the array as the second — the opposite of `array_append`. Alternatively use `elem || arr` with the `||` operator.

example

SELECT 0 || ARRAY[1,2,3];

→{0,1,2,3}

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

array_remove

PG 9.3+→ anyarray

Removes all occurrences of the given value from the array.

DeveloperData EngDBA

Signature

array_remove ( anyarray, anyelement ) → anyarray

Parameters

Parameter	Type	Description
array	anyarray	Source array
element	anyelement	Value to remove (all occurrences)

Examples

sql

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

→{1,3,1}

sql

SELECT array_remove(tags, 'deprecated') FROM posts;

→Tags without 'deprecated'

sql

SELECT array_remove(ARRAY[1,NULL,2,NULL], NULL);

→{1,2}

sql

UPDATE user_profiles SET interests = array_remove(interests, $1) WHERE user_id = $2;

→Removes a specific interest from a user's stored interest array

sql

SELECT id, array_remove(pipeline_stages, 'deprecated_step') AS active_stages FROM workflows WHERE 'deprecated_step' = ANY(pipeline_stages);

→Returns only active stages, dropping the retired pipeline step from each affected workflow

⚠Anti-Pattern— Removing elements via application-level filter and full rewrite

Fetching the array, filtering in application code, and writing back a full UPDATE wastes a round-trip and risks a race condition in concurrent environments. Use `array_remove` in a single UPDATE statement.

✓ Instead: UPDATE posts SET tags = array_remove(tags, 'deprecated') WHERE 'deprecated' = ANY(tags);

`array_remove(arr, NULL)` removes all NULL elements from an array — useful when NULL was introduced by a LEFT JOIN aggregate or string-to-array parsing.

example

SELECT array_remove(array_agg(tag), NULL) AS tags FROM post_tags GROUP BY post_id;

→Tag array with NULLs removed

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

⚠

Foreign keys don't auto-create an index on the referencing column

PostgreSQL creates an index on the primary key side of a FK, but NOT on the foreign key column. Missing FK indexes cause slow lookups and lock escalation on DELETE.

array_replace

PG 9.3+→ anyarray

Replaces all occurrences of the second argument with the third argument in the array.

DeveloperData Eng

Signature

array_replace ( anyarray, anyelement, anyelement ) → anyarray

Parameters

Parameter	Type	Description
array	anyarray	Source array
search	anyelement	Value to find
replacement	anyelement	Value to substitute

Examples

sql

SELECT array_replace(ARRAY[1,2,3,2], 2, 99);

→{1,99,3,99}

sql

SELECT array_replace(statuses, 'pending', 'processing') FROM jobs;

→Statuses with 'pending' → 'processing'

sql

UPDATE user_roles SET permissions = array_replace(permissions, 'write', 'read-write') WHERE department = 'ops';

→Renames the 'write' permission to 'read-write' for all ops department users

sql

SELECT array_replace(scores, NULL, 0) AS filled_scores FROM assessments;

→NULL slots in the scores array replaced with 0

⚠Anti-Pattern— Unnest + re-aggregate to rename an enum value in an array

A common mistake is unnesting, applying REPLACE/CASE on each element, and re-aggregating just to rename a value. `array_replace` does this in one expression without expanding rows.

✓ Instead: UPDATE t SET statuses = array_replace(statuses, 'old_value', 'new_value');

Use `array_replace(arr, NULL, default_val)` to substitute a default for every NULL in an array — a clean alternative to unnesting, COALESCE-ing, and re-aggregating.

example

SELECT array_replace(scores, NULL, 0) AS filled_scores FROM assessments;

→Array with NULLs replaced by 0

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

array_sample

PG16+→ anyarray

Returns n randomly selected elements from an array, without replacement. Added in PostgreSQL 16. The output order is unspecified.

Developer

Signature

array_sample ( array anyarray, n integer ) → anyarray

Parameters

Parameter	Type	Description
array	anyarray	Source array to sample from
n	integer	Number of elements to randomly select; must be <= array length

Examples

Random 3 elements; result varies each call

sql

SELECT array_sample(ARRAY[1,2,3,4,5,6,7,8,9,10], 3);

→{4,9,2}

Random 2 text elements

sql

SELECT array_sample(ARRAY['a','b','c','d','e'], 2);

→{c,a}

N equals array length: all elements in random order (shuffle)

sql

SELECT array_sample(ARRAY[10,20,30,40,50], 5);

→{50,10,30,20,40}

Random sample of 10 product IDs

sql

SELECT array_sample(array_agg(id), 10) FROM products WHERE active;

→{342,17,891,...}

Reproducible sample with setseed()

sql

SELECT setseed(0.42); SELECT array_sample(ARRAY[1,2,3,4,5], 3);

→{3,5,1}

⚠Anti-Pattern— Using ORDER BY random() LIMIT n for large-scale random sampling

ORDER BY random() LIMIT n sorts the entire table by a random key — an O(N log N) operation. For large tables this is very slow.

✓ Instead: Use TABLESAMPLE SYSTEM (percentage) for table-level sampling, or array_sample(array_agg(id), n) to get random IDs in a single aggregate pass.

array_sample(array_agg(id), k) gives k random IDs in a single pass, much faster than ORDER BY random() LIMIT k on large tables (which requires a full sort).

example

SELECT * FROM products WHERE id = ANY(array_sample((SELECT array_agg(id) FROM products WHERE active), 100));

→100 randomly selected active products

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

array_to_string

PG 8.0+→ text

Converts an array to a delimited string. An optional third argument replaces NULLs.

DeveloperData Eng

Signature

array_to_string ( anyarray, text [, text] ) → text

Parameters

Parameter	Type	Description
array	anyarray	Array to join
delimiter	text	String to place between elements
null_string	text	String to substitute for NULL elements (default: NULLs are omitted)

Examples

sql

SELECT array_to_string(ARRAY[1,2,3], ',');

→1,2,3

sql

SELECT array_to_string(ARRAY['a',NULL,'b'], ',', 'n/a');

→a,n/a,b

sql

SELECT array_to_string(tags, ' | ') FROM posts;

→tag1 | tag2 | tag3

sql

SELECT user_id, array_to_string(array_agg(role ORDER BY role), ', ') AS roles_list FROM user_roles GROUP BY user_id;

→Comma-separated role list per user, ready for display

sql

SELECT array_to_string(ARRAY['SELECT', schema_name, 'FROM', table_name], ' ') AS dynamic_sql FROM query_parts;

→Assembles a SQL fragment from array elements using space delimiter

⚠Anti-Pattern— Using array_to_string to serialize data for storage instead of proper columns

Storing `array_to_string(tags, ',')` as a text column to avoid an array type defeats indexing and query capabilities. Keep data as an array type and convert to string only at the presentation layer.

✓ Instead: Keep tags as text[] and call array_to_string only in the final SELECT for display.

Use `array_to_string` when you already have an array and need a string. Use `string_agg` when aggregating from rows. `array_to_string(array_agg(x), ',')` = `string_agg(x, ',')`.

example

SELECT array_to_string(permissions, ', ') AS perm_list FROM user_roles;

→"read, write, admin"

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

⚠

UPDATE ... FROM with multiple matching rows produces non-deterministic results

When an UPDATE ... FROM join matches multiple rows from the FROM table, PostgreSQL uses one arbitrarily — no error.

cardinality

PG 9.4+→ integer

Returns the total number of elements in the array across all dimensions. Returns 0 for empty arrays (unlike array_length which returns NULL).

DeveloperData Eng

Signature

cardinality ( anyarray ) → integer

Parameters

Parameter	Type	Description
array	anyarray	Array to count elements in

Examples

sql

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

→3

sql

SELECT cardinality(ARRAY[]::int[]);

→0

2D array: 4 total elements

sql

SELECT cardinality(ARRAY[[1,2],[3,4]]);

→4

sql

SELECT id, cardinality(tags) AS tag_count FROM articles WHERE cardinality(tags) BETWEEN 3 AND 10;

→Articles with a moderate number of tags, safely handles empty arrays

sql

SELECT AVG(cardinality(skills)) AS avg_skills FROM job_listings;

→Average number of required skills across all job listings

⚠Anti-Pattern— Using cardinality on a NULL column without checking

`cardinality(NULL)` returns NULL, not 0. If the column itself can be NULL (not just an empty array), add a COALESCE or IS NOT NULL guard before comparing to 0.

✓ Instead: SELECT * FROM posts WHERE cardinality(COALESCE(tags, '{}')) = 0;

`cardinality(ARRAY[]::int[])` returns 0, while `array_length(ARRAY[]::int[], 1)` returns NULL. Use `cardinality` in WHERE clauses to avoid NULL comparison pitfalls.

example

SELECT * FROM posts WHERE cardinality(tags) = 0;

→Posts with no tags (safe, no NULL check needed)

Common Gotchas

⚠

COUNT(col) skips NULLs but COUNT(*) does not

COUNT(column_name) counts non-NULL values only. COUNT(*) counts all rows. These produce different results when the column has NULLs.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

string_to_array

PG 7.4+→ text[]

Splits a string into an array using the specified delimiter. An optional third argument specifies which field value to treat as NULL.

DeveloperData Eng

Signature

string_to_array ( string text, delimiter text [, null_string text] ) → text[]

Parameters

Parameter	Type	Description
string	text	String to split
delimiter	text	Delimiter to split on (NULL splits on each character)
null_string	text	Value to convert to NULL in the output array

Examples

sql

SELECT string_to_array('a,b,c', ',');

→{a,b,c}

sql

SELECT string_to_array('a,b,,c', ',', '');

→{a,b,NULL,c}

NULL delimiter splits each character

sql

SELECT string_to_array('abc', NULL);

→{a,b,c}

sql

SELECT id FROM products WHERE string_to_array(category_path, '/') @> ARRAY['electronics','laptops'];

→Products whose slash-delimited category path includes both 'electronics' and 'laptops'

sql

SELECT unnest(string_to_array(pg_read_file('ids.txt'), E'\n')) AS id;

→Splits a newline-separated text file into individual ID rows for bulk lookup

⚠Anti-Pattern— Storing CSV in a text column and splitting at query time instead of using an array column

Splitting a CSV text column on every query is wasteful and prevents index use. Migrate the column to a proper array type so you can add a GIN index and use `@>`, `= ANY()`, and other array operators natively.

✓ Instead: ALTER TABLE t ALTER COLUMN csv_col TYPE text[] USING string_to_array(csv_col, ',');

After splitting, use `@>` to check if a value is in the list: `string_to_array(csv, ',') @> ARRAY['value']`. Create a GIN index on the column for fast containment queries.

example

SELECT * FROM events WHERE string_to_array(allowed_roles, ',') @> ARRAY[$1];

→Events accessible to the given role

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

unnest

PG 8.4+ (single-array); PG 9.4+ (multi-array parallel form)→ setof anyelement

Expands an array into a set of rows, one row per element. Multiple arrays can be passed to expand them in parallel.

DeveloperDBAData Eng

Signatures

unnest ( anyarray ) → setof anyelement

unnest ( anyarray, anyarray [, ...] ) → setof (anyelement, anyelement [, ...])

Parameters

Parameter	Type	Description
array	anyarray	Array to expand into rows

Examples

sql

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

→1, 2, 3 (3 rows)

sql

SELECT id, unnest(tags) AS tag FROM posts;

→One row per tag per post

sql

SELECT * FROM unnest(ARRAY['a','b','c'], ARRAY[1,2,3]) AS t(letter, num);

→Parallel unnest: a|1, b|2, c|3

sql

SELECT val, pos FROM unnest(ARRAY['todo','in_progress','done']) WITH ORDINALITY AS t(val, pos);

→todo|1, in_progress|2, done|3 — position preserved for downstream ordering

sql

SELECT tag, count(*) AS usage FROM posts, unnest(tags) AS tag GROUP BY tag ORDER BY usage DESC LIMIT 20;

→Top 20 most-used tags across all posts, computed by exploding tag arrays into rows

⚠Anti-Pattern— Calling unnest in SELECT list without LATERAL — causes Cartesian products

In older SQL patterns, `SELECT t.id, unnest(t.arr1), unnest(t.arr2) FROM t` with two set-returning functions in the SELECT list produces a Cartesian product of the two unnested sets. Use the multi-argument form `unnest(arr1, arr2)` (PG 9.4+) or a LATERAL subquery to zip arrays safely.

✓ Instead: SELECT t.id, u.a, u.b FROM t, unnest(t.arr1, t.arr2) AS u(a, b);

Use `unnest(arr) WITH ORDINALITY AS t(val, pos)` to get the element value and its 1-based position in the array. Essential when order matters.

example

SELECT val, pos FROM unnest(ARRAY['bronze','silver','gold']) WITH ORDINALITY AS t(val, pos);

→bronze|1, silver|2, gold|3

Common Gotchas

⚠

COUNT(col) skips NULLs but COUNT(*) does not

COUNT(column_name) counts non-NULL values only. COUNT(*) counts all rows. These produce different results when the column has NULLs.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.