Intro to Arrays: What They Are and When to Use Them

You've probably already heard about arrays more than once. In different programming languages, they mean pretty much the same thing. But there are some quirks.

An array in PostgreSQL is a data type that lets you store a set of values of the same type in a single table column. It's like you're not just writing one value, but a whole "box" of data into a cell. This could be a list of numbers, strings, dates, or even more complex objects.

To put it abstractly, you can think of an array like a toolbox. Instead of carrying a whole bunch of wrenches in your hands (each wrench as a separate column/row), you toss them all into a handy suitcase (one column holding an array).

Two Ways to Create Arrays

PostgreSQL has two main syntaxes for creating arrays. It's like two different ways to pack your suitcase — the result is the same, but the approach is different:

1. Curly braces syntax {}

This is the more compact and often-used way:

-- Array of strings
'{"apple", "banana", "cherry"}'

-- Array of numbers
'{1, 2, 3, 4, 5}'

-- Array of dates
'{"2023-01-01", "2023-01-02", "2023-01-03"}'

2. ARRAY[] constructor syntax

A more explicit and readable way:

-- Array of strings
ARRAY['apple', 'banana', 'cherry']

-- Array of numbers
ARRAY[1, 2, 3, 4, 5]

-- Array of dates
ARRAY['2023-01-01'::DATE, '2023-01-02'::DATE, '2023-01-03'::DATE]

When to Use Arrays

Arrays are a PostgreSQL superpower that let you solve problems where it's awkward to create extra tables or columns. They're perfect for:

1. Storing lists, tags, or categories

Imagine we're building a database for an online store. Each product might have several tags, for example:

• For a t-shirt, the tags could be: ["clothing", "men's", "sports"].
• For headphones, the tags: ["electronics", "wireless", "gadgets"].

With arrays, you can store these tags in a single column without needing a separate tags table, saving space and making your data structure simpler.

-- Products table with an array of tags
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[]  -- Array of strings for tags
);

-- Example data
INSERT INTO products (name, tags)
VALUES
    ('T-shirt', ARRAY['clothing', 'men''s', 'sports']),
    ('Headphones', ARRAY['electronics', 'wireless', 'gadgets']);

2. Storing a list of orders

If you're working with orders, an array can help you keep a list of products in a single order:

-- Orders table with an array of product IDs
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    product_ids INTEGER[]  -- Array of product IDs
);

-- Example data
INSERT INTO orders (customer_name, product_ids)
VALUES
    ('Anna', ARRAY[1, 2, 3]),
    ('Otto', ARRAY[4, 5]);

Now, each order has a list of products that were bought. Pretty handy, right?

3. Simple configs and settings

   When you need to store simple lists of user settings or preferences:

   CREATE TABLE user_preferences (
   user_id INTEGER PRIMARY KEY,
   notification_types TEXT[],
   allowed_days INTEGER[]  -- days of the week: 1-7
   );
   

Comparing Arrays to Other Data Types

It's important to realize that arrays are just another tool in the PostgreSQL toolbox, and they're not for every job. For example, instead of arrays, you might use a join table (many-to-many relationship) or the JSON/JSONB data type.

Arrays vs JSONB

Arrays are great when you know for sure you'll be storing multiple values of the same type. They're simpler, faster, and take up less space.

JSONB is better for more complex structures, like if you need to store objects with a flexible structure. But JSONB can be trickier to work with.

Example of an array and JSONB for the same set of data:

-- Array
ARRAY['clothing', 'men''s', 'sports']

-- JSONB
'["clothing", "men''s", "sports"]'::JSONB

If you need to do stuff like sorting, filtering, or indexing a lot, arrays are usually the better choice.

Real-World Example: Using Arrays

Let's say you're building an app to track attendance at a university. Each lecture has a list of students who attended, but you don't need a separate table to link students and lectures. Instead, you can use arrays:

-- Lectures table with an array of students
CREATE TABLE lectures (
    id SERIAL PRIMARY KEY,
    topic TEXT NOT NULL,
    students INTEGER[]  -- Array of student IDs
);

-- Example data
INSERT INTO lectures (topic, students)
VALUES
    ('Databases', ARRAY[101, 102, 103]),
    ('Algorithms', ARRAY[104, 105]);

Now you can easily query the list of students for any lecture, and add or remove a student from the array.

Important Things to Know When Using Arrays in PostgreSQL

1. The data type inside the array has to be the same. PostgreSQL won't let you mix, say, strings and numbers in one array.

-- Works
ARRAY[1, 2, 3]  -- Array of numbers

-- Error: mixing strings and numbers
ARRAY['apple', 42, 'cherry']

2. Array indexing starts at 1, not 0. Don't forget this if you're used to programming languages where indexing starts at 0 (Python, JavaScript, C++, etc.).
3. NULL support: arrays can contain NULL values
4. Multidimensional: PostgreSQL supports multidimensional arrays

2

Task

SQL SELF, level 35, lesson 0

Locked

Creating a table with arrays

Creating a table with arrays

Open