PostgreSQL Creating Databases and Tables

A database is the top-level container that holds all related tables, functions, and other objects. A table is the actual structure where data lives, organized into rows and columns. This topic covers how to create and manage databases and tables in PostgreSQL.

Creating a Database

To create a new database, use the CREATE DATABASE command followed by a name.

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE school;

This creates an empty database named school. The name must begin with a letter or underscore and cannot contain spaces. By convention, database names are written in lowercase.

Listing All Databases

In psql, run the following command to see all existing databases:

\l

Connecting to a Database

After creating a database, connect to it before creating tables inside it:

\c school

The prompt will change to school=#, confirming the connection.

Dropping a Database

To permanently delete a database and all its contents, use DROP DATABASE:

DROP DATABASE school;

This action is irreversible. Always double-check before running it. A database cannot be dropped while any client is connected to it.

Understanding Tables

A table is like a spreadsheet inside the database. It has named columns with fixed data types, and each row represents one record. For example, a students table might have columns for student ID, name, age, and enrollment date. Each row holds the data for one student.

Creating a Table

Syntax

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Example: A Simple Students Table

CREATE TABLE students (
    student_id   SERIAL PRIMARY KEY,
    first_name   VARCHAR(50) NOT NULL,
    last_name    VARCHAR(50) NOT NULL,
    age          SMALLINT,
    email        VARCHAR(100) UNIQUE,
    enrolled_on  DATE DEFAULT CURRENT_DATE
);

Breaking this down column by column:

• student_id SERIAL PRIMARY KEY — auto-generates a unique integer for each student; the primary key uniquely identifies every row
• first_name VARCHAR(50) NOT NULL — text up to 50 characters; cannot be empty
• last_name VARCHAR(50) NOT NULL — same as first name
• age SMALLINT — a small integer; no constraint, so it can be NULL
• email VARCHAR(100) UNIQUE — no two students can share the same email
• enrolled_on DATE DEFAULT CURRENT_DATE — stores the enrollment date; defaults to today if no date is provided

Common Constraints

Constraints are rules applied to columns to control what data is allowed. They help keep data accurate and consistent.

Viewing Table Structure

After creating a table, inspect its structure using the psql describe command:

\d students

This displays each column name, data type, and any constraints applied to it.

Listing All Tables in a Database

\dt

This lists all tables in the currently connected database.

Modifying an Existing Table

The ALTER TABLE command modifies the structure of a table after it has been created.

Adding a New Column

ALTER TABLE students ADD COLUMN phone VARCHAR(15);

Removing a Column

ALTER TABLE students DROP COLUMN phone;

Renaming a Column

ALTER TABLE students RENAME COLUMN age TO student_age;

Changing a Column's Data Type

ALTER TABLE students ALTER COLUMN student_age TYPE INTEGER;

Renaming a Table

ALTER TABLE students RENAME TO learners;

Dropping a Table

To permanently delete a table and all its data:

DROP TABLE students;

To avoid an error if the table does not exist, use the safer version:

DROP TABLE IF EXISTS students;

Schema — Organizing Tables Within a Database

PostgreSQL uses schemas to organize tables within a database. A schema is like a folder inside the database. The default schema is called public. Every table created without specifying a schema goes into the public schema automatically.

Creating a Schema

CREATE SCHEMA academics;

Creating a Table Inside a Schema

CREATE TABLE academics.students (
    student_id SERIAL PRIMARY KEY,
    name       VARCHAR(100)
);

Schemas are useful in large projects where multiple teams work on the same database and need to separate their tables cleanly.

Example: Building a Complete Mini Database

Create a database for a bookstore, then create a table for books:

-- Create the database
CREATE DATABASE bookstore;

-- Connect to it (run in psql)
-- \c bookstore

-- Create the books table
CREATE TABLE books (
    book_id     SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    author      VARCHAR(100) NOT NULL,
    price       NUMERIC(7, 2) CHECK (price >= 0),
    published   DATE,
    in_stock    BOOLEAN DEFAULT TRUE
);

Lines starting with -- are comments. They are ignored by PostgreSQL and are used to explain the code.

Key Points

• CREATE DATABASE creates a new database; DROP DATABASE deletes it permanently.
• CREATE TABLE defines the structure of a table including column names, types, and constraints.
• SERIAL PRIMARY KEY is the most common way to create an auto-incrementing unique identifier.
• ALTER TABLE allows adding, removing, or changing columns after a table is created.
• Schemas organize tables within a database; the default schema is public.
• Always use DROP TABLE IF EXISTS to avoid errors when dropping tables that may not exist.