Composite types in PostgreSQL - Step-by-Step Execution

Concept Flow - Composite types

Define Composite Type

↓

Create Table Using Composite Type

↓

Insert Data into Table

↓

Query Data

↓

Access Composite Type Fields

First, you define a composite type, then create a table using it, insert data, query it, and access its fields.

Execution Sample

PostgreSQL

CREATE TYPE address AS (
  street TEXT,
  city TEXT,
  zip_code TEXT
);

CREATE TABLE person (
  id SERIAL PRIMARY KEY,
  name TEXT,
  home_address address
);

INSERT INTO person (name, home_address) VALUES
('Alice', ROW('123 Main St', 'Springfield', '12345'));

SELECT name, home_address.city FROM person;

This code defines a composite type 'address', creates a table 'person' with a column of that type, inserts a row, and queries the city field.

Execution Table

Step	Action	Evaluation	Result
1	CREATE TYPE address	Defines composite type with fields street, city, zip_code	Type 'address' created
2	CREATE TABLE person	Creates table with id, name, home_address of type address	Table 'person' created
3	INSERT INTO person	Insert row with name='Alice' and home_address=('123 Main St','Springfield','12345')	Row inserted with id=1
4	SELECT name, home_address.city FROM person	Retrieve name and city field from home_address	Returns row: name='Alice', city='Springfield'
5	End of operations	No more commands	Execution complete

💡 All commands executed successfully; query returned expected data.

Variable Tracker

Variable	Start	After Step 3	Final
address type	undefined	defined with fields street, city, zip_code	defined
person table	undefined	created with columns id, name, home_address	created
person rows	empty	1 row inserted: (1, 'Alice', ('123 Main St', 'Springfield', '12345'))	1 row present

Key Moments - 3 Insights

How do you access a field inside a composite type in a SELECT query?

You use dot notation like home_address.city as shown in step 4 of the execution_table.

Can you insert data into a composite type column using a simple tuple?

Yes, as in step 3, you insert data using parentheses with values matching the composite type fields.

Is the composite type created before the table that uses it?

Yes, step 1 creates the composite type before step 2 creates the table that uses it.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of step 4?

A Returns error due to wrong field access

B Returns empty result

C Returns row with name='Alice' and city='Springfield'

D Returns all fields of home_address

💡 Hint

Check the 'Result' column in step 4 of the execution_table.

At which step is the composite type 'address' defined?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Action' column to find when 'CREATE TYPE address' happens.

If you wanted to add a new field 'state' to the composite type, which step would change?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Composite type definition happens in step 1, so changes to its fields affect that step.

Concept Snapshot

Composite types in PostgreSQL:
- Use CREATE TYPE to define a composite type with named fields.
- Use the composite type as a column type in tables.
- Insert data using tuples matching the composite structure.
- Access fields with dot notation (e.g., column.field).
- Composite types help group related data logically.

Full Transcript

This visual execution trace shows how to use composite types in PostgreSQL. First, a composite type named 'address' is created with fields street, city, and zip_code. Then, a table 'person' is created with a column 'home_address' of type 'address'. Next, a row is inserted into 'person' with name 'Alice' and an address tuple. Finally, a SELECT query retrieves the name and the city field from the composite address. The trace highlights how composite types are defined before use, how data is inserted as tuples, and how to access individual fields using dot notation.