【DWS】跨库访问外表带collate case_insensitive参数报错column information does
【DWS】跨库访问外表带collate case_insensitive参数报错column information does not match.
【问题版本】8.1.3
建立原表:
建立外表:
CREATE FOREIGN TABLE ptemp.ft_etl_job3(
etl_system character(6) collate case_insensitive
,etl_job character varying(100) collate case_insensitive
,etl_server character varying(20) collate case_insensitive
,description character varying(100) collate case_insensitive
,frequency character varying(60) collate case_insensitive
,jobtype character(1) collate case_insensitive
,enable character(1) collate case_insensitive
,last_starttime character(38) collate case_insensitive
,last_endtime character(38) collate case_insensitive
,last_jobstatus character varying(40) collate case_insensitive
,last_txdate date
,last_filecnt integer
,last_cubestatus character(40) collate case_insensitive
,cubeflag character(1) collate case_insensitive
,checkflag character(1) collate case_insensitive
,autooff character(1) collate case_insensitive
,checkcalendar character(1) collate case_insensitive
,calendarbu character varying(30) collate case_insensitive
,runningscript character varying(100) collate case_insensitive
,jobsessionid integer
,expectedrecord integer
,checklaststatus character(1) collate case_insensitive
,timetrigger character(1) collate case_insensitive
,job_priority smallint
)
SERVER rs_uat3_to_edw
OPTIONS(schema_name 'petl_bak', table_name 'etl_job', encoding 'utf-8');
查询结果
【问题跟因】collate检查逻辑问题,collate值有残留,遇到不带collate的字段,会持有上一个字段的collate,导致判断出错,即第一个字段带collate,第二个不带的话会导致它判断第二个字段的类型,导致报错
【规避方法】所有带collate的字段需要放在不带collate参数的后面保证后面所有的字段都是正常识别
- 点赞
- 收藏
- 关注作者
评论(0)