Character types (char, varchar, text) in PostgreSQL - Mini Project: Build & Apply

Character types (char, varchar, text) in PostgreSQL

📖 Scenario: You are creating a simple database table to store information about books in a library. Each book has a fixed-length code, a title with a maximum length, and a description that can be very long.

🎯 Goal: Build a PostgreSQL table named books using the character types char, varchar, and text correctly for different columns.

📋 What You'll Learn

Create a table named books

Add a column book_code with type char(5) for fixed-length codes

Add a column title with type varchar(100) for titles with a maximum length

Add a column description with type text for long descriptions

💡 Why This Matters

🌍 Real World

Character types are used to store text data in databases, such as codes, names, and descriptions.

💼 Career

Database developers and administrators must choose appropriate character types for efficient storage and data integrity.

1

Create the books table with book_code column

Write a SQL statement to create a table named books with one column called book_code of type char(5).

PostgreSQL

CREATE TABLE books (
    -- Add book_code column here
);

Need a hint?

The char(5) type stores fixed-length strings of length 5.

2

Add the title column with varchar(100)

Add a column named title of type varchar(100) to the books table definition.

PostgreSQL

CREATE TABLE books (
    book_code char(5),
    -- Add title column here
);

Need a hint?

The varchar(100) type stores variable-length strings up to 100 characters.

3

Add the description column with text

Add a column named description of type text to the books table definition.

PostgreSQL

CREATE TABLE books (
    book_code char(5),
    title varchar(100),
    -- Add description column here
);

Need a hint?

The text type stores strings of unlimited length.

4

Complete the table definition with a primary key

Modify the books table to add a primary key constraint on the book_code column.

PostgreSQL

CREATE TABLE books (
    book_code char(5),
    title varchar(100),
    description text,
    -- Add primary key constraint here
);

Need a hint?

The primary key uniquely identifies each book by its code.