DEFAULT values and expressions in PostgreSQL - Mini Project: Build & Apply

Using DEFAULT Values and Expressions in PostgreSQL

📖 Scenario: You are creating a simple database table to store information about books in a library. Some columns should have default values to simplify data entry and ensure consistency.

🎯 Goal: Create a table called books with columns that use DEFAULT values and expressions to automatically fill in missing data.

📋 What You'll Learn

Create a table named books with columns id, title, author, copies, and added_date.

Set id as a primary key with automatic increment.

Set copies to have a default value of 1.

Set added_date to have a default value of the current date.

Insert a row without specifying copies and added_date to see defaults in action.

💡 Why This Matters

🌍 Real World

DEFAULT values help reduce errors and simplify data entry in real databases by automatically filling in common or expected values.

💼 Career

Understanding DEFAULT values is essential for database design and maintenance roles, ensuring data consistency and efficient workflows.

1

Create the books table with basic columns

Write a SQL statement to create a table called books with columns: id as an integer primary key with auto-increment, title as text, and author as text.

PostgreSQL

-- Create the books table with id, title, and author columns
-- Your code here

Need a hint?

Use SERIAL PRIMARY KEY for auto-incrementing id.

2

Add columns with DEFAULT values

Alter the books table creation SQL to add a column copies as integer with a default value of 1, and a column added_date as date with a default value of the current date using CURRENT_DATE.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author TEXT,
    copies INTEGER DEFAULT 1,
    added_date DATE DEFAULT CURRENT_DATE
);
-- Your code here

Need a hint?

Use DEFAULT 1 for copies and DEFAULT CURRENT_DATE for added_date.

3

Insert a book without specifying copies and added_date

Write an INSERT statement to add a book with title 'The Great Gatsby' and author 'F. Scott Fitzgerald' without specifying copies or added_date.

PostgreSQL

INSERT INTO books (title, author) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald');
-- Your code here

Need a hint?

Only specify title and author in the INSERT statement.

4

Verify the default values are applied

Write a SELECT statement to retrieve all columns from the books table to check that copies is 1 and added_date is set to the current date for the inserted book.

PostgreSQL

SELECT * FROM books;
-- Your code here

Need a hint?

Use SELECT * FROM books to see all columns and verify defaults.