postgreSql自增序列

创建一个自增序列

CREATE SEQUENCE "public"."xxx_id_seq"
INCREMENT 1
MINVALUE  1
NO MAXVALUE
START 1

查询某个序列的下一个值

-- 当把nextval('xxx_id_seq'::regclass)设置到ID的默认值时可用实现自增操作
select nextval('xxx_id_seq'::regclass)

有时会碰到表内ID已经增长很多了还是序列却从0开始了,这种情况就需要将当前表内的最大值设置为序列的初始值

SELECT setval( 'xxx_id_seq', (SELECT MAX(id) FROM `表名称` ) +1 );

下面是获取某个库的所有更新自增序列的.

-- E'' 这个写法是为了转义
select concat( E'SELECT setval(\'',s.relname ,E'\',(SELECT MAX(id) FROM ', c.relname, ') +1 );')
from (select oid,relname from pg_class where relkind ='r' and relnamespace = 2200)c
join pg_attrdef af on c.oid = af.adrelid
join pg_depend d on d.objid = af.oid and d.deptype = 'n'
join pg_class s on s.oid = d.refobjid
where s.relname is not null