PostgreSQL Series — Notes & Queries

01 DDL Create users Table — Basic Version

▾

CREATE TABLE users(
  id       SERIAL       PRIMARY KEY,
  name     VARCHAR(100),
  email    VARCHAR(150),
  is_active BOOLEAN
);

Starting point — a bare-bones users table. We'll upgrade this with constraints and relations next.

02 DDL Create departments Table

▾

CREATE TABLE departments (
  id   SERIAL       PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

UNIQUE on name ensures no duplicate department entries — useful with ON CONFLICT later.

03 DDL Upgraded users Table with Constraints

▾

CREATE TABLE users (
  id            SERIAL       PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  email         VARCHAR(150) UNIQUE NOT NULL,
  role          VARCHAR(50)  NOT NULL
                  CHECK (role IN ('student','professor','admin')),
  department_id INT          REFERENCES departments(id)
                  ON DELETE SET NULL,
  metadata      JSONB,
  is_active     BOOLEAN      DEFAULT TRUE,
  created_at    TIMESTAMP    DEFAULT NOW()
);

JSONB for flexible metadata. ON DELETE SET NULL means removing a department won't delete users — their department_id just becomes NULL.

04 DQL Inspect Public Tables with pg_tables

▾

SELECT *
FROM   pg_tables
WHERE  schemaname = 'public';

A handy system query to list all user-created tables in the public schema — great for debugging your schema setup.

05 DDL Drop a Table

▾

DROP TABLE departments;

⚠️ DROP TABLE is irreversible — it deletes the table and all its data permanently. Always double-check before running in production.

06 DML Single Record Insert into Departments

▾

INSERT INTO departments (name)
VALUES ('Computer Science')
ON CONFLICT (name) DO NOTHING;

ON CONFLICT DO NOTHING makes the insert idempotent — safe to run multiple times without errors or duplicates.

07 DML Bulk Insert — All Departments

▾

INSERT INTO departments (id, name) VALUES
  (2, 'Mechanical Engineering'),
  (3, 'Electronics & Communication'),
  (4, 'Business Administration'),
  (5, 'Basic Sciences'),
  (6, 'Computer Science')
ON CONFLICT (name) DO NOTHING;

Multi-row inserts are faster than individual inserts — a single round-trip to the database instead of many.

08 DQL Select All Users

▾

SELECT * FROM users;

The most fundamental query — use this to verify your inserts worked. In production, avoid SELECT * and name your columns explicitly.

09 DDL Practice Task — Create courses Table

▾

-- Task: Create a courses table with id, title, and fee
-- Hint: Use NUMERIC(10, 2) for the fee column

CREATE TABLE courses (
  id    SERIAL          PRIMARY KEY,
  title VARCHAR(200)   NOT NULL,
  fee   NUMERIC(10,2)  NOT NULL
);

NUMERIC(10, 2) stores up to 10 digits total with exactly 2 decimal places — the standard choice for money. Never use FLOAT for currency!

Learning PostgreSQL
From Scratch

Complete Episode Resources

Learning PostgreSQLFrom Scratch

Complete Episode Resources

Learning PostgreSQL
From Scratch