在PostgreSQL中的COPY函数 [英] COPY function in PostgreSQL

问题描述

我想使用PostgreSQL中的COPY函数将CSV文件导入PostgreSQL数据库。

在文档，CSV文件必须存储在特定位置，还是可以存储在任何位置。

例如， copy data_table from'/tmp/outputdata.csv'WITH DELIMITER AS'，'CSV QUOTE AS'' code>。 tmp 这是否意味着C：驱动器中的tmp文件夹，是否可以更改为另一个文件夹名称？

解决方案

看起来你对Linux和Windows文件路径符号感到困惑，你有一个Linux路径锚定到根目录。

如果您使用Windows符号，请小心，您必须转义反斜杠，如果您没有使用 standard_conforming_strings = on - 这是最新版本9.1中的默认值，但不是旧版本中的值。

  COPY data_table from E'C：\\tmp\\outputdata.csv'WITH ... 
  

适用于任何情况。

使用 standard_conforming_strings = on ，您还可以写：

  COPY data_table from'C：\tmp\outputdata.csv'WITH ... 
  
 
 
 
请注意，PostgreSQL Windows服务器还理解为斜杠而不是反斜杠的默认路径符号。
 
 
 
对于SQL   COPY FROM / TO  您可以使用服务器进程所有者（默认情况下 postgres ）具有读/写权限的任何路径。
 
 
 
请注意，对于  \copy   meta命令，psql客户端当前本地用户的权限应用。
 
I would like to use the COPY function in PostgreSQL to import a CSV file into a PostgreSQL database.

Where it says the filename in the documentation, does the CSV file have to be stored in a specific location or can it be stored in any location. 

For example, copy data_table from '/tmp/outputdata.csv' WITH DELIMITER AS ',' CSV QUOTE AS '"';. Where it says tmp, does that mean the tmp folder in the C: drive. Can it be change to another folder name?
解决方案
It looks like you are confused by Linux vs. Windows file-path notation. What you have there is a Linux path anchored to root. Windows uses drive letters - which you can specify just as well when you are running on Windows.

If you use Windows notation, take care that you have to escape backslashes if you are not using standard_conforming_strings = on - which is the default in the latest version 9.1 but not in older versions. Like this:
COPY data_table from E'C:\\tmp\\outputdata.csv' WITH ...
Works in any case.

With standard_conforming_strings = on you could also write:
COPY data_table from 'C:\tmp\outputdata.csv' WITH ...
Note that a PostgreSQL Windows server also understands default path notation with slashes instead of backslashes.

For SQL COPY FROM / TO you can use any path that the owner of server process (postgres by default) has permission to read / write.

Note that for the \copy meta command of the psql client the permissions of current local user apply.

                        
这篇关于在PostgreSQL中的COPY函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！