Summary of 2 methods of establishing self-increment primary keys on PostgreSQL

1. Preparation

First create a table:

create table test(
	id int primary key,
	age int
)

2. The first method---create the sequence to achieve the effect of self-increase

1. Create a sequence

In pgsql, there is something called self-increase, which is very similar to the constraints in mysql. Create a self-incremental sequence. Every time you need to obtain the self-incremental primary key, just call this sequence. Syntax for establishing a sequence of autoincremental primary keys:

CREATE SEQUENCE 
test_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START WITH 1
CACHE 1;

INCREMENT BY: The step length of each sequence increases (or decreases)
MINVALUE: The minimum value of the sequence, NO MINVALUE indicates that there is no minimum value
MAXVALUE: The maximum value of the sequence, NO MAXVALUE indicates that there is no maximum value
START WITH: What sequence value starts with
CYCLE: Whether the sequence is used in a loop
OWNED BY: You can directly specify the fields of a table, or you can not specify them.

When the primary key needs to be increased, call itnextval(sequence name)That's it:

test=#  select nextval('test_id_seq');
 nextval
 ---------
1

2. Sequence related methods

The related methods are as follows (regclass represents the name of the sequence):

function	Return type	describe
currval( regclass )	bigint	Get the value of the specified sequence after the last time it was used by netxval. If you do not use nextval and use currval directly, you will have an error.
lastval()	bigint	Returns the value of any sequence that was last obtained with nextval
nextval( regclass )	bigint	Increment the sequence and return the new value
setval( regclass,bigint )	bigint	Set the current value of the sequence
setval( regclass,bigint ,boolean )	bigint	Set the current value of the sequence and the is_called flag. If true, it will take effect immediately. If false, it will take effect only after calling nextval once.

Just find the specific function if you need any value.

3. Insert data

Assuming that you want to insert data into the test table now and the id is incremented by itself, then:

insert into test values(
	nextval('test_id_seq') , 1 
)

However, this kind of operation requires writing the sequence name every time, which is very annoying. But there is a way to solve it. Look at the following:

4. Set the default value

You can set the default value of the id field to nextval('test_id_seq'), so that you don't need to write it every time you insert the data.

alter table 
	test -- Table name
alter column 
	id -- List name 
set default nextval(
	'test_id_seq'  -- 序List name
);

Then insert data into the test table, just write it like this:

insert into test(age) values(12)

Or directly:

insert into test values(12)

3. The second method-----Use SERIAL

1. Table creation statement

When using serial, the table creation statement needs to be changed:

create table test(
	id serial primary key,
	age int
)

At this time, the name is automatically createdTable name_field name_seqand MAXVALUE=9223372036854775807, the remaining values are 1.

For example, after the above table is created, the name is automatically createdtest_id_seqsequence.

2. Insert data

Insert the data, just write it like this:

insert into test(age) values(12)

Or directly:

insert into test values(12)

3. How to get the current value of a sequence

Use the following sql to get the current value of the sequence:

select currval('test_id_seq')

Summarize

This is the end of this article about two methods of establishing self-increasing primary keys for PostgreSQL. For more related content on establishing self-increasing primary keys, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!