postgresql - 使用不正确的日期值将 csv 导入 Postgres 数据库

Question

我有一个查询，它有一个日期字段，其值在查询结果窗口中看起来像这样:

2013-10-01 00:00:00

但是，当我将结果保存到 csv 时，它会像这样保存:

2013-10-01T00:00:00

当我尝试将 csv 复制到 Redshift 中的表中时，这会导致出现问题，它会给我一个错误，指出该值不是有效的时间戳(我要导入的字段是时间戳字段).

我怎样才能让它完全去掉时间部分，只留下日期，或者至少从结果中删除“T”？

我正在使用 Aginity SQL Workbench for Redshift 将结果导出到 csv。

Answer 1

根据 this knowledgebase article :

After import, add new TIMESTAMP columns and use the CAST() function to populate them:

ALTER TABLE events ADD COLUMN received_at TIMESTAMP DEFAULT NULL;
UPDATE events SET received_at = CAST(received_at_raw as timestamp);

ALTER TABLE events ADD COLUMN generated_at TIMESTAMP DEFAULT NULL; UPDATE events SET generated_at = CAST(generated_at_raw as timestamp); Finally, if you forsee no more imports to this table, the raw VARCHAR timestamp columns may be removed. If you forsee importing more events from S3, do not remove these columns. To remove the columns, run:

ALTER TABLE events DROP COLUMN received_at_raw; ALTER TABLE events
DROP COLUMN generated_at_raw;

希望对您有所帮助...