Sequence usage in Oracle, DB2, and PostgreSQL

This article will share with you a summary of the Sequence usage of Oracle, DB2, and PostgreSQL. I hope this method will be helpful to you.

Sequence is a Sequence of numbers automatically added by the database system according to certain rules. This sequence is generally used as the proxy primary key (because it will not be repeated) and has no other meaning.

Sequence is a feature of the database system. Some databases have Sequence and some do not. For example, Sequence exists in Oracle, DB2, and PostgreSQL databases, and Sequence exists in MySQL, SQL Server, Sybase, and other databases.

According to my personal understanding, Sequence is a table in the data that stores the differential Sequence. This table is controlled by the database system, at any time, the database system can obtain the next record in the table based on the current number of records plus the step size. This table has no practical significance and is often used for primary keys, which is very good, haha, but very depressing database vendors don't get into one pot-each has its own set of Sequence definitions and operations. Here I will compare and summarize the Sequence definitions and operations of the three common databases for future reference.

1. Define Sequence

Define a seq_test with a minimum value of 10000 and a maximum value of 99999999999999999. Starting from 20000, the incremental step is 1 and the cache is 20.

Oracle definition method:

The Code is as follows:	Copy code
Create sequence seq_test Min value 10000 Max value 99999999999999999 Start with 20000 Increment by 1 CACHE 20 CYCLE ORDER;

DB2 statement:

The Code is as follows:	Copy code
Create sequence seq_test AS BIGINT Start with 20000 Increment by 1 Min value 10000 Max value 99999999999999999 CYCLE CACHE 20 ORDER;

PostgreSQL statement:

The Code is as follows:	Copy code
Create sequence seq_test Increment by 1 Min value 10000 Max value 99999999999999999 START 1, 20000 CACHE 20 CYCLE;

Ii. Sequence values
In Oracle, DB2, and PostgreSQL, Sequence has different value methods, but both the current value and the next value are obtained. The value code is as follows:

-- Oracle

The Code is as follows:	Copy code
SELECT seq_test.CURRVAL from dual; -- Current Value SELECT seq_test.NEXTVAL from dual; -- next value

-- DB2

The Code is as follows:	Copy code
Values prevval for "seq_test"; -- Current Value Values nextval for "seq_test"; -- next value -- Or Values previous value for "seq_test"; -- Current VALUE Values next value for "seq_test"; -- NEXT VALUE

-- PostgreSQL

The Code is as follows:	Copy code
Select currval (seq_test); -- Current Value Select nextval (seq_test); -- next value

Iii. Differences and relationships between Sequence and indentity

Sequence and indentity have similar basic functions. Can generate auto-incrementing sequence. Sequence is an object in the database system. It can be used in the whole database and has no relationship with the table. indentity only specifies a column in the table and its scope is the table.