使用php将txt文件导入到postgres时出现问题 [英] Problems while importing a txt file into postgres using php

问题描述

我正在尝试使用\copy命令从php导入一个txt / csv文件到我的postgres数据库中。我不能使用COPY而不是\copy，因为我需要它作为psql客户端执行。我的代码是：

  $ query ='\\'。'copy data1 FROMdata1.txtWITH CSV HEADER DELIMITER AS，QUOTE AS，^'; 
 
 $ result = pg_query（$ conn，$ query）; 
 if（！$ result）{
 echo无法复制数据\ n; 
} else {
 echoSUCCESS！; 
} 
  

当我运行这个php文件时，出现这个错误：

  PHP警告：pg_query（）：查询失败：错误：语法错误位于或接近\
 LINE 1：\复制data1 FROMdata1.txtWITH ... 
 ^在第30行的script.php中
  

pg_query（）运行 \copy >。这是不是SQL命令。它是 psql 客户端的元命令。

您可以执行：

$ p $ < data1.txt'WITH CSV HEADER DELIMITER AS'，'QUOTE AS'^'

或运行shell命令：
$ b $ pre $ code $ ps $ mydb -c\ copy data1 FROM'data1.txt'
带有CSV头部分隔符AS'，'QUOTE AS'^'

请注意引号。值需要在PostgreSQL中单引号 ： 'value'。

双引号是用于标识符的 - 实际上仅用于带有大写或非法字符的标识符或保留字： code>我的表。

I am trying to import a txt/csv file into my postgres database from php using "\copy" command. I cannot use COPY instead of \copy as I need it to execute as a psql client. My code is:

$query = '\\'.'copy data1 FROM "data1.txt" WITH CSV HEADER DELIMITER AS "," QUOTE AS "^"';

$result = pg_query($conn,$query);
if (!$result) {
  echo "cannot copy data\n";
} else {
  echo "SUCCESS!";
}

When I run this php file, I get this error:

PHP Warning:  pg_query(): Query failed: ERROR:  syntax error at or near "\"
LINE 1: \copy data1 FROM "data1.txt" WITH ...
    ^ in script.php on line 30

解决方案

Actually, you cannot run \copy via pg_query(). It is not an SQL command. It is a meta-command of the psql client.

There you can excute:

\copy data1 FROM 'data1.txt' WITH CSV HEADER DELIMITER AS ',' QUOTE AS '^'

Or run the shell-command:

psql mydb -c "\copy data1 FROM 'data1.txt'
                WITH CSV HEADER DELIMITER AS ',' QUOTE AS '^'"

Note the quotes. Values need to be single-quoted in PostgreSQL: 'value'.
Double-quotes are for identifiers - and are only actually needed for identifiers with upper case or illegal character or for reserved words: "My table".

这篇关于使用php将txt文件导入到postgres时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！