CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- 插入時自動增長
INSERT INTO users(name) VALUES ('Alice');
-- id = 1

INSERT INTO users(name) VALUES ('Bob');
-- id = 2

CREATE TABLE products (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT,
    price NUMERIC
);

-- 插入
INSERT INTO products(name, price) VALUES ('iPhone', 999);
-- id = 1

-- 允許手動指定 ID
CREATE TABLE logs (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY,
    message TEXT
);

INSERT INTO logs(id, message) VALUES (100, 'Custom ID');

-- 查看自動創(chuàng)建的序列
SELECT pg_get_serial_sequence('users', 'id');
-- 返回: public.users_id_seq

-- 直接操作序列
SELECT nextval('users_id_seq');  -- 獲取下一個值
SELECT setval('users_id_seq', 100);  -- 設(shè)置當(dāng)前值
SELECT currval('users_id_seq');  -- 獲取當(dāng)前值（事務(wù)內(nèi)）

CREATE SEQUENCE my_seq
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999
    CACHE 10;

CREATE TABLE orders (
    id INT PRIMARY KEY DEFAULT nextval('my_seq'),
    total NUMERIC
);

-- SERIAL 表
TRUNCATE TABLE users RESTART IDENTITY;

-- 或手動
SELECT setval(pg_get_serial_sequence('users', 'id'), 1, false);

-- 重新編號（慎用！影響外鍵）
WITH ranked AS (
    SELECT id, row_number() OVER (ORDER BY id) AS rn
    FROM users
)
UPDATE users u
SET id = r.rn
FROM ranked r
WHERE u.id = r.id;

SELECT setval('users_id_seq', 10000);
-- 下一個 INSERT 從 10001 開始

-- 創(chuàng)建高性能序列
CREATE SEQUENCE fast_seq CACHE 100;

-- 100 個值預(yù)分配，減少鎖競爭

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id)
);
-- 沒問題

-- 導(dǎo)出時包含序列
pg_dump -Fc -f backup.dump dbname

-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY

-- 轉(zhuǎn)為 PostgreSQL
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- 查看所有序列
SELECT * FROM information_schema.sequences;

-- 查看系統(tǒng)序列
SELECT schemaname, sequencename, last_value
FROM pg_sequences;

-- 修改序列
ALTER SEQUENCE users_id_seq
    INCREMENT BY 2
    MINVALUE 1
    MAXVALUE 1000000
    RESTART WITH 1;

INSERT INTO products(id, name)
VALUES (999, 'Legacy Product')
OVERRIDING SYSTEM VALUE;

CREATE TABLE customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 觸發(fā)器自動更新 updated_at
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_customers_updated_at
    BEFORE UPDATE ON customers
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

-- 允許導(dǎo)入舊數(shù)據(jù)
INSERT INTO customers(id, email, name)
VALUES (1001, 'old@example.com', 'Legacy User')
OVERRIDING SYSTEM VALUE;

TRUNCATE TABLE customers, orders, products RESTART IDENTITY CASCADE;

-- 插入并返回 ID（推薦）
INSERT INTO users(name) VALUES ('Tom')
RETURNING id;