Cú pháp PostgreSQL cơ bản

-- Postgres recommend cú pháp IDENTITY chuẩn ANSI hơn SERIAL
CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(18, 2) NOT NULL CHECK (price >= 0)
);

-- Array
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]                            -- array of TEXT
);
INSERT INTO posts (title, tags) VALUES ('Hello', ARRAY['vue', 'nuxt']);
SELECT * FROM posts WHERE 'vue' = ANY(tags);

-- JSON / JSONB (JSONB nén nhị phân, query nhanh, index được)
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);
INSERT INTO events (payload)
VALUES ('{"type": "click", "user": "alice", "tags": ["promo"]}');

SELECT payload->>'type' AS event_type,
       payload->'user' AS user_json
FROM events;

-- UUID — Postgres 18 có uuidv7() built-in
CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT uuidv7(),    -- 18+
    -- DEFAULT gen_random_uuid() cho version < 18
    email TEXT UNIQUE
);

-- INET / CIDR
CREATE TABLE logs (
    ip INET,
    subnet CIDR
);

-- Range type
CREATE TABLE reservations (
    id BIGSERIAL PRIMARY KEY,
    room_id INT,
    during TSRANGE NOT NULL,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)  -- chống overlap!
);

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    age INT CHECK (age BETWEEN 0 AND 150),
    status TEXT NOT NULL DEFAULT 'active',
    country_id INT REFERENCES countries(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Add column
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- Drop column
ALTER TABLE users DROP COLUMN avatar_url;

-- Rename
ALTER TABLE users RENAME COLUMN status TO state;

-- Change type
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- Drop table
DROP TABLE IF EXISTS users CASCADE;

-- VIRTUAL (Postgres 18 default) — tính khi đọc
ALTER TABLE products
    ADD COLUMN price_with_tax NUMERIC(18, 2)
    GENERATED ALWAYS AS (price * 1.1) VIRTUAL;

-- STORED — lưu vật lý, index được
ALTER TABLE products
    ADD COLUMN price_with_tax_stored NUMERIC(18, 2)
    GENERATED ALWAYS AS (price * 1.1) STORED;

INSERT INTO users (email, age)
VALUES ('alice@x.com', 25)
RETURNING id, email, created_at;

-- 18+ — UPDATE log diff
UPDATE users
SET age = age + 1
WHERE id = 1
RETURNING OLD.age AS old_age, NEW.age AS new_age;

INSERT INTO products (name, price) VALUES
('Laptop', 32000000),
('Mouse', 800000),
('Keyboard', 2200000);

-- INSERT, nếu trùng email thì UPDATE
INSERT INTO users (email, age)
VALUES ('alice@x.com', 26)
ON CONFLICT (email) DO UPDATE
    SET age = EXCLUDED.age,
        updated_at = NOW();

-- Hoặc DO NOTHING
INSERT INTO users (email, age)
VALUES ('alice@x.com', 26)
ON CONFLICT DO NOTHING;

UPDATE products
SET price = price * 1.1, updated_at = NOW()
WHERE category_id = 1;

-- UPDATE từ JOIN (Postgres dùng FROM)
UPDATE products p
SET category_name = c.name
FROM categories c
WHERE c.id = p.category_id;

DELETE FROM products WHERE is_active = false;

-- DELETE từ JOIN (USING)
DELETE FROM orders o
USING users u
WHERE u.id = o.user_id AND u.banned = true;

TRUNCATE TABLE logs;
TRUNCATE TABLE logs RESTART IDENTITY;            -- reset sequence
TRUNCATE TABLE logs RESTART IDENTITY CASCADE;    -- + truncate bảng FK reference

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE email IS NULL;

-- COALESCE — first non-null
SELECT name, COALESCE(phone, email, 'no-contact') FROM users;

-- NULLIF — null nếu 2 giá trị bằng
SELECT amount / NULLIF(divisor, 0) FROM calc;

SELECT
    LENGTH('Hello'),                       -- 5
    LOWER('ABC'), UPPER('abc'),
    SUBSTRING('Hello' FROM 2 FOR 3),       -- 'ell'
    LEFT('Hello', 3), RIGHT('Hello', 2),
    TRIM('  abc  '),
    REPLACE('abc', 'b', 'X'),
    CONCAT('Hello', ' ', 'World'),
    'Hello' || ' ' || 'World',             -- || operator
    STRING_AGG(name, ', ' ORDER BY name);  -- aggregate

SELECT
    NOW(),                                  -- TIMESTAMPTZ
    CURRENT_DATE, CURRENT_TIME,
    AGE(TIMESTAMP '1990-01-01'),            -- interval
    NOW() + INTERVAL '7 days',
    NOW() - INTERVAL '1 month',
    EXTRACT(YEAR FROM NOW()),
    DATE_TRUNC('month', NOW()),             -- truncate đến month
    TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI');

-- Access
SELECT
    payload->'user' AS user_json,             -- trả JSONB
    payload->>'user' AS user_text,            -- trả TEXT
    payload->'tags'->0 AS first_tag,          -- index array
    payload->'tags'->>0 AS first_tag_text;

-- Existence
SELECT * FROM events WHERE payload ? 'type';                  -- key tồn tại
SELECT * FROM events WHERE payload ?| ARRAY['type', 'name'];  -- bất kỳ key
SELECT * FROM events WHERE payload ?& ARRAY['type', 'name'];  -- tất cả key

-- Containment (rất hay dùng + index GIN tốt)
SELECT * FROM events WHERE payload @> '{"type": "click"}';

-- Modify (18+ có thêm nhiều function)
UPDATE events
SET payload = jsonb_set(payload, '{processed}', 'true')
WHERE id = 1;

-- Path
SELECT jsonb_path_query(payload, '$.tags[*]') FROM events;

SELECT
    ARRAY[1, 2, 3, 4],
    ARRAY_LENGTH(ARRAY[1, 2, 3], 1),       -- 3
    ARRAY[1, 2] || ARRAY[3, 4],            -- {1,2,3,4}
    1 = ANY(ARRAY[1, 2, 3]),               -- TRUE
    ARRAY_AGG(name ORDER BY name);

-- Array trong WHERE
SELECT * FROM posts WHERE tags && ARRAY['vue', 'react'];   -- overlap
SELECT * FROM posts WHERE tags @> ARRAY['vue'];            -- contains

DO $$
DECLARE
    user_count INT;
BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
    RAISE NOTICE 'Total users: %', user_count;
END $$;

CREATE OR REPLACE FUNCTION full_name(first TEXT, last TEXT)
RETURNS TEXT
LANGUAGE SQL IMMUTABLE
AS $$
    SELECT first || ' ' || last;
$$;

SELECT full_name('Alice', 'Nguyen');

CREATE OR REPLACE FUNCTION get_user_tier(uid BIGINT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    spent NUMERIC;
BEGIN
    SELECT COALESCE(SUM(total), 0) INTO spent
    FROM orders WHERE user_id = uid;
    
    IF spent >= 100000000 THEN RETURN 'gold';
    ELSIF spent >= 10000000 THEN RETURN 'silver';
    ELSE RETURN 'bronze';
    END IF;
END $$;

CREATE OR REPLACE PROCEDURE transfer(from_id BIGINT, to_id BIGINT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    COMMIT;
END $$;

CALL transfer(1, 2, 1000000);

-- PG 19 — lấy row nếu tồn tại, insert nếu chưa, luôn trả về kết quả
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO SELECT
RETURNING *;

-- Pattern cũ (PG 18 trở về trước) — vẫn chạy nhưng tạo dead tuple không cần thiết
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email  -- dirty no-op
RETURNING *;

-- PG 19 — sạch hơn, nhanh hơn ~4x
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO SELECT
RETURNING *;

-- PG 19 — tự động GROUP BY mọi non-aggregate column trong SELECT
SELECT department, role, count(*)
FROM employees
GROUP BY ALL;

-- Thêm cột location mà không cần sửa GROUP BY
SELECT department, role, location, count(*)
FROM employees
GROUP BY ALL;  -- tự hiểu là GROUP BY department, role, location

-- Xóa cột cũng vậy — GROUP BY ALL tự điều chỉnh
SELECT department, count(*)
FROM employees
GROUP BY ALL;  -- tự hiểu là GROUP BY department

-- Bảng giá sản phẩm theo thời gian (temporal table)
CREATE TABLE product_prices (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    price NUMERIC(18, 2) NOT NULL,
    valid_range TSRANGE NOT NULL,
    EXCLUDE USING GIST (product_id WITH =, valid_range WITH &&)
);

-- Dữ liệu ban đầu: giá 29.99 áp dụng cả năm 2025
INSERT INTO product_prices (product_id, price, valid_range)
VALUES (1, 29.99, '[2025-01-01, 2025-12-31)');

-- PG 19 — cập nhật giá cho Q3/2025, Postgres tự chia row
UPDATE product_prices
FOR PORTION OF valid_range FROM '2025-07-01' TO '2025-10-01'
SET price = 34.99
WHERE product_id = 1;

-- PG 19 — forward-fill: điền giá trị sensor gần nhất không NULL
SELECT
    sensor_id,
    ts,
    reading,
    last_value(reading) IGNORE NULLS OVER (
        PARTITION BY sensor_id ORDER BY ts
    ) AS last_known_reading
FROM sensor_data;