PostgreSQL-- the Secret of sequence and self-increasing id
I. the essence of self-increasing id serial1.2 and serial of PostgreSQL
When we create tables, we often need to use self-increment id, which is often written like this
Create table test (id serial, # uses serial to create a self-increasing id name varchar (64) not null) creates a self-increasing id through serial, creates a self-increasing sequence belonging to this table, and sets the id field to not null
The serial and bigserial types are not real types, but just a conceptual convenience to set a unique identity in the table. In essence, a sequence generator is created, and then the default value of the self-increasing field is set to the nextval of the sequence generator.
CREATE TABLE tablename (colname SERIAL); equivalent to CREATE SEQUENCE tablename_colname_seq;CREATE TABLE tablename (colname integer DEFAULT nextval ('tablename_colname_seq') NOT NULL) 1.2 、 Postgres=#\ d + test5_id_seq Sequence "public.test5_id_seq" Column | Type | Value | Storage-+-sequence_name | name | test5_id_seq | plain last _ value | bigint | 1 | plain start_value | bigint | 1 | start value of plain # sequence increment_by | bigint | 1 | plain # value-added max_value each time | bigint | 9223372036854775807 | plain # maximum min_value | bigint | 1 | plain # minimum cache_value | | bigint | 1 | plain log_cnt | bigint | 0 | plain is_cycled | boolean | f | whether plain # circulates is_called | boolean | f | plainOwned by: public.test5.id |
Because self-increment is essentially taking the next value of the sequence generator, multiple tables can share a sequence generator, and the unique value of multiple tables can be realized. of course, you can also set whether the sequence can be cycled to realize the id loop.
For example:
Sequence 2.1 create sequence CREATE SEQUENCE name increment by 1 maxvalue 10 minvalue 1 start 1 cycle2.2, sequence operation next value: nextval (regclass) current value: currval (regclass) setting value: setval (regclass) 2.3, bind sequence to table create table test_seq (id int not null default nextval ('my_seq1'), name varchar (64))
