PostgreSQL按带时区时间戳分区后插入数据报错求助

问题场景 #

已基于row_create_tmst（带时区的时间戳）创建范围分区表，成功创建指定日期分区的语句：

CREATE TABLE source_record_Apr10 PARTITION OF source_record_new
FOR VALUES FROM ('2023-04-10T00:00:00') TO ('2023-04-10T23:59:59');

执行跨表插入时触发错误，插入语句：

insert into source_record_new
(select * from source_record where row_created_tmst between '2023-04-10 00:00:00.000+00' and '2023-04-10 23:59:59.999+00')

错误提示：

ERROR: column "row_modified_tmst" is of type timestamp with time zone but expression is of type character varying
LINE 3: (select * from midas_user.staging_source_record where row_c...

原因分析 #

尽管你认为两张表列类型一致，但实际存在以下可能：

• 源表source_record的row_modified_tmst列实际为字符类型（建表定义错误或数据导入时被转换）
• 使用select *时列顺序不匹配，导致字符型数据被映射到目标表的timestamp with time zone列
• 存在隐式类型转换失败的场景

解决步骤 #

1. 验证表结构一致性
   执行以下语句对比两张表的列定义，重点确认row_modified_tmst的数据类型：

-- 查看目标表列定义
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'source_record_new';
-- 查看源表列定义
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'source_record';

2. 显式指定列名插入
   避免使用select *，明确指定列名确保顺序匹配，示例：

insert into source_record_new (col1, col2, row_created_tmst, row_modified_tmst, ...)
select col1, col2, row_created_tmst, row_modified_tmst, ... 
from source_record 
where row_created_tmst between '2023-04-10 00:00:00.000+00' and '2023-04-10 23:59:59.999+00';

3. 强制转换数据类型
   如果源表row_modified_tmst确实为字符类型，显式转换后再插入：

insert into source_record_new
select 
  col1, 
  col2, 
  row_created_tmst, 
  row_modified_tmst::timestamp with time zone, 
  ...
from source_record 
where row_created_tmst between '2023-04-10 00:00:00.000+00' and '2023-04-10 23:59:59.999+00';

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