数据仓库服务 GAUSSDB(DWS)-CREATE SEQUENCE:示例

时间:2024-12-06 15:12:41

示例

创建一个名为serial的递增序列,从101开始:

1
2
3
CREATE SEQUENCE serial
 START 101
 CACHE 20;

从序列中选出下一个数字:

1
2
3
4
SELECT nextval('serial');
 nextval 
 ---------
      101

从序列中选出下一个数字:

1
2
3
4
SELECT nextval('serial');
 nextval 
 ---------
      102

创建与表关联的序列:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE customer_address
(
    ca_address_sk             integer               not null,
    ca_address_id             char(16)              not null,
    ca_street_number          char(10)                      ,
    ca_street_name            varchar(60)                   ,
    ca_street_type            char(15)                      ,
    ca_suite_number           char(10)                      ,
    ca_city                   varchar(60)                   ,
    ca_county                 varchar(30)                   ,
    ca_state                  char(2)                       ,
    ca_zip                    char(10)                      ,
    ca_country                varchar(20)                   ,
    ca_gmt_offset             decimal(5,2)                  ,
    ca_location_type          char(20)                     
) ;

CREATE SEQUENCE serial1
 START 101
 CACHE 20
OWNED BY customer_address.ca_address_sk;

使用serial创建主键自增序列表serial_table:

1
2
3
4
5
6
7
8
9
CREATE TABLE serial_table(a int, b serial);
INSERT INTO serial_table (a) VALUES (1),(2),(3);
SELECT * FROM serial_table ORDER BY b;
 a | b
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)
support.huaweicloud.com/sqlreference-910-dws/dws_06_0174.html