-- Created on savesnippets.com · https://savesnippets.com/hvPosuXUnzDkhd
-- PostgreSQL — GENERATED ALWAYS AS ... STORED
CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    qty        INT NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL,
    total      NUMERIC(10,2) GENERATED ALWAYS AS (qty * unit_price) STORED
);

INSERT INTO orders (qty, unit_price) VALUES (3, 9.99);
SELECT total FROM orders;       -- 29.97

-- Search-friendly normalized column for case-insensitive lookups
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL,
    email_lower TEXT GENERATED ALWAYS AS (LOWER(email)) STORED
);
CREATE UNIQUE INDEX uq_users_email_lower ON users (email_lower);

-- MySQL — can be VIRTUAL (not stored, computed on read) or STORED
CREATE TABLE orders (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    qty        INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total      DECIMAL(10,2) AS (qty * unit_price) VIRTUAL,
    INDEX idx_total (total)              -- VIRTUAL columns CAN be indexed
);