Problems while importing a txt file into postgres using php

Question

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

I don't know pgsql, so can't really help you with that, but I would just import it in to sqlite and then sql it over — RoboTamer
– RoboTamer, Commented Jan 15, 2012 at 10:50

Erwin Brandstetter · Accepted Answer · 2022-12-14 07:08:40Z

10

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 execute:

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

edited Dec 14, 2022 at 7:08

answered Jan 15, 2012 at 11:05

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Himanshu Shekhar Over a year ago

thanks Erwin, I tried copying as a superUser(using COPY) and it worked!

Timofey Over a year ago

what is the difference between COPY and \COPY?

Erwin Brandstetter Over a year ago

@Tim: COPY is the SQL command and runs on the server. \copy is the psql command that wraps COPY for use from the client (convenient for local files).

Ricoh · Accepted Answer · 2020-01-06 15:41:36Z

0

I encountered the same problem, but instead of using the the raw psql \copy command, decided to use the Ecto.Adapters.SQL.stream/2 function to read the contents of the file, and Repo.transaction/1 that executes the normal SQL COPY command getting the data from STDIN provided by the stream as described in this blog. I haven't tested the performance of this but i figured it would be a good addition to this answer.

answered Jan 6, 2020 at 15:41

Ricoh

391 silver badge7 bronze badges

Collectives™ on Stack Overflow

Problems while importing a txt file into postgres using php

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related