CREATE TABLE in PostgreSQL with Constraints and Defaults

A realistic table

CREATE TABLE students (
  id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  roll_no      varchar(20)  NOT NULL UNIQUE,
  name_en      text         NOT NULL,
  name_bn      text,
  email        citext       UNIQUE,              -- case-insensitive, needs the citext extension
  age          smallint     CHECK (age BETWEEN 5 AND 100),
  department   varchar(10)  NOT NULL DEFAULT 'CSE',
  cgpa         numeric(3,2) NOT NULL DEFAULT 0 CHECK (cgpa >= 0 AND cgpa <= 4),
  tags         text[]       DEFAULT '{}',
  prefs        jsonb        NOT NULL DEFAULT '{}'::jsonb,
  admitted_on  date         NOT NULL,
  created_at   timestamptz  NOT NULL DEFAULT now(),
  updated_at   timestamptz  NOT NULL DEFAULT now()
);

COMMENT ON TABLE students IS 'Enrolled students';
COMMENT ON COLUMN students.cgpa IS 'On a 4.00 scale';

Generated columns

A stored generated column is computed from other columns on every write:

ALTER TABLE students
ADD COLUMN full_label text
GENERATED ALWAYS AS (name_en || ' (' || department || ')') STORED;

Postgres supports STORED only; there is no virtual generated column yet.

Table-level constraints

CREATE TABLE enrollments (
  student_id int NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  course_id  int NOT NULL REFERENCES courses(id),
  semester   varchar(10) NOT NULL,
  grade      char(2),
  PRIMARY KEY (student_id, course_id, semester),
  CONSTRAINT valid_grade CHECK (grade IN ('A+','A','B','C','D','F'))
);

Inspecting a table

\d students
\d+ students

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'students'
ORDER BY ordinal_position;

SELECT pg_size_pretty(pg_total_relation_size('students'));

Copying tables

CREATE TABLE students_backup (LIKE students INCLUDING ALL);   -- structure + indexes + defaults
INSERT INTO students_backup SELECT * FROM students;

CREATE TABLE cse_students AS
SELECT id, name_en, cgpa FROM students WHERE department = 'CSE';

LIKE … INCLUDING ALL copies defaults, constraints, indexes and comments; CREATE TABLE AS copies only the data and column types.

Temporary and unlogged tables

CREATE TEMP TABLE tmp_top AS
SELECT * FROM students ORDER BY cgpa DESC LIMIT 10;

CREATE UNLOGGED TABLE import_staging (raw text);

A temp table lives for the session. An unlogged table skips the write-ahead log, which makes bulk loading much faster, at the cost of being emptied after a crash — ideal for staging and scratch data.

Auto-updating updated_at

Postgres has no ON UPDATE CURRENT_TIMESTAMP, so use a trigger:

CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_students_updated
BEFORE UPDATE ON students
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Creating Tables