“红移”无法插入，时间戳列为字符串类型。
“红移”无法插入，时间戳列为字符串类型。
提问于 2020-12-18 19:06:14
我有两个红移表，它们是彼此的精确副本(扫描，scans_staging)，一个是一个临时表，另一个是主表。它们有下列DDL：
CREATE TABLE scans_staging (
id text,
imb text,
scandatetime timestamp without time zone,
scaneventcode text,
status text,
anticipateddeliverydate timestamp without time zone,
scanfacilityname text,
scanfacilitystate text,
scanfacilitycity text,
scanfacilityzip text
);我正在尝试运行一个查询，将数据从暂存表插入到扫描表，如下所示：
insert into scans
select scans_staging.*
from scans
right join scans_staging on scans.id = scans_staging.id
where scans.id is null但是，我得到了错误：Invalid operation: column "scandatetime" is of type timestamp without time zone but expression is of type character varying
但是当我查看两个表中的数据时，时间戳是完全相同的格式，例如2020-11-23 16:17:02。他们在yyyy-MM-dd HH:mm:ss。我在这里犯了什么小错误？
编辑:下面是下表def查询的结果：
select "column", type, encoding, distkey, sortkey, "notnull"
from pg_table_def
where tablename = 'scans' 这两张表
Scans:
|column |type |encoding|distkey|sortkey |notnull|
----------------------------------------------------------------------------------------
|id |character varying(256) |lzo |false |0 |false |
|imb |character varying(256) |lzo |false |0 |false |
|scandatetime |timestamp without time zone|az64 |false |0 |false |
|scaneventcode |character varying(256) |lzo |false |0 |false |
|status |character varying(256) |lzo |false |0 |false |
|anticipateddeliverydate|timestamp without time zone|az64 |false |0 |false |
|scanfacilityname |character varying(256) |lzo |false |0 |false |
|scanfacilitystate |character varying(256) |lzo |false |0 |false |
|scanfacilitycity |character varying(256) |lzo |false |0 |false |
|scanfacilityzip |character varying(256) |lzo |false |0 |false |
Scans_staging:
|column |type |encoding|distkey|sortkey |notnull|
----------------------------------------------------------------------------------------
|id |character varying(256) |lzo |false |0 |false |
|imb |character varying(256) |lzo |false |0 |false |
|scandatetime |timestamp without time zone|az64 |false |0 |false |
|scaneventcode |character varying(256) |lzo |false |0 |false |
|status |character varying(256) |lzo |false |0 |false |
|anticipateddeliverydate|timestamp without time zone|az64 |false |0 |false |
|scanfacilityname |character varying(256) |lzo |false |0 |false |
|scanfacilitystate |character varying(256) |lzo |false |0 |false |
|scanfacilitycity |character varying(256) |lzo |false |0 |false |
|scanfacilityzip |character varying(256) |lzo |false |0 |false |回答 1
Stack Overflow用户
回答已采纳
发布于 2020-12-18 19:25:48
如果两个表的结构完全相同，则不会得到该错误。
首先，我将枚举insert和select子句中的列，以避免两个表中的列不位于相同位置的潜在问题：
insert into scans (
id, imb, scandatetime, scaneventcode, status, anticipateddeliverydate, scanfacilityname, scanfacilitystate, scanfacilitycity, scanfacilityzip
)
select id, imb, scandatetime, scaneventcode, status, anticipateddeliverydate, scanfacilityname, scanfacilitystate, scanfacilitycity, scanfacilityzip
from scans_staging ss
where not exists (select 1 from scans s where s.id = ss.id)如果仍然存在类型不匹配错误，这表示您的列具有不同的数据类型。如果是这样的话，您需要在select子句中设置额外的强制转换。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/65362580
复制相关文章
相似问题
腾讯云开发者
