LightDB 序列支持NOMAXVALUE功能-CSDN博客

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

功能介绍

Oracle数据库在创建sequence的时候可以支持设置nomaxvalue，这样的SQL在LightDB 23.3版本之前都是执行失败的。为了方便Oracle用户迁移到LightDB上，在LightDB 23.3版本上，增加了sequence支持设置nomaxvalue的语法兼容。
nomaxvalue内部逻辑和PostgreSQL数据库sequence原本就支持的NO MAXVALUE相同。

使用说明

创建sequence时指定nomaxvalue

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

lightdb@lt_test=#

修改sequence时指定nomaxvalue

lightdb@lt_test=# alter sequence s1 maxvalue 100000;
ALTER SEQUENCE
lightdb@lt_test=# \d+ s1
                       Sequence "public.s1"
  Type  | Start | Minimum | Maximum | Increment | Cycles? | Cache 
--------+-------+---------+---------+-----------+---------+-------
 bigint |     1 |       1 |  100000 |         1 | no      |     1

lightdb@lt_test=# alter sequence s1 nomaxvalue;
ALTER SEQUENCE
lightdb@lt_test=# \d+ s1
                             Sequence "public.s1"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

lightdb@lt_test=#

nomaxvalue不可以和no maxvalue或者maxvalue一起使用

lightdb@lt_test=# alter sequence s1 maxvalue 10000 nomaxvalue;
ERROR:  conflicting or redundant options
LINE 1: alter sequence s1 maxvalue 10000 nomaxvalue;
                                         ^
lightdb@lt_test=# alter sequence s1 no maxvalue nomaxvalue;
ERROR:  conflicting or redundant options
LINE 1: alter sequence s1 no maxvalue nomaxvalue;
                                      ^
lightdb@lt_test=#