postgresql 把2021-8-9改为2021-08-09
--当字段长度等于8时,代表当前为2021-8-9格式
UPDATE tdms_doc_library
SET doc_recheck_date = concat(LEFT ( "doc_recheck_date", 5 ) ,'0' , SUBSTRING( "doc_recheck_date", 6, 2 ) , '0' , RIGHT ( "doc_recheck_date", 1 ) )
WHERE
LENGTH ( "doc_recheck_date" ) = 8
--当字段长度为9时,并且当前是2021-8-09格式
UPDATE tdms_doc_library
SET doc_recheck_date = concat(LEFT ( "doc_recheck_date", 5 ) ,'0' , SUBSTRING( "doc_recheck_date", 6, 2 ) , RIGHT ( "doc_recheck_date", 2 ) )
WHERE
length(split_part("doc_recheck_date",'-',3)) = 2 and LENGTH ( "doc_recheck_date" ) = 9
--当字段长度为9时,并且当前是2021-08-9格式
UPDATE tdms_doc_library
SET doc_recheck_date = concat(LEFT ( "doc_recheck_date", 5 ) , SUBSTRING( "doc_recheck_date", 6, 3 ),0 , RIGHT ( "doc_recheck_date", 1 ) )
WHERE
length(split_part("doc_recheck_date",'-',3)) = 1 and LENGTH ( "doc_recheck_date" ) = 9
用的最简单的sql语句方式实现,如果有更好的实现方式,欢迎讨论。