LightDB-A sequence支持MAXVALUE最大值与Oracle相同

yunmu666

已于 2023-08-17 16:56:54 修改

阅读量389

点赞数

文章标签： oracle 数据库

于 2023-08-17 15:18:35 首次发布

本文链接：https://blog.csdn.net/yunmu666/article/details/132341692

版权

功能介绍

Oracle数据库在创建sequence的时候可以支持设置maxvalue 为9999999999999999999999999999，这样的SQL在LightDB-A 23.3版本之前都是执行失败的。为了方便Oracle用户迁移到LightDB-A上，在LightDB-A 23.3版本上，增加了sequence支持maxvalue设置为9999999999999999999999999999的语法兼容。
如果设置的maxvalue的值大于INT64_MAX（9223372036854775807），则设置maxvalue为sequence类型所对应的最大值。

使用说明

创建sequence时设置maxvalue为9999999999999999999999999999

postgres=# create sequence s1 maxvalue 9999999999999999999999999999;
CREATE SEQUENCE
postgres=# \d+ s1
                             Sequence "public.s1"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |    20

postgres=#

修改sequence时设置maxvalue为9999999999999999999999999999

postgres=# create sequence s2 maxvalue 100000;
CREATE SEQUENCE
postgres=# \d+ s2
                       Sequence "public.s2"
  Type  | Start | Minimum | Maximum | Increment | Cycles? | Cache 
--------+-------+---------+---------+-----------+---------+-------
 bigint |     1 |       1 |  100000 |         1 | no      |    20

postgres=# 
postgres=# alter sequence s2 maxvalue 9999999999999999999999999999;
ALTER SEQUENCE
postgres=# \d+ s2
                             Sequence "public.s2"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |    20

postgres=#

当sequence类型为smallint时，设置maxvalue为9999999999999999999999999999

postgres=# create sequence s3 as smallint maxvalue 1000;
CREATE SEQUENCE
postgres=# \d+ s3
                        Sequence "public.s3"
   Type   | Start | Minimum | Maximum | Increment | Cycles? | Cache 
----------+-------+---------+---------+-----------+---------+-------
 smallint |     1 |       1 |    1000 |         1 | no      |    20

postgres=# alter sequence s3 maxvalue 9999999999999999999999999999;
ALTER SEQUENCE
postgres=# \d+ s3
                        Sequence "public.s3"
   Type   | Start | Minimum | Maximum | Increment | Cycles? | Cache 
----------+-------+---------+---------+-----------+---------+-------
 smallint |     1 |       1 |   32767 |         1 | no      |    20

postgres=#