Sequence usage of Oracle, DB2 and PostgreSQL

Source: Internet
Author: User
Tags db2 postgresql

Sequence is a sequence of numbers that the database system automatically increases according to certain rules. This sequence is generally used as the surrogate primary key (because it does not repeat) and has no other meaning.

Sequence is the characteristic of database system, some databases have sequence, some have not. For example, Oracle, DB2, PostgreSQL databases have sequence,mysql, SQL Server, Sybase, and other databases without sequence.

According to my personal understanding, sequence is a special stored arithmetic progression table, which is controlled by the database system, at any time the database system can be based on the current number of records and the size of the step to get to the table the next record should be how much, this table has no practical significance, often used for the key to use, very good, Oh, but very depressed each database manufacturer urine less than a pot-each have a set of sequence definition and operation. Here I compare and summarize the definition and sequence of the common three kinds of databases for future viewing.

I. Definition of sequence

Defines a seq_test, with a minimum value of 10000, a maximum of 99999999999999999, starting at 20000, an increment of step 1, and a cyclic sort sequence cached for 20.

How Oracle is defined:

The code is as follows Copy Code

CREATE SEQUENCE Seq_test
MinValue 10000
MAXVALUE 99999999999999999
START with 20000
INCREMENT by 1
CACHE 20
CYCLE
order;

DB2 's writing:

The code is as follows Copy Code
CREATE SEQUENCE Seq_test
As BIGINT
START with 20000
INCREMENT by 1
MinValue 10000
MAXVALUE 99999999999999999
CYCLE
CACHE 20
order;

PostgreSQL's writing:

The code is as follows Copy Code
CREATE SEQUENCE Seq_test
INCREMENT by 1
MinValue 10000
MAXVALUE 99999999999999999
START 20000
CACHE 20
CYCLE;

Second, the value of sequence
In Oracle, DB2, and PostgreSQL, sequence values are different, but both have current and next values. Take the value code 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

The difference and relation between sequence and indentity

The basic function of sequence and indentity is similar. Can generate a self-increasing number sequence. Sequence is an object in the database system that can be used throughout the database and has nothing to do with the table; indentity is only specified on a column in the table, and the scope is this 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.