SQL Academy · Lesson

Array Columns Basics

Create and read array values.

Array Columns Basics is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is an Array Column?

PostgreSQL supports array columns, which let a single cell hold multiple values of the same type. Instead of storing comma-separated text you get a proper typed list you can query, index, and manipulate with built-in functions.

Arrays can hold integers, text, booleans, dates, or any other PostgreSQL type. The syntax looks like integer[] or text[].

Declaring an Array Column

Add [] after any type name when writing a CREATE TABLE statement to declare an array column. The table below stores a list of tags for each article.

CREATE TABLE articles (
  id      serial PRIMARY KEY,
  title   text NOT NULL,
  tags    text[]
);

Inserting Array Values

Use curly-brace literals wrapped in single quotes to insert array data. Each element is separated by a comma inside the braces: '{"sql","database"}'.

Alternatively you can use the ARRAY[...] constructor syntax shown in the second row.

INSERT INTO articles (title, tags) VALUES
  ('Intro to SQL',    '{"sql","beginner"}'),
  ('Advanced Joins',  ARRAY['sql','joins','performance']),
  ('NULL Handling',   '{"sql","nulls","tips"}');

Reading the Whole Array

Selecting an array column works just like selecting any other column. PostgreSQL returns the entire array as a single value in the result set, displayed in curly-brace format.

SELECT id, title, tags
FROM   articles;

Accessing a Single Element

Array subscripts in PostgreSQL are 1-based (the first element is at index 1, not 0). Use square brackets after the column name to access a specific element.

SELECT title,
       tags[1] AS first_tag,
       tags[2] AS second_tag
FROM   articles;

Array Slicing

You can extract a range of elements using the lower:upper slice syntax inside square brackets. Both bounds are inclusive.

SELECT title,
       tags[1:2] AS first_two_tags
FROM   articles;

Filtering With ANY

To find rows where an array contains a specific value, use the = ANY(column) expression. This returns rows where at least one element of the array matches the given value.

SELECT id, title, tags
FROM   articles
WHERE  'joins' = ANY(tags);

Filtering With the @> Operator

The contains operator @> checks whether the left array contains all elements of the right array. This is useful when you want rows that include a set of required tags.

SELECT id, title, tags
FROM   articles
WHERE  tags @> ARRAY['sql','tips'];

Getting the Array Length

The array_length(arr, dimension) function returns the number of elements along a given dimension. For a one-dimensional array use 1 as the second argument.

SELECT title,
       array_length(tags, 1) AS tag_count
FROM   articles
ORDER  BY tag_count DESC;

Appending Elements

Use the array_append(array, element) function or the concatenation operator || to add a new element to the end of an existing array column.

UPDATE articles
SET    tags = array_append(tags, 'featured')
WHERE  title = 'Intro to SQL';

SELECT title, tags FROM articles WHERE title = 'Intro to SQL';

Unnesting an Array Into Rows

The unnest(array) function expands an array into a set of rows — one row per element. This is handy for aggregations, joins, or any operation that needs each element separately.

SELECT title, unnest(tags) AS tag
FROM   articles
ORDER  BY title, tag;

Quick Check

Test your understanding of PostgreSQL array columns.

Recap: Array Columns Basics

In this lesson you learned how PostgreSQL array columns work from end to end:

Declare arrays with type[] syntax in CREATE TABLE
Insert values using curly-brace literals or ARRAY[...]
Access elements by 1-based index and slices with lower:upper
Filter with = ANY(col) for a single value or @> for a subset
Measure length with array_length(col, 1)
Add elements using array_append() or ||
Expand arrays into rows with unnest()

Arrays are a powerful PostgreSQL feature that can simplify schemas when a list of values belongs to a single entity.

Frequently asked questions

Is the “Array Columns Basics” lesson free?

Yes — the full text of “Array Columns Basics” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Array Columns Basics”?

Create and read array values. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Array Columns Basics” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy