postgresql 使用 psql \copy 导入带有时间戳列 (dd.mm.yyyy hh.mm.ss) 的 .csv
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14042452/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Importing .csv with timestamp column (dd.mm.yyyy hh.mm.ss) using psql \copy
提问by jatobat
I'm trying to import data from a .csv file into a postgresql 9.2 database using the psql \COPYcommand (not the SQL COPY).
我正在尝试使用psql \COPY命令（而不是 SQL COPY）将 .csv 文件中的数据导入 postgresql 9.2 数据库。
The input .csv file contains a column with a timestamp in the dd.mm.yyyy hh.mm.ss format.
输入 .csv 文件包含一个带有 dd.mm.yyyy hh.mm.ss 格式的时间戳的列。
I've set the database datestyle to DMY using.
我已使用将数据库日期样式设置为 DMY。
set datestyle 'ISO,DMY'
Unfortunately, when I run the \COPYcommand:
不幸的是，当我运行\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'
I get this error:
我收到此错误：
ERROR: date/time field value out of range: "16.11.2012 07:10:06"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: COPY trace, line 2, column timestamp_mes: "16.11.2012 07:10:06"
错误：日期/时间字段值超出范围：“16.11.2012 07:10:06”
提示：也许您需要不同的“日期样式”设置。
上下文：复制跟踪，第 2 行，列时间戳记：“16.11.2012 07:10:06”
What is wrong with the datestyle?
日期样式有什么问题？
回答by Erwin Brandstetter
Have you tried setting the datestylesetting of the server?
您是否尝试过设置服务器的datestyle设置？
SET datestyle = 'ISO,DMY';
You are using the psql meta-command \copy, which means the input file is local to the client. But it's still the server who has to coerce the input to matching data-types.
您正在使用 psql meta-command \copy，这意味着输入文件是客户端的本地文件。但它仍然是服务器必须将输入强制转换为匹配的数据类型。
More generally, unlike the psql meta-command \copywhich invokes COPYon the server and is closely related to it .. I quote the manual concerning \set:
更一般地说，与在服务器\copy上调用COPY并与之密切相关的 psql 元命令不同......我引用了有关\set以下内容的手册：
Note: This command is unrelated to the SQL command SET.
注意：此命令与 SQL 命令 SET 无关。
回答by homat
I found it difficult to apply 'SET datestyle' within the same session when applying the psql command. Altering the datestyle on the whole database/server (just for the import) also might cause side effects on other users or existing applications. So i usually modify the file itself before loading:
我发现在应用 psql 命令时很难在同一个会话中应用“SET 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]\);/;--;/g' myfile
# then import file with date column
psql <connect_string> -c "\COPY mytable FROM 'myfile' ...."
回答by Noel Cosgrave
The date style you seem to be using is German. PostgreSQL supports this date style. Try using this:
您似乎使用的日期样式是德语。PostgreSQL 支持这种日期样式。尝试使用这个：
SET datestyle TO German;
