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.

Practice

1. What is a composite type in PostgreSQL?

easy

A. A special index type for faster queries

B. A built-in type for storing large text data

C. A custom type that groups multiple related fields together

D. A function that returns multiple rows

Solution

1. Step 1: Understand the definition of composite types

   Composite types are user-defined types that group several fields into one unit, like a record.

2. Step 2: Compare with other options

   Options A, B, and D describe unrelated PostgreSQL features, not composite types.

3. Final Answer:

   A custom type that groups multiple related fields together -> Option C

4. Quick Check:

   Composite type = grouped fields [OK]

Hint: Composite types group fields like a mini table row [OK]

Common Mistakes:

• Confusing composite types with arrays
• Thinking composite types are indexes
• Mixing composite types with functions

2. Which of the following is the correct syntax to define a composite type named person with fields name (text) and age (integer)?

easy

A. CREATE TYPE person AS (name text, age integer);

B. CREATE TABLE person (name text, age integer);

C. CREATE TYPE person (name text, age integer);

D. CREATE COMPOSITE person AS (name text, age integer);

Solution

1. Step 1: Recall the syntax for creating composite types

   The correct syntax uses CREATE TYPE with AS and parentheses listing fields and types.

2. Step 2: Check each option

   CREATE TYPE person AS (name text, age integer); matches the correct syntax. CREATE TABLE person (name text, age integer); creates a table, not a type. CREATE TYPE person (name text, age integer); misses AS keyword. CREATE COMPOSITE person AS (name text, age integer); uses invalid keyword COMPOSITE.

3. Final Answer:

   CREATE TYPE person AS (name text, age integer); -> Option A

4. Quick Check:

   CREATE TYPE ... AS (...) is correct [OK]

Hint: Use CREATE TYPE name AS (fields) for composite types [OK]

Common Mistakes:

• Using CREATE TABLE instead of CREATE TYPE
• Omitting AS keyword
• Using non-existent COMPOSITE keyword

3. Given the composite type and table:
CREATE TYPE address AS (city text, zip integer);
CREATE TABLE users (id serial, home address);
What will the query return?
SELECT (home).city FROM users WHERE id = 1;

medium

A. An error because you cannot access composite fields like this

B. The zip code of the home address for user with id 1

C. The entire home composite value as text

D. The city name stored in the home column for user with id 1

Solution

1. Step 1: Understand composite field access syntax

   Using (home).city extracts the city field from the composite column home.

2. Step 2: Analyze the query result

   The query selects city from home for user with id 1, so it returns that city name.

3. Final Answer:

   The city name stored in the home column for user with id 1 -> Option D

4. Quick Check:

   (column).field extracts field from composite [OK]

Hint: Use (column).field to get composite field value [OK]

Common Mistakes:

• Trying to access composite fields without parentheses
• Expecting entire composite instead of single field
• Confusing city with zip field

4. Identify the error in this code snippet:
CREATE TYPE product_info AS (name text, price numeric);
CREATE TABLE products (id serial, info product_info);
INSERT INTO products (info) VALUES ('Laptop', 999.99);

medium

A. The serial type is invalid for id column

B. The INSERT statement syntax is incorrect for composite type

C. The table products should not use composite types

D. The CREATE TYPE statement is missing a semicolon

Solution

1. Step 1: Check INSERT syntax for composite types

   When inserting a composite type, values must be wrapped in parentheses or ROW(), not as separate values.

2. Step 2: Identify the error in the INSERT statement

   The statement tries to insert two separate values without wrapping them as a composite, causing syntax error.

3. Final Answer:

   The INSERT statement syntax is incorrect for composite type -> Option B

4. Quick Check:

   Insert composite as (value1, value2) or ROW(...) [OK]

Hint: Wrap composite values in parentheses or use ROW() [OK]

Common Mistakes:

• Inserting composite fields as separate values
• Forgetting parentheses around composite values
• Misunderstanding serial type usage

5. You have a composite type location with fields latitude and longitude. You want to create a function that returns this composite type and use it in a query. Which of the following is the correct way to define the function?

hard

A. CREATE FUNCTION get_location() RETURNS location AS $$ BEGIN RETURN ROW(40.7128, -74.0060); END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION get_location() RETURNS location AS $$ BEGIN RETURN 40.7128, -74.0060; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION get_location() RETURNS TABLE(latitude float, longitude float) AS $$ BEGIN RETURN (40.7128, -74.0060); END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION get_location() RETURNS location AS $$ BEGIN RETURN '40.7128, -74.0060'; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Understand how to return composite types from functions

   Functions returning composite types must return a row value, typically using ROW() constructor.

2. Step 2: Analyze each option

   CREATE FUNCTION get_location() RETURNS location AS $$ BEGIN RETURN ROW(40.7128, -74.0060); END; $$ LANGUAGE plpgsql; correctly uses RETURN ROW(...) to return the composite type. CREATE FUNCTION get_location() RETURNS location AS $$ BEGIN RETURN 40.7128, -74.0060; END; $$ LANGUAGE plpgsql; returns multiple values without row constructor (parentheses or ROW()), causing syntax error. CREATE FUNCTION get_location() RETURNS TABLE(latitude float, longitude float) AS $$ BEGIN RETURN (40.7128, -74.0060); END; $$ LANGUAGE plpgsql; returns a table, not the composite type. CREATE FUNCTION get_location() RETURNS location AS $$ BEGIN RETURN '40.7128, -74.0060'; END; $$ LANGUAGE plpgsql; returns a string, not composite.

3. Final Answer:

   CREATE FUNCTION get_location() RETURNS location AS $$ BEGIN RETURN ROW(40.7128, -74.0060); END; $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   Use RETURN ROW(...) for composite return [OK]

Hint: Use RETURN ROW(...) to return composite types in functions [OK]

Common Mistakes:

• Returning values without row constructor
• Returning string instead of composite
• Confusing RETURNS TABLE with RETURNS composite