First, Introduction
A Sequence object (also called a sequence builder) is a special Single-line table created with the Create SEQUENCE. A sequence object is typically used to generate a unique identifier for a row or table.
Second, create a sequence
Method One: Specify the field type as serial type directly in the table
david=# CREATE TABLE Tbl_xulie (
# ID serial,
David (# Name text);
Notice:create TABLE would CREATE implicit sequence "Tbl_xulie_id_seq" for serial column "Tbl_xulie.id"
create Table
david=#
Method Two: First create the sequence name, and then in the new Table column properties to specify the sequence, the column needs int type
syntax for creating a sequence:
CREATE [Temporary | TEMP] SEQUENCE name [INCREMENT [by] INCREMENT]
[MinValue minvalue | NO MinValue] [MAXVALUE MAXVALUE | NO MAXVALUE]
[start [with] start] [Cache cache] [[NO] CYCLE]
[owned by {table.column | NONE}]
Instance:
david=# Create sequence Tbl_xulie2_id_seq increment by 1 minvalue 1 no maxvalue start with 1;
Create SEQUENCE
david=#
david=# CREATE table Tbl_xulie2
David (# ID INT4 NOT NULL default Nextval (' Tbl_xulie 2_id_seq '),
David (# Name text);
CREATE TABLE
david=#
Third, view the sequence
david=# \d Tbl_xulie
Table "Public.tbl_xulie"
Column | Type | Modifiers
--------+---------+--------------------------------------------------------
ID | Integer | NOT NULL default Nextval (' Tbl_xulie_id_seq ':: regclass)
name | text |
david=# \d tbl_xulie2
Table "public.tbl_xulie2"
Column | Type | Modifiers
--------+---------+---------------------------------------------------------
ID | Integer | NOT NULL default Nextval (' Tbl_xulie2_id_seq ':: regclass)
name | text |
david=#
View Sequence Properties
david=# \d Tbl_xulie_id_seq
Sequence "Public.tbl_xulie_id_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | TBL_XULIE_ID_SEQ
Last_value | bigint | 1
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigi NT | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0< C23/>is_cycled | boolean | f
is_called | boolean | f
owned by:public.tbl_xulie.id
david=# select * from Tbl_xulie2_id_seq;
Sequence_name | last_value | start_value | increment_by | Max_value | min_value | cache_value | log_cnt | is_cycled | is_called
-------------------+------------+-------- -----+--------------+---------------------+-----------+-------------+---------+-----------+-----------
tbl_ Xulie2_id_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | F | f
(1 row)
Iv. Sequence Application
4.1 Using a sequence in the insert command
david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' David ');
Insert 0 1
david=# insert into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Sandy ');
INSERT 0 1
david=# select * from Tbl_xulie;
ID | Name
----+-------
1 | David
2 | Sandy
(2 rows)
4.2 Update sequence after data migration
david=# truncate Tbl_xulie;
TRUNCATE TABLE david=# david=# insert INTO Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Sandy ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' David ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Eagle ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Miles ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Simon ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Rock ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Peter ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Sally ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Nicole ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Monica '); Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' Tbl_xulie_id_seq '), ' Renee ');
INSERT 0 1 david=# select * from Tbl_xulie;
ID | Name
----+--------
15 | Sandy
16 | David
17 | Eagle
18 | Miles
19 | Simon
20 | Rock
21 | Peter
22 | Sally
23 | Nicole
24 | Monica
25 | Renee
(rows)
david=# copy Tbl_xulie to '/tmp/tbl_xulie.sql ';
COPY one
david=# truncate Tbl_xulie;
TRUNCATE TABLE
david=# alter sequence tbl_xulie_id_seq restart with;
ALTER SEQUENCE
david=# Select Currval (' Tbl_xulie_id_seq ');
Currval
david=# Select Nextval (' Tbl_xulie_id_seq ');
Nextval
david=# Select Nextval (' Tbl_xulie_id_seq ');
Nextval
---------
(1 row)
david=# begin;
BEGIN
david=# copy tbl_xulie from '/tmp/tbl_xulie.sql ';
COPY
-david=# Select Setval (' Tbl_xulie_id_seq ', max (ID)) from Tbl_xulie;
Setval
david=# end;
COMMIT
david=# INSERT INTO Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Flash ');
INSERT 0 1
david=# select * from Tbl_xulie;
ID | Name
----+--------
15 | Sandy
16 | David
17 | Eagle
18 | Miles
19 | Simon
20 | Rock
21 | Peter
22 | Sally
23 | Nicole
24 | Monica
25 | Renee
26 | Flash
(rows)
david=# Select Nextval (' Tbl_xulie_id_seq ');
Nextval
Five, sequence functions
The following sequence functions provide a simple and concurrent read security method for getting the latest sequence values from the sequence objects.
5.1 View Next Sequence value
david=# Select Nextval (' Tbl_xulie_id_seq ');
Nextval
david=# Select Nextval (' Tbl_xulie_id_seq ');
Nextval
5.2 View recent values for a sequence
david=# Select Nextval (' Tbl_xulie_id_seq ');
Nextval
david=# Select Currval (' Tbl_xulie_id_seq ');
Currval
david=# Select Currval (' Tbl_xulie_id_seq ');
Currval
5.3 Reset Sequence
Method one: Using Sequence functions
A. Setval (Regclass, bigint)
david=# truncate Tbl_xulie;
TRUNCATE TABLE
david=# Select Setval (' Tbl_xulie_id_seq ', 1);
Setval
david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Sandy ');
Insert 0 1
david=# insert into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' David ');
INSERT 0 1
david=# select * from Tbl_xulie;
ID | Name
----+-------
2 | Sandy
3 | David
(2 rows)
david=# Select Currval (' Tbl_xulie_id_seq ');
Currval
david=# Select Nextval (' Tbl_xulie_id_seq ');
Nextval
---------
4
(1 row)
B. Setval (Regclass, bigint, Boolean)
B.1 setval (Regclass, bigint, True) david=# truncate
;
TRUNCATE TABLE
david=# Select Setval (' Tbl_xulie_id_seq ', 1, true);
Setval
david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Sandy ');
Insert 0 1
david=# insert into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' David ');
INSERT 0 1
david=# select * from Tbl_xulie;
ID | Name
----+-------
2 | Sandy
3 | David
(2 rows)
Effect with a. Setval (Regclass, bigint)
B.2 Setval (Regclass, bigint, false) david=# truncate
;
TRUNCATE TABLE
david=# Select Setval (' Tbl_xulie_id_seq ', 1, false);
Setval
david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Sandy ');
Insert 0 1
david=# insert into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' David ');
INSERT 0 1
david=# select * from Tbl_xulie;
ID | Name
----+-------
1 | Sandy
2 | David
(2 rows)
Method Two: Modify the sequence
To modify the syntax of a sequence:
ALTER SEQUENCE name [INCREMENT [by] INCREMENT]
[MinValue minvalue | NO MinValue] [MAXVALUE MAXVALUE | NO MAXVALUE]
[start [with] start]
[Restart [[with] restart]]
[Cache Cache] [[NO] CYCLE]
[owned by {table.column | NONE]
alter SEQUENCE name OWNER to New_owner
alter SEQUENCE name RENAME to new_name
alter SEQUENCE name SET SCHEMA New_schema
Instance:
david=# truncate Tbl_xulie;
TRUNCATE TABLE
david=# alter sequence TBL_XULIE_ID_SEQ restart with 0;
Error:restart Value (0) cannot is less than MinValue (1)
david=# alter sequence tbl_xulie_id_seq restart with 1;
ALTER SEQUENCE
david=# insert INTO Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' David ');
Insert 0 1
david=# insert into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Sandy ');
INSERT 0 1
david=# select * from Tbl_xulie;
ID | Name
----+-------
1 | David
2 | Sandy
(2 rows)
david=# Select Nextval (' Tbl_xulie_id_seq ');
Nextval
Vi. Delete Sequence
Grammar:
DROP SEQUENCE [IF EXISTS] name [, ...] [CASCADE | RESTRICT]
When a table field is used to a PG sequence, it cannot be deleted directly.
david=# drop sequence tbl_xulie2_id_seq;
Error:cannot drop sequence Tbl_xulie2_id_seq because other objects depend on it
detail:default for table Tbl_xulie2 Column ID depends on sequence tbl_xulie2_id_seq
hint:use DROP ... CASCADE to drop the dependent objects too.
david=# drop table tbl_xulie2;
Drop TABLE
david=# drop sequence tbl_xulie2_id_seq;
DROP SEQUENCE
david=#
Note: For a sequence is created by specifying serial when the table is built, the corresponding sequence is deleted while the table is deleted.
The
Seven, other description
a.currval Gets the sequence value of the current session, and the value in the current session does not change because the other session is Nextval. It is the global Last_value value that will change, and the current session will be nextval if the currval is read directly if the value is not read.
B. When a sequence is created by specifying serial when the table is built, the corresponding sequence is deleted as well.
C. Table primary key data can be associated with a table-related sequence, or it can be used in other sequences, but not recommended, just PG default it's right.
D. In order for multiuser concurrency to have a sequence value that is not duplicated, nextval is not rollback, but you can use Setval to reset
If a sequence object was created with a default parameter, calling Nextval on it would return a subsequent number starting at 1. Other behaviors can be obtained by using special parameters in the CREATE SEQUENCE command; see the Command Reference page for more information.
E. The nextval operation will never be rolled back in order to avoid the current transaction that gets the numeric value from the same sequence, that is, once a value has been taken, it is considered to have been used, even after the transaction that invoked Nextval has exited. This means that the exiting transaction may leave a "hole" in the value given by the sequence. The setval operation is never rolled back.