Summary of Sequence in Oracle, DB2, and PostgreSQL

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:
Create sequence seq_test
Min value 10000
Max value 99999999999999999
Start with 20000
Increment by 1
Cache 20
Cycle
Order;

DB2 statement:
Create sequence seq_test
As bigint
Start with 20000
Increment by 1
Min value 10000
Max value 99999999999999999
Cycle
Cache 20
Order;

PostgreSQL statement:
Create sequence seq_test
Increment by 1
Min value 10000
Max value 99999999999999999
Start 1, 20000
Cache 20
Cycle;

Ii. Reference parameters for Sequence values of Oracle, DB2, and PostgreSQL databases: currval and nextval, indicating the current value and the next value respectively. The values of nextval are obtained from the Sequence of the three databases respectively.

In Oracle: seq_test.nextval
Example: select seq_test.nextval from dual;

In DB2: nextval for SEQ_TOPICMS
Example: values nextval for seq_test;

In PostgreSQL: nextval (seq_test)
Example: select nextval (seq_test );

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.