postgresql update 设置某列为某些条件生成的值

最新推荐文章于 2025-10-13 00:00:00 发布

原创最新推荐文章于 2025-10-13 00:00:00 发布 · 1.3k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #update #coalesce #lpad #rpad

数据库专栏收录该内容

32 篇文章

订阅专栏

本文介绍了一种使用SQL语句动态生成表中记录的唯一编码的方法。通过结合row_number()函数、COALESCE函数、to_char函数及lpad函数，实现了基于创建时间和行号递增的唯一编码生成。这种方法适用于需要为已有数据添加唯一标识符的场景。

with tmp as( select row_number() over() as rownum,id idd  from bas_cm_verification_strategy)
update bas_cm_verification_strategy
set code = COALESCE(to_char(create_time, 'yyyyMMdd')) || lpad(CAST(tmp.rownum as VARCHAR), 3, '0')
from tmp where id = tmp.idd;

需求说明：

接到一个需求：给表bas_cm_verification_strategy新增一个code字段，表中历史数据要根据创建时间+001递增来设置code的值。

注释：

其中 row_number() over() 获取到的是数据的行号，此处为了省事没有用序列。

lpad(string text, length int [, fill text]) 通过填充字符 fill （缺省时为空白），把 string 填充为长度 length。如果 string 已经比 length 长则将其截断（在右边）。类似的还有rpad，不过填充的位置相反。

COALESCE(to_char(create_time, 'yyyyMMdd')) 格式化时间

CAST(tmp.rownum as VARCHAR) 转换格式为字符串