一文全面了解PostgreSQL的序列（sequence）

CREATE SEQUENCE oldgeek_test_seq;

CREATE SEQUENCE oldgeek_test_seq
    INCREMENT BY 1
    MAXVALUE 99999999
    START WITH 1
    NO CYCLE;

drop sequence oldgeek_test_seq;

CREATE TABLE oldgeek_test (
    id integer NOT NULL,
    user_name varchar(20),
    PRIMARY KEY(id)
);

CREATE TABLE oldgeek_test(
  id SERIAL NOT NULL,
  user_name varchar(20),
  PRIMARY KEY(id)
);

CREATE SEQUENCE oldgeek_test_seq
    INCREMENT BY 1
    MAXVALUE 99999999
    START WITH 1
    NO CYCLE;
CREATE TABLE oldgeek_test (
    id integer DEFAULT nextval('oldgeek_test_seq') NOT NULL,
    user_name varchar(20),
    PRIMARY KEY(id)
);

INSERT INTO oldgeek_test VALUES (nextval('oldgeek_test_seq'), 'oldgeek');

INSERT INTO oldgeek_test(user_name) VALUES ('oldgeek');

SELECT * FROM oldgeek_test;

select nextval('oldgeek_test_seq');

select nextval('oldgeek_test_id_seq');

select currval('oldgeek_test_seq');

select setval('oldgeek_test_seq', 1);
select nextval('oldgeek_test_seq');

Unique violation: 7 ERROR: duplicate key value violates unique constraint "oldgeek_test_pkey"↵DETAIL: Key (id)=(33) already exists.

select MAX(id) FROM oldgeek_test

select nextval('oldgeek_test_seq');

select setval('oldgeek_test_seq', (select max(id) from oldgeek_test));

select nextval('oldgeek_test_seq');