当前位置：首页 > news >正文

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

news 2025/8/29 3:59:31

功能介绍

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

使用说明

创建sequence时设置maxvalue为9999999999999999999999999999

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

修改sequence时设置maxvalue为9999999999999999999999999999

lightdb@lt_test=# create sequence s2 maxvalue 100000;
CREATE SEQUENCE
lightdb@lt_test=# \d+ s2Sequence "public.s2"Type  | Start | Minimum | Maximum | Increment | Cycles? | Cache 
--------+-------+---------+---------+-----------+---------+-------bigint |     1 |       1 |  100000 |         1 | no      |     1lightdb@lt_test=# 
lightdb@lt_test=# alter sequence s2 maxvalue 9999999999999999999999999999;
ALTER SEQUENCE
lightdb@lt_test=# \d+ s2Sequence "public.s2"Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1lightdb@lt_test=#

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

lightdb@lt_test=# create sequence s3 as smallint maxvalue 1000;
CREATE SEQUENCE
lightdb@lt_test=# \d+ s3Sequence "public.s3"Type   | Start | Minimum | Maximum | Increment | Cycles? | Cache 
----------+-------+---------+---------+-----------+---------+-------smallint |     1 |       1 |    1000 |         1 | no      |     1lightdb@lt_test=# alter sequence s3 maxvalue 9999999999999999999999999999;
ALTER SEQUENCE
lightdb@lt_test=# \d+ s3Sequence "public.s3"Type   | Start | Minimum | Maximum | Increment | Cycles? | Cache 
----------+-------+---------+---------+-----------+---------+-------smallint |     1 |       1 |   32767 |         1 | no      |     1lightdb@lt_test=#