Data Types and Constraints

CREATE TYPE demo_mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE demo_types (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    qty       integer,
    price     numeric(10,2),
    label     text,
    is_active boolean,
    created   timestamptz,
    ref       uuid,
    attrs     jsonb,
    mood      demo_mood
);

INSERT INTO demo_types (qty, price, label, is_active, created, ref, attrs, mood)
VALUES (3, 19.99, 'widget', true, '2026-01-15 10:30:00+00',
        '123e4567-e89b-12d3-a456-426614174000', '{"color": "red", "tags": ["a", "b"]}', 'happy');

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

SELECT attrs ->> 'color' AS color, attrs -> 'tags' ->> 0 AS first_tag, mood > 'ok' AS happier_than_ok
FROM demo_types;

SELECT 0.1::double precision + 0.2::double precision AS float_sum,
       (0.1::double precision + 0.2::double precision) = 0.3::double precision AS float_equal,
       0.1::numeric + 0.2::numeric AS numeric_sum,
       (0.1::numeric + 0.2::numeric) = 0.3::numeric AS numeric_equal;

SELECT 2147483647::integer AS max_int, 2147483647::bigint + 1 AS bigint_ok;

SELECT 2147483647::integer + 1 AS overflow;

SELECT length('abc'::text) AS text_len,
       length('abc'::varchar(10)) AS varchar_len,
       length('abc'::char(10)) AS char_len,
       octet_length('abc'::char(10)) AS char_bytes;

CREATE TABLE demo_codes (code varchar(5));
INSERT INTO demo_codes VALUES ('toolongvalue');

CREATE TABLE demo_events (
    id        int PRIMARY KEY,
    local_ts  timestamp,
    abs_ts    timestamptz
);

INSERT INTO demo_events VALUES (1, '2026-03-01 12:00:00', '2026-03-01 12:00:00+00');

SET TIME ZONE 'UTC';
SELECT local_ts, abs_ts FROM demo_events;

SET TIME ZONE 'Asia/Kolkata';
SELECT local_ts, abs_ts FROM demo_events;

RESET TIME ZONE;

CREATE TABLE demo_users (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email  text NOT NULL UNIQUE,
    uid    uuid NOT NULL DEFAULT gen_random_uuid()
);

INSERT INTO demo_users (email) VALUES ('a@example.com'), ('b@example.com');

SELECT id, email, length(uid::text) AS uid_chars, pg_column_size(uid) AS uid_bytes
FROM demo_users
ORDER BY id;

INSERT INTO demo_users (id, email) VALUES (100, 'c@example.com');

CREATE TABLE demo_products (
    id       int PRIMARY KEY,
    name     text NOT NULL,
    price    numeric(8,2) NOT NULL CHECK (price >= 0),
    stock    int NOT NULL DEFAULT 0,
    status   text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'retired')),
    CONSTRAINT stock_not_negative CHECK (stock >= 0)
);

INSERT INTO demo_products (id, name, price) VALUES (1, 'Pen', 1.50);
SELECT * FROM demo_products;

INSERT INTO demo_products (id, name, price) VALUES (2, NULL, 2.00);

INSERT INTO demo_products (id, name, price) VALUES (3, 'Ink', -5);

INSERT INTO demo_products (id, name, price) VALUES (1, 'Duplicate', 3.00);

INSERT INTO demo_products (id, name, price, status) VALUES (4, 'Cap', 1.00, 'lost');

CREATE TABLE demo_accounts (
    id    int PRIMARY KEY,
    phone text UNIQUE,
    tax   text,
    UNIQUE NULLS NOT DISTINCT (tax)
);

INSERT INTO demo_accounts VALUES (1, NULL, NULL), (2, NULL, 'T-1');
SELECT * FROM demo_accounts ORDER BY id;

INSERT INTO demo_accounts VALUES (3, NULL, NULL);

CREATE TABLE demo_authors (id int PRIMARY KEY, name text NOT NULL);

CREATE TABLE demo_books_cascade (
    id int PRIMARY KEY, title text,
    author_id int REFERENCES demo_authors(id) ON DELETE CASCADE
);
CREATE TABLE demo_books_restrict (
    id int PRIMARY KEY, title text,
    author_id int REFERENCES demo_authors(id) ON DELETE RESTRICT
);
CREATE TABLE demo_books_setnull (
    id int PRIMARY KEY, title text,
    author_id int REFERENCES demo_authors(id) ON DELETE SET NULL
);

INSERT INTO demo_authors VALUES (1, 'Asha'), (2, 'Bala'), (3, 'Chitra');
INSERT INTO demo_books_cascade VALUES (1, 'C1', 1);
INSERT INTO demo_books_restrict VALUES (1, 'R1', 2);
INSERT INTO demo_books_setnull VALUES (1, 'S1', 3);

INSERT INTO demo_books_cascade VALUES (2, 'Orphan', 99);

DELETE FROM demo_authors WHERE id = 2;

DELETE FROM demo_authors WHERE id = 1;
DELETE FROM demo_authors WHERE id = 3;

SELECT count(*) AS cascade_books FROM demo_books_cascade;
SELECT id, title, author_id FROM demo_books_setnull;

CREATE TABLE demo_students (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE demo_courses  (id int PRIMARY KEY, title text NOT NULL);
CREATE TABLE demo_enrollments (
    student_id int REFERENCES demo_students(id) ON DELETE CASCADE,
    course_id  int REFERENCES demo_courses(id)  ON DELETE CASCADE,
    enrolled_on date NOT NULL DEFAULT '2026-01-01',
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO demo_students VALUES (1, 'Isha'), (2, 'Jay');
INSERT INTO demo_courses  VALUES (10, 'SQL'), (20, 'Python');
INSERT INTO demo_enrollments (student_id, course_id) VALUES (1, 10), (1, 20), (2, 10);

SELECT s.name, c.title
FROM demo_enrollments e
JOIN demo_students s ON s.id = e.student_id
JOIN demo_courses c ON c.id = e.course_id
ORDER BY s.name, c.title;

INSERT INTO demo_enrollments (student_id, course_id) VALUES (1, 10);

CREATE TABLE demo_categories (
    id        int PRIMARY KEY,
    name      text NOT NULL,
    parent_id int REFERENCES demo_categories(id) ON DELETE SET NULL
);

INSERT INTO demo_categories VALUES
    (1, 'Electronics', NULL),
    (2, 'Phones', 1),
    (3, 'Laptops', 1),
    (4, 'Android', 2);

SELECT c.name AS category, p.name AS parent
FROM demo_categories c
LEFT JOIN demo_categories p ON p.id = c.parent_id
ORDER BY c.id;

ALTER TABLE demo_products ADD COLUMN sku text;
ALTER TABLE demo_products ADD CONSTRAINT demo_products_sku_uq UNIQUE (sku);
ALTER TABLE demo_products ALTER COLUMN stock SET DEFAULT 10;
ALTER TABLE demo_products RENAME COLUMN name TO title;
ALTER TABLE demo_products ALTER COLUMN price TYPE numeric(10,2);

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

ALTER TABLE demo_products ADD CONSTRAINT price_min CHECK (price > 5);

DROP TABLE demo_authors;

DROP TABLE demo_authors CASCADE;
DROP TABLE IF EXISTS demo_books_cascade, demo_books_restrict, demo_books_setnull,
    demo_enrollments, demo_students, demo_courses, demo_categories,
    demo_accounts, demo_users, demo_codes, demo_products, demo_events, demo_types;
DROP TYPE demo_mood;