Three ways to achieve self-increase in PostgreSQL

There are three ways to implement self-increment fields in PG:

Identity column (IDENTITY)
Sequence (SEQUENCE)
Serial Type

The self-increase field is mainly used to realize the self-increase primary key or generate a unique version number.

1.1 Identification column (IDENTITY)

Identification columns are recommended to implement self-increase.

Example

CREATE TABLE t_user (
    user_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY ,
    user_name varchar(50) NOT NULL UNIQUE
)
INSERT INTO t_user (user_name) value ('tony')

illustrate:

Create a name calledt_usertable CREATE TABLE t_user ()
Fieldsuser_idis integer type user_id integer

Defined as an identification column and always generated according to the identification column (generated by the system) GENERATED ALWAYS AS IDENTITY

Defined as primary key PRIMARY KEY
Fieldsuser_namevarchar type

Constraints it not empty and unique NOT NULL UNIQUE
Insert a data INSERT INTO t_user (user_name) value (‘tony’)

becauseuser_idIt is an auto-increment primary key implemented using the identification column. Assigned by the system, so only the specified value must be specified when inserting.user_nameThe value is

principle

After defining the identity column, the system background will automatically create and maintain a name calledtablename_column_seqSequences Generate self-incremental data based on this sequence

-- SEQUENCE: public.t_user_user_id_seq

-- DROP SEQUENCE IF EXISTS public.t_user_user_id_seq;

CREATE SEQUENCE IF NOT EXISTS public.t_user_user_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1;

ALTER SEQUENCE public.t_user_user_id_seq
    OWNER TO postgres;

The following table is deleted and the sequence is deleted. The following table and sequence are related.DROP TABLE t_user

1.2 Sequence (SEQUENCE)

Implementing self-increment fields by defining sequences requires defining a sequence before creating a table.

Example

Define the sequence first

CREATE SEQUENCE IF NOT EXISTS public.t_user_user_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1
    NO CYCLE

illustrate:

Create a sequence named table name_self-increasing field name_seqCREATE SEQUENCE IF NOT EXISTS public.t_user_user_id_seq
parameter:
- INCREMENT 1: How many times the sequence increases each time
- START 1: Starting from 1
- MINVALUE 1: The minimum value of the sequence
- MAXVALUE 2147483647: The maximum value of the sequence
- CACHE 1: Cache sequence, but the cache will be lost after the server restarts
- NO CYCLE / CYCLE: Whether to cycle? Whether the sequence grows to the maximum value? Whether it starts again?NO CYCLEIt means not to start again

Create a table again

CREATE TABLE t_user (
    user_id integer DEFAULT nextval('t_user_user_id_seq') PRIMARY KEY ,
    user_name varchar(50) NOT NULL UNIQUE
)

illustrate:

DEFAULT nextval('t_user_user_id_seq'): The default value ist_user_user_id_seqThe next value of the sequence

nextval('sequence') - Get the next value of the sequence

currval('sequence') - Get the current value of the sequence

The sequence will not be deleted and will remain

Delete statement:DROP SEQUENCE IF EXISTS public.t_user_user_id_seq;

1.3 Serial

Example

CREATE TABLE t_user (
    user_id serial PRIMARY KEY ,
    user_name varchar(50) NOT NULL UNIQUE
)

principle

The system automatically generates a sequence associated with the table

CREATE TABLE IF NOT EXISTS public.t_user
(
    user_id integer NOT NULL DEFAULT nextval('t_user_user_id_seq'::regclass),
    user_name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT t_user_pkey PRIMARY KEY (user_id),
    CONSTRAINT t_user_user_name_key UNIQUE (user_name)
)

The sequence will be deleted together when the table is deleted

Attachment: Modify the primary key of an existing table to increase

Already have table structure

CREATE TABLE student
(
    id   int4 PRIMARY KEY,
    name VARCHAR
);

Create sequence: 1 in the code means that this primary key starts from 1. Note: If the table in the project already has data, then the number after START must be larger or the same as the maximum value of the primary key field in the database.

CREATE SEQUENCE student_id_seq START 1;

Modify the default value of primary key

ALTER TABLE student
    ALTER COLUMN id SET DEFAULT nextval('student_id_seq'::regclass);

Summarize

This is the end of this article about the three ways to achieve self-increase in PostgreSQL. For more related content on PostgreSQL implementation, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!