Serial and identity columns in PostgreSQL - Mini Project: Build & Apply

Creating a Table with Serial and Identity Columns in PostgreSQL

📖 Scenario: You are setting up a simple database table to store information about books in a library. Each book needs a unique ID that automatically increases whenever a new book is added.

🎯 Goal: Create a PostgreSQL table called books with an auto-incrementing primary key using both SERIAL and IDENTITY column types.

📋 What You'll Learn

Create a table named books with columns id_serial and id_identity as auto-incrementing primary keys.

Use SERIAL type for the id_serial column.

Use GENERATED ALWAYS AS IDENTITY for the id_identity column.

Add a title column of type VARCHAR(100) to store the book title.

💡 Why This Matters

🌍 Real World

Auto-incrementing IDs are used in databases to uniquely identify records without manual input, such as assigning unique book IDs in a library system.

💼 Career

Understanding how to create and manage auto-incrementing columns is essential for database design and is a common task for database administrators and backend developers.

1

Create the books table with a SERIAL column

Write a SQL statement to create a table called books with three columns: id_serial as a SERIAL primary key, title as VARCHAR(100), and id_identity as an integer (you will update this later).

PostgreSQL

CREATE TABLE books (
    id_serial SERIAL PRIMARY KEY,
    title VARCHAR(100),
    id_identity INTEGER
);
-- Your code here

Need a hint?

Use SERIAL for id_serial to auto-increment IDs.

2

Add an IDENTITY column configuration

Modify the id_identity column to use GENERATED ALWAYS AS IDENTITY so it auto-increments like id_serial.

PostgreSQL

CREATE TABLE books (
    id_serial SERIAL PRIMARY KEY,
    title VARCHAR(100),
    id_identity INTEGER GENERATED ALWAYS AS IDENTITY
);
-- Your code here

Need a hint?

Use GENERATED ALWAYS AS IDENTITY after the INTEGER type.

3

Set the primary key to the identity column

Change the table so that id_identity is the primary key instead of id_serial. Remove the primary key from id_serial.

PostgreSQL

CREATE TABLE books (
    id_serial SERIAL,
    title VARCHAR(100),
    id_identity INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
-- Your code here

Need a hint?

Remove PRIMARY KEY from id_serial and add it to id_identity.

4

Finalize the table with both auto-increment columns

Ensure the table books has id_serial as a SERIAL column, title as VARCHAR(100), and id_identity as an INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY column.

PostgreSQL

CREATE TABLE books (
    id_serial SERIAL,
    title VARCHAR(100),
    id_identity INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
-- Your code here

Need a hint?

Check that all columns are defined exactly as required.