#PostgreSQL 自动增长

-- 创建序列和自动增长列
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 等效于：
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
    id INTEGER NOT NULL DEFAULT nextval('users_id_seq') PRIMARY KEY
);

-- 适用于大型表
CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    message TEXT
);

-- 适用于小范围
CREATE TABLE small_table (
    id SMALLSERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- 标准 SQL 标识列
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 默认生成
CREATE TABLE orders (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id INTEGER NOT NULL
);

-- 获取下一个值
SELECT nextval('users_id_seq');

-- 获取当前值
SELECT currval('users_id_seq');

-- 设置值
SELECT setval('users_id_seq', 1000);

-- 获取序列的最后一个值
SELECT lastval();

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