如何基于现有列新增标准化姓名列而非更新原列（PostgreSQL）

原始数据 #

创建表并插入数据：

create table mytest (
staff_n int,
emp_name text
);

insert into mytest values (1,'James'),(1,'James H'),(2,'B Hua'),(2,'B Huatur');

原始表数据：

原有实现（更新原列） #

之前通过以下SQL将原emp_name列原地更新为对应staff_n的最大姓名值：

with temp_t as (
select staff_n,max(emp_name) as max_emp from mytest
group by staff_n)
update mytest
set emp_name = max_emp
from temp_t
where mytest.staff_n = temp_t.staff_n;

更新后原列被覆盖，数据变为：

新增归一化列的解决方案 #

要保留原emp_name列，新增emp_name_normalized列存储归一化值，分两步操作：

1. 添加新列 #

先给表新增目标列：

ALTER TABLE mytest ADD COLUMN emp_name_normalized text;

2. 填充新列值 #

使用CTE关联分组后的最大姓名值，更新新列：

WITH temp_t AS (
    SELECT staff_n, MAX(emp_name) AS max_emp 
    FROM mytest 
    GROUP BY staff_n
)
UPDATE mytest
SET emp_name_normalized = temp_t.max_emp
FROM temp_t
WHERE mytest.staff_n = temp_t.staff_n;

最终结果 #

执行后表数据如下：

内容的提问来源于stack exchange，提问作者moth