Array data type in PostgreSQL - Step-by-Step Execution

Concept Flow - Array data type

Define array column

↓

Insert array data

↓

Query array column

↓

Access elements or whole array

↓

Use array functions/operators

↓

Get results

This flow shows how to define, insert, query, and manipulate arrays in PostgreSQL step-by-step.

Execution Sample

PostgreSQL

CREATE TABLE fruits (
  id SERIAL PRIMARY KEY,
  names TEXT[]
);

INSERT INTO fruits (names) VALUES (ARRAY['apple', 'banana', 'cherry']);

SELECT names, names[2] FROM fruits;

Create a table with an array column, insert an array of fruit names, then select the array and its second element.

Execution Table

Step	Action	Query/Command	Result/State
1	Create table with array column	CREATE TABLE fruits (id SERIAL PRIMARY KEY, names TEXT[]);	Table 'fruits' created with 'names' as text array
2	Insert array data	INSERT INTO fruits (names) VALUES (ARRAY['apple', 'banana', 'cherry']);	One row inserted with names = {apple,banana,cherry}
3	Select full array and second element	SELECT names, names[2] FROM fruits;	Returns row: names = {apple,banana,cherry}, names[2] = banana
4	Exit	No more steps	Query complete, array accessed successfully

💡 All steps executed, array data inserted and accessed correctly

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
fruits.names	undefined	{apple,banana,cherry}	{apple,banana,cherry}	{apple,banana,cherry}
names[2]	undefined	undefined	banana	banana

Key Moments - 3 Insights

Why does names[2] return 'banana' and not 'apple'?

In PostgreSQL arrays, indexing starts at 1, so names[1] is 'apple' and names[2] is 'banana' as shown in execution_table step 3.

Can we store different types in the same array column?

No, PostgreSQL arrays must be of a single data type, here TEXT[], so all elements are text strings as in step 2.

What happens if we query an index that does not exist?

Querying an out-of-range index returns NULL, but this example only queries index 2 which exists (see step 3).

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what is the value of names[2]?

A apple

B cherry

C banana

D NULL

💡 Hint

Refer to the 'Result/State' column in step 3 of execution_table

At which step is the array data inserted into the table?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Check the 'Action' column for insertion in execution_table

If we tried to access names[4], what would the result be?

A banana

B NULL

C apple

D Error

💡 Hint

Recall key_moments explanation about out-of-range index returning NULL

Concept Snapshot

PostgreSQL arrays store multiple values in one column.
Define with data_type[] like TEXT[].
Insert using ARRAY[...] syntax.
Access elements with 1-based index: array[1], array[2], etc.
Out-of-range indexes return NULL.
Use array functions for advanced queries.

Full Transcript

This visual execution trace shows how to use the array data type in PostgreSQL. First, a table is created with a column of type TEXT array. Then, an array of fruits is inserted into this column. Next, a query selects the full array and accesses the second element using 1-based indexing. The execution table tracks each step, showing the array stored and the element retrieved. Key moments clarify common confusions like indexing starting at 1 and behavior on out-of-range access. The quiz tests understanding of array indexing, insertion step, and out-of-range results. The snapshot summarizes syntax and behavior for quick reference.

Practice

1. What is the correct way to declare an integer array column named scores in a PostgreSQL table?

easy

A. scores integer[]

B. scores array(integer)

C. scores [integer]

D. scores int array

Solution

1. Step 1: Understand PostgreSQL array syntax

   PostgreSQL uses type[] to declare an array column, for example integer[].

2. Step 2: Match the syntax to the options

   Only scores integer[] uses the correct syntax scores integer[].

3. Final Answer:

   scores integer[] -> Option A

4. Quick Check:

   Array declaration = type[] [OK]

Hint: Use type[] to declare arrays in PostgreSQL [OK]

Common Mistakes:

• Using parentheses instead of square brackets
• Writing 'array(integer)' which is invalid syntax
• Trying to put brackets before the type

2. Which of the following is the correct syntax to insert an array of integers {10, 20, 30} into a column numbers in PostgreSQL?

easy

A. INSERT INTO table_name (numbers) VALUES ('{10,20,30}');

B. INSERT INTO table_name (numbers) VALUES (ARRAY(10, 20, 30));

C. INSERT INTO table_name (numbers) VALUES ([10, 20, 30]);

D. INSERT INTO table_name (numbers) VALUES (10, 20, 30);

Solution

1. Step 1: Know how to insert arrays in PostgreSQL

   Arrays are inserted as string literals with curly braces, like '{10,20,30}'.

2. Step 2: Check each option

   INSERT INTO table_name (numbers) VALUES ('{10,20,30}'); uses the correct string literal format. INSERT INTO table_name (numbers) VALUES ([10, 20, 30]); uses square brackets which is invalid. INSERT INTO table_name (numbers) VALUES (ARRAY(10, 20, 30)); uses wrong ARRAY syntax (should be ARRAY[10,20,30]). INSERT INTO table_name (numbers) VALUES (10, 20, 30); inserts separate values, not an array.

3. Final Answer:

   INSERT INTO table_name (numbers) VALUES ('{10,20,30}'); -> Option A

4. Quick Check:

   Array insert = string with curly braces [OK]

Hint: Insert arrays as strings with curly braces '{...}' [OK]

Common Mistakes:

• Using square brackets instead of curly braces
• Using ARRAY() with parentheses instead of ARRAY[]
• Inserting multiple values without array syntax

3. Given a table students with a column grades integer[], what will the query SELECT grades[2] FROM students WHERE id = 1; return if the row with id = 1 has grades = {85, 90, 78}?

medium

A. 85

B. NULL

C. 90

D. 78

Solution

1. Step 1: Understand PostgreSQL array indexing

   PostgreSQL arrays start at index 1, so grades[2] accesses the second element.

2. Step 2: Identify the second element in the array

   The array is {85, 90, 78}, so the second element is 90.

3. Final Answer:

   90 -> Option C

4. Quick Check:

   Array index 2 = 90 [OK]

Hint: PostgreSQL arrays start at 1, not 0 [OK]

Common Mistakes:

• Assuming array index starts at 0
• Confusing element positions
• Expecting entire array instead of single element

4. Consider the query: SELECT numbers[0] FROM data_table; where numbers is an integer array column. What is the likely result?

medium

A. Returns the first element of the array

B. Returns NULL because PostgreSQL arrays start at index 1

C. Syntax error due to invalid index

D. Returns the entire array

Solution

1. Step 1: Recall PostgreSQL array indexing rules

   PostgreSQL arrays start at index 1, so index 0 is invalid but does not cause syntax error.

2. Step 2: Understand behavior of accessing index 0

   Accessing index 0 returns NULL, not an error or the first element.

3. Final Answer:

   Returns NULL because PostgreSQL arrays start at index 1 -> Option B

4. Quick Check:

   Index 0 access = NULL [OK]

Hint: Index 0 in PostgreSQL arrays returns NULL, not error [OK]

Common Mistakes:

• Expecting index 0 to return first element
• Thinking it causes syntax error
• Assuming it returns entire array

5. You have a table orders with a column item_ids integer[]. How would you write a query to find all orders that include the item ID 42 anywhere in the array?

hard

A. SELECT * FROM orders WHERE item_ids[42] IS NOT NULL;

B. SELECT * FROM orders WHERE item_ids[1] = 42;

C. SELECT * FROM orders WHERE ARRAY[42] @> item_ids;

D. SELECT * FROM orders WHERE 42 = ANY(item_ids);

Solution

1. Step 1: Understand how to check if an array contains a value

   PostgreSQL provides the ANY() operator to check if a value exists in an array.

2. Step 2: Analyze each option

   SELECT * FROM orders WHERE 42 = ANY(item_ids); correctly uses 42 = ANY(item_ids). SELECT * FROM orders WHERE item_ids[42] IS NOT NULL; incorrectly tries to access index 42. SELECT * FROM orders WHERE ARRAY[42] @> item_ids; uses @> which checks if array contains another array, but syntax is reversed (should be item_ids @> ARRAY[42]). SELECT * FROM orders WHERE item_ids[1] = 42; only checks first element.

3. Final Answer:

   SELECT * FROM orders WHERE 42 = ANY(item_ids); -> Option D

4. Quick Check:

   Use ANY() to check value in array [OK]

Hint: Use value = ANY(array) to check membership [OK]

Common Mistakes:

• Using wrong operator like @> with reversed operands
• Checking only first element instead of whole array
• Trying to access array at index equal to value