Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.2k views
in Technique[技术] by (71.8m points)

postgresql - Problems while importing a txt file into postgres using php

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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".


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...