PostgreSQL中WITH AS语句报语法错误[42601]的原因排查

你的SQL报错看似是逗号位置的问题，但实际根源是列名拼写错误，导致SQL解析器误判了语法结构：

• 在data2的SELECT语句里，列名写成了logoffet，正确应为logoffset（需和data1里的列名保持一致）

另外，PostgreSQL中多CTE用逗号分隔是完全合法的语法，不需要删除逗号。

修正后的完整SQL如下：

create table final_owner as
with data1 as (
  select fullnode as node, owner, address, block, logoffset, event_time 
  from registry_newowner 
),
data2 as (
  select node, owner, address, block, logoffset, event_time 
  from registry_transfer
),
combined_data as (
  select * from data1 
  union all 
  select * from data2
),
ranked_data as (
  select node, owner, address, block, logoffset, event_time,
       row_number() over(partition by node order by event_time desc, block desc, logoffset desc) as rn
  from combined_data
)
select node, owner, address, block, logoffset, event_time
from ranked_data
where rn=1;

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