#PostgreSQL AUTO INCREMENT

-- Creates sequence and auto-increment column
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Equivalent to:
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
    id INTEGER NOT NULL DEFAULT nextval('users_id_seq') PRIMARY KEY
);

-- For large tables
CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    message TEXT
);

-- For small ranges
CREATE TABLE small_table (
    id SMALLSERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- Standard SQL identity column
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Generated by default
CREATE TABLE orders (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id INTEGER NOT NULL
);

-- Get next value
SELECT nextval('users_id_seq');

-- Get current value
SELECT currval('users_id_seq');

-- Set value
SELECT setval('users_id_seq', 1000);

-- Get last value from sequence
SELECT lastval();

-- Alter sequence
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;