Composite types in PostgreSQL - Mini Project: Build & Apply

Create and Use Composite Types in PostgreSQL

📖 Scenario: You are building a simple database for a library. Each book has a title, an author, and a publication year. You want to group these details into a single composite type to keep your database organized.

🎯 Goal: Create a composite type called book_info with fields for title, author, and year. Then create a table that uses this composite type to store book records.

📋 What You'll Learn

Create a composite type named book_info with fields title (text), author (text), and year (integer).

Create a table named library with a column info of type book_info.

Insert at least two records into the library table using the composite type.

Write a SELECT query to retrieve all records from the library table.

💡 Why This Matters

🌍 Real World

Composite types help organize related data fields together, making database design cleaner and queries easier to write and read.

💼 Career

Understanding composite types is useful for database developers and administrators working with PostgreSQL to build efficient and maintainable data models.

1

Create the composite type book_info

Write a SQL statement to create a composite type called book_info with three fields: title of type text, author of type text, and year of type integer.

PostgreSQL

-- Create composite type book_info with fields title, author, and year
-- Your code here

Need a hint?

Use CREATE TYPE followed by the type name and define fields inside parentheses.

2

Create the library table with a column of type book_info

Write a SQL statement to create a table called library with one column named info of type book_info.

PostgreSQL

CREATE TYPE book_info AS (
    title text,
    author text,
    year integer
);

-- Create table library with column info of type book_info
-- Your code here

Need a hint?

Use CREATE TABLE and specify the column info with the composite type book_info.

3

Insert records into the library table using the composite type

Write two SQL INSERT statements to add these books into the library table: (1) Title: '1984', Author: 'George Orwell', Year: 1949; (2) Title: 'To Kill a Mockingbird', Author: 'Harper Lee', Year: 1960. Use the composite type book_info to insert the data.

PostgreSQL

CREATE TYPE book_info AS (
    title text,
    author text,
    year integer
);

CREATE TABLE library (
    info book_info
);

-- Insert two records into library using book_info
-- Your code here

Need a hint?

Use INSERT INTO library VALUES (ROW(...)) with the values matching the composite type fields.

4

Select all records from the library table

Write a SQL SELECT statement to retrieve all records from the library table, showing the info column.

PostgreSQL

CREATE TYPE book_info AS (
    title text,
    author text,
    year integer
);

CREATE TABLE library (
    info book_info
);

INSERT INTO library VALUES (ROW('1984', 'George Orwell', 1949)::book_info);
INSERT INTO library VALUES (ROW('To Kill a Mockingbird', 'Harper Lee', 1960)::book_info);

-- Select all records from library
-- Your code here

Need a hint?

Use SELECT info FROM library; to get all records.