Array data type in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of selecting an array element

What is the output of this query?

SELECT ARRAY[10, 20, 30][2];

PostgreSQL

SELECT ARRAY[10, 20, 30][2];

A 20

B 10

C 30

D ARRAY[20]

💡 Hint

Remember that PostgreSQL arrays are 1-indexed.

PostgreSQL arrays start counting at 1, so the element at position 2 is 20.

❓ query_result

intermediate

Result of concatenating arrays

What is the result of this query?

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

PostgreSQL

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

A ARRAY[1,2,3,4]

B ARRAY[4,3,2,1]

C ARRAY[1,2]

D ARRAY[3,4]

💡 Hint

The || operator concatenates arrays in PostgreSQL.

The || operator joins two arrays end to end, so the result is ARRAY[1,2,3,4].

📝 Syntax

advanced

Identify the syntax error in array declaration

Which option contains a syntax error when declaring an array in PostgreSQL?

A SELECT ARRAY['a', 'b', 'c'];

B SELECT ARRAY[1, 2, 3];

C SELECT ARRAY[1,2,3]::int[];

D SELECT ARRAY(1, 2, 3);

💡 Hint

ARRAY constructor uses square brackets, not parentheses.

ARRAY constructor requires square brackets for list of elements; parentheses cause syntax error.

❓ optimization

advanced

Efficiently check if an element exists in an array

Which query is the most efficient way to check if the integer 5 exists in the integer array column 'numbers'?

A SELECT * FROM table WHERE 5 = ANY(numbers);

B SELECT * FROM table WHERE 5 IN (SELECT unnest(numbers));

C SELECT * FROM table WHERE numbers @> ARRAY[5];

D SELECT * FROM table WHERE array_position(numbers, 5) IS NOT NULL;

💡 Hint

Use the array containment operator for best performance with indexes.

The @> operator checks if the array contains the specified array and can use indexes efficiently.

🧠 Conceptual

expert

Understanding multidimensional arrays in PostgreSQL

Given the array declaration:

ARRAY[[1,2],[3,4]]

What is the result of the query?

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

PostgreSQL

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

A 4

B 2

C 3

D 1

💡 Hint

Remember that arrays are 1-indexed and multidimensional arrays are arrays of arrays.

The first index selects the first sub-array [1,2], the second index selects the second element which is 2.

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