Common Mistakes When Working with Arrays and How to Avoid Them

Today we're wrapping up our fun journey into working with arrays in PostgreSQL. On the agenda — the typical mistakes you might run into and, most importantly, how to dodge them. If you've ever caught yourself thinking "this array is acting weird again," this lecture is for you. Let's dig in.

Mistakes When Creating Arrays: Problems with Data Types Inside the Array

When you create arrays in PostgreSQL, it's important to remember that all elements in the array have to be of the same type. For example:

SELECT ARRAY[1, 2, 'three'];
-- Error: all elements in the array must be of the same type

PostgreSQL won't let you mix numbers and strings in one array. If you really need to do that, use type casting:

SELECT ARRAY[1::TEXT, 2::TEXT, 'three'];
-- Now the array is made up of strings

Mistakes When Using ARRAY[] with Different Data Types

By default, PostgreSQL tries to figure out the array type based on its contents. If you give it ambiguous data, get ready for an error:

SELECT ARRAY[1, NULL];
-- Error: PostgreSQL doesn't know how to interpret NULL

To fix this, just specify the data type explicitly:

SELECT ARRAY[1, NULL]::INTEGER[];
-- Everything works fine now

Mistakes When Extracting Data: Array Index Problems

If you're used to programming in Python or JavaScript, where array indexing starts at zero, PostgreSQL has a surprise for you. Here, arrays are indexed starting from 1.

SELECT ARRAY[10, 20, 30][0];
-- Error: index must start from 1

The correct query:

SELECT ARRAY[10, 20, 30][1];
-- Result: 10

Mistakes When Using Functions to Extract Data

Functions like unnest() can be confusing if you don't realize that they "unfold" the array across all rows:

CREATE TEMP TABLE example (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);

INSERT INTO example (tags) VALUES (ARRAY['tag1', 'tag2']), (ARRAY['tag3']);

SELECT unnest(tags) FROM example;
-- Result:
-- tag1
-- tag2
-- tag3

If you need to keep the row context (like id), add it explicitly:

SELECT id, unnest(tags) AS tag FROM example;
-- Result:
-- id | tag
--  1 | tag1
--  1 | tag2
--  2 | tag3

Mistakes When Filtering and Comparing Arrays

1. Incorrect Use of @>, <@, && Operators

These operators are meant for specific tasks:

• @> checks if an array contains another array.
• <@ checks if an array is contained within another.
• && checks if two arrays overlap.

You get an error if you use them wrong:

SELECT ARRAY[1, 2, 3] @> 2;
-- Error: @> operator is for arrays

Correct way:

SELECT ARRAY[1, 2, 3] @> ARRAY[2];
-- Result: true

2. Performance Issues When You Forget Indexes

If you use array operators a lot and notice your queries slowing down, chances are you forgot about indexing. Here's how to index an array column:

CREATE INDEX idx_tags ON example USING GIN (tags);

Now queries with @> and && will run way faster.

Mistakes When Modifying Arrays

1. Removing and Adding Values

The array_remove() and array_append() functions don't change the array "in place" — they return a new array. If you expect the original array to change, that's a mistake:

UPDATE example
SET tags = array_remove(tags, 'tag1');
-- Now the array is updated

If you forget to include SET tags =, SQL will run the query but the array won't change.

2. Duplicate Data When Using array_append()

The array_append() function doesn't check if the element is already there. This can lead to duplicates:

SELECT array_append(ARRAY['tag1', 'tag2'], 'tag1');
-- Result: {tag1, tag2, tag1}

If you want to avoid duplicates, use filtering:

SELECT array_remove(array_append(ARRAY['tag1', 'tag2'], 'tag1'), 'tag1') || 'tag1';
-- Result: {tag1, tag2}

Tips to Avoid Mistakes

To avoid the mistakes above when working with arrays:

1. Check your data types. Always specify the array type explicitly if there's any ambiguity.
2. Be careful with indexes. Remember, arrays in PostgreSQL are indexed from 1.
3. Optimize your array queries. Use indexing to speed up comparison and filtering operations.
4. Test on small samples. If your array queries are slow, try them on a limited dataset to spot bottlenecks.
5. Prevent duplicates. Use extra checks when adding elements to an array if repeats aren't allowed.

Arrays in PostgreSQL, like any powerful tool, deserve respect and caution. With these tips, your arrays will never be the reason for sleepless nights again (well, almost). Good luck designing and optimizing your databases!