Composite types in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of selecting composite type fields

Given the composite type person defined as CREATE TYPE person AS (name TEXT, age INT); and a table employees with a column info of type person, what is the output of this query?

SELECT info.name, info.age FROM employees WHERE info.age > 30;

PostgreSQL

CREATE TYPE person AS (name TEXT, age INT);
CREATE TABLE employees (id SERIAL PRIMARY KEY, info person);
INSERT INTO employees (info) VALUES (ROW('Alice', 28)), (ROW('Bob', 35)), (ROW('Carol', 40));

SELECT info.name, info.age FROM employees WHERE info.age > 30;

A [{"name": "Bob", "age": 35}, {"name": "Carol", "age": 40}]

B [{"info": {"name": "Bob", "age": 35}}, {"info": {"name": "Carol", "age": 40}}]

C [{"name": "Alice", "age": 28}]

D SyntaxError

💡 Hint

Access composite type fields using dot notation like info.name.

The query filters employees with age > 30 and selects the name and age fields from the composite type column info. Only Bob and Carol satisfy the condition.

📝 Syntax

intermediate

Correct syntax to create a composite type

Which of the following is the correct syntax to create a composite type address with fields street (TEXT), city (TEXT), and zip (INT)?

A CREATE COMPOSITE TYPE address (street TEXT, city TEXT, zip INT);

B CREATE TYPE address AS (street TEXT, city TEXT, zip INT);

C CREATE TYPE address {street TEXT, city TEXT, zip INT};

D CREATE TYPE address (street TEXT, city TEXT, zip INT);

💡 Hint

Use the CREATE TYPE ... AS syntax for composite types.

PostgreSQL uses CREATE TYPE name AS (field1 type1, ...) to define composite types. Other options are invalid syntax.

❓ optimization

advanced

Optimizing queries with composite types

You have a table orders with a composite type column customer_info (name TEXT, email TEXT). You want to find all orders where the customer's email ends with '@example.com'. Which query is the most efficient?

A SELECT * FROM orders WHERE customer_info.email LIKE '[e-mail]';

B SELECT * FROM orders WHERE (customer_info).email LIKE '[e-mail]';

C SELECT * FROM orders WHERE customer_info LIKE '[e-mail]';

D SELECT * FROM orders WHERE customer_info.email = '@example.com';

💡 Hint

Access composite fields directly with dot notation.

Option A correctly accesses the email field inside the composite type and uses LIKE to filter emails ending with '@example.com'. Option A is valid but unnecessary parentheses add overhead. Option A tries to apply LIKE to the whole composite type, which is invalid. Option A checks equality, not pattern matching.

🔧 Debug

advanced

Debugging composite type insertion error

You run this insertion:
INSERT INTO employees (info) VALUES ('John', 30);
and get an error. Why?

A You need to cast the values to the composite type explicitly.

B The column info does not exist in employees table.

C The age field must be a string, not an integer.

D You must insert a composite type value using ROW() or parentheses, not separate values.

💡 Hint

Composite type columns expect a single composite value, not separate values.

You must insert composite type values as a single composite value, e.g., ROW('John', 30) or ('John', 30). Just listing values separated by commas is invalid.

🧠 Conceptual

expert

Understanding composite type behavior in functions

Consider a PostgreSQL function that returns a composite type person (name TEXT, age INT). What is the result of calling this function in a SELECT statement without aliasing the composite fields?

A The function returns JSON objects representing the composite type.

B The function returns separate columns named name and age automatically.

C The result is a single column of type person containing composite values.

D The query will raise a syntax error because composite types cannot be returned.

💡 Hint

Functions returning composite types return one column of that type unless expanded.

When a function returns a composite type, the result is a single column of that composite type. To get separate columns, you must use SELECT (func()).* or alias the fields.