PostgreSQL Create Tables

In this lesson you create the courses and students tables of the institute database.

Identity Columns

PostgreSQL can number new rows for you. The modern, standard way is an identity column:

Example

sql

CREATE TABLE courses (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL UNIQUE,
  duration_months INTEGER NOT NULL CHECK (duration_months > 0)
);

Output

CREATE TABLE

GENERATED ALWAYS AS IDENTITY creates 1, 2, 3... automatically
GENERATED BY DEFAULT AS IDENTITY does the same, but also lets you type an id yourself

You will often see the older shortcut SERIAL in tutorials and old projects: id SERIAL PRIMARY KEY. It still works, but identity columns are recommended for new tables.

The students Table

Example

sql

CREATE TABLE students (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  city TEXT NOT NULL DEFAULT 'Karachi',
  age INTEGER CHECK (age BETWEEN 15 AND 80),
  marks INTEGER CHECK (marks BETWEEN 0 AND 100),
  course_id INTEGER REFERENCES courses (id) ON DELETE SET NULL,
  joined_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The constraints explained

Constraint	Rule
`PRIMARY KEY`	Unique id for each row, never NULL
`NOT NULL`	A value is required
`UNIQUE`	No two students may share an email (NULLs are allowed)
`DEFAULT 'Karachi'`	Used when no city is given
`CHECK (...)`	The value must pass the condition
`REFERENCES courses (id)`	A foreign key: the course must exist
`ON DELETE SET NULL`	If a course is deleted, its students get NULL as course

Constraints in Action

Marks above 100 are refused:

Example

sql

INSERT INTO students (name, marks) VALUES ('Test', 120);

Output

ERROR:  new row for relation "students" violates check constraint "students_marks_check"
DETAIL:  Failing row contains (1, Test, null, Karachi, null, 120, null, 2026-10-15 10:12:44.31+05).

A course that does not exist is refused too:

Example

sql

INSERT INTO students (name, course_id) VALUES ('Imran', 99);

Output

ERROR:  insert or update on table "students" violates foreign key constraint "students_course_id_fkey"
DETAIL:  Key (course_id)=(99) is not present in table "courses".

PostgreSQL names each constraint for you, like students_marks_check. To choose your own name, write CONSTRAINT valid_marks CHECK (marks BETWEEN 0 AND 100).

A failed insert can still use up an identity number. That is why ids sometimes have gaps, such as 1, 2, 4. This is normal: ids only need to be unique, not continuous.

Describe a Table

In psql, \d shows a table's columns, constraints and indexes:

Example

text

institute=# \d courses
                       Table "public.courses"
     Column      |  Type   | Collation | Nullable |           Default
-----------------+---------+-----------+----------+------------------------------
 id              | integer |           | not null | generated always as identity
 title           | text    |           | not null |
 duration_months | integer |           | not null |
Indexes:
    "courses_pkey" PRIMARY KEY, btree (id)
    "courses_title_key" UNIQUE CONSTRAINT, btree (title)
Check constraints:
    "courses_duration_months_check" CHECK (duration_months > 0)
Referenced by:
    TABLE "students" CONSTRAINT "students_course_id_fkey" FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE SET NULL

ALTER TABLE

Goal	Statement
Add a column	`ALTER TABLE students ADD COLUMN phone TEXT;`
Remove a column	`ALTER TABLE students DROP COLUMN phone;`
Rename a column	`ALTER TABLE students RENAME COLUMN marks TO score;`
Change a type	`ALTER TABLE students ALTER COLUMN age TYPE SMALLINT;`
Set a default	`ALTER TABLE students ALTER COLUMN city SET DEFAULT 'Lahore';`
Make required	`ALTER TABLE students ALTER COLUMN email SET NOT NULL;`
Add a constraint	`ALTER TABLE courses ADD CONSTRAINT short_title CHECK (length(title) <= 60);`
Rename a table	`ALTER TABLE students RENAME TO learners;`

SET NOT NULL and new constraints only work if the existing rows already obey the rule.

DROP TABLE

Example

sql

DROP TABLE IF EXISTS old_results;
DROP TABLE courses CASCADE;

A table that other tables refer to cannot be dropped normally. CASCADE also removes the foreign keys that point to it (not the other tables themselves). Use it with care.

TRUNCATE students RESTART IDENTITY; empties a table and resets its id counter.

Exercise

Which column definition is the modern, recommended way to get automatic ids in PostgreSQL?

id INT AUTO_INCREMENT PRIMARY KEYid INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEYid INTEGER AUTO PRIMARY KEYid IDENTITY(1,1) PRIMARY KEY