Sequence usage in Oracle, DB2, and PostgreSQL

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.