使用psql \copy导入包含时间戳列（dd.mm.yyyy hh.mm.ss）的.csv文件

Question

我想使用psql \COPY命令（而不是SQL COPY）将.csv文件中的数据导入到PostgreSQL 9.2数据库中。

.csv输入文件包含一个以dd.mm.yyyy hh.mm.ss格式表示的时间戳列。

我已经设置了数据库的日期样式为DMY。

set datestyle 'ISO,DMY'

不幸的是，当我运行\COPY命令时：

\COPY gc_test.trace(numpoint,easting,northing,altitude,numsats,pdop,timestamp_mes,duration,ttype,h_error,v_error) 
FROM 'C:\data.csv' WITH DELIMITER ';' CSV HEADER ENCODING 'ISO 8859-1'

我遇到了这个错误：

ERROR：日期/时间字段值超出范围：“16.11.2012 07:10:06”

HINT：也许您需要不同的“datestyle”设置。

CONTEXT：复制跟踪，第2行，列时间戳_mes：“16.11.2012 07:10:06”

datestyle 的问题在哪里？

- jatobat

Answer 1

你尝试设置服务器的datestyle设置吗？

SET datestyle = 'ISO,DMY';

您正在使用psql元命令\copy，这意味着输入文件在客户端本地。但仍然是服务器必须强制输入匹配数据类型。

更一般地说，与在服务器上调用 COPY 并与之密切相关的 psql 元命令 \copy 不同。我引用有关\set的手册：

注意：此命令与 SQL 命令 SET 无关。

- Erwin Brandstetter

---

修改postgresql.conf是一种激进的方法。你也可以在运行\copy之前，在你的会话中发出所述的SQL命令。然后该设置仅更改该会话。 - Erwin Brandstetter

好的，我已经重启了所有东西，将postgresql.conf重置为其原始配置“iso,mdy”。连接到服务器。运行SET datestyle ='ISO,DMY';然后是\copy命令。导入成功。非常感谢您的帮助。 - jatobat

@NandaKumar：使用to_timestamp()。参考链接：https://dev59.com/iWMk5IYBdhLWcg3w2RaT#18919571 - Erwin Brandstetter

@ErwinBrandstetter。谢谢，但我的要求是在导入CSV文件时能够进行转换。很抱歉，我没有明确提到这一点，因为帖子是关于导入CSV的。目前，我已经采取了一个两步骤的过程，将其作为varchar导入到临时表中，然后执行“insert into select”并进行转换为to_timestamp。但我正在寻找是否有直接的方法来完成它。 - PKU

@NandaKumar：请将您的问题提出为“问题”。评论不是提问的地方。 - Erwin Brandstetter

Answer 2

在使用psql命令时，我发现在同一会话中应用“SET datestyle”很困难。在整个数据库/服务器上更改datestyle（仅供导入使用）也可能对其他用户或现有应用程序产生副作用。因此，通常在加载之前修改文件本身：

#!/bin/bash 
#
# change from dd.mm.yyyy to yyyy-mm-dd inside the file
# note: regex searches for date columns separated by semicolon (;) 
sed -i 's/;\([0-9][0-9]\)\.\([0-9][0-9]\)\.\([0-9][0-9][0-9][0-9]\);/;\3-\2-\1;/g' myfile
# then import file with date column
psql <connect_string> -c "\COPY mytable FROM 'myfile' ...."

- homat

Answer 3

你似乎正在使用德国日期格式。PostgreSQL支持这种日期格式。请尝试使用以下格式：

SET datestyle TO German;

- Noel Cosgrave