使用php将txt文件导入postgres时出现问题

问题描述：

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
";
} 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

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

  $ query ='\\'。'copy data1 FROM“data1.txt”WITH CSV HEADER DELIMITER AS“，”QUOTE AS“^”';  
 
 $ result = pg_query（$ conn，$ query）; 
if（！$ result）{
 echo“无法复制数据
”; 
}其他{
 echo“SUCCESS！”; 
  } 
  code>  pre> 
 
 当我运行这个php文件时，我收到此错误： p> 
 
 
  PHP警告：pg_query（）  ：查询失败：错误：语法错误在“\”
LINE 1处或附近：\ copy data1 FROM“data1.txt”WITH ... 
 ^在第30行的script.php 
  code>  pre  > 
  div>

答

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时出现问题

相关推荐