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.