CODEWITHSUNDEEP
phppostgresqlcsvcopy
mycupoftea
mycupoftea

Reputation: 371

Inserting csv in postgresql with \copy from php

I am trying to insert csv file in postgresql database table from php script.

While using command \copy in psql: 

\copy tablename(col1, col2, col3) FROM 'path/to/the/file.csv' DELIMITERS ',' CSV HEADER;

I don't occur any problem, but while executing same command as query from php I get: Query failed: ERROR: syntax error at or near "\" LINE 1: \copy ...:

   $query = "\copy tablename(col1, col2, col3) FROM 'path/to/the/file.csv' DELIMITERS ',' CSV HEADER";
    $result = pg_query($conn, $query);
    if (!$result) {
      echo "An error occurred.\n";
      exit;
    }

From PostgreSQL docs I read this

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

Maybe if someone can explain this to me, I would understand it better.

I am not a superuser so I understand that I cannot use COPY, however I assumed if I can start \copy from psgl that I can use it as query in PHP also.

EDIT

Example of my CSV

   date,code,name,aut,data,lat,long
    2017-06-08,431, one ,0, 11.1, 22.121,43.220
    2017-06-08,321, two,1, 13.5,32.323,23.232
    2017-06-08,210, three ,1, 19.5,23.432,43.232

TABLE

CREATE TABLE table (
gid serial NOT NULL,
date timestamptz NOT NULL,
code bigint NOT NULL,
name character varying(50),
type boolean NOT NULL,
data smallint NOT NULL,
lat numeric(12,8),
long numeric(12,8),
the_geom geometry,
CONSTRAINT table_pkey PRIMARY KEY (gid)
);

PHP regarding Laurenz Albe answer:

$path = "path/to/the/file.csv";

$csv2array = array();
if (!$fp=fopen($path,"r")) echo "The file could not be opened.<br/>";  
while (( $data = fgetcsv ($fp)) !== FALSE ){
    $data = str_replace(' ', '', $data);
    $data = implode(" ",$data);
    $csv2array[] = $data;
}
print_r($csv2array);

Output:

Array
(
    [0] => 2017-06-08 431 one 0 11.1 22.121 43.220
    [1] => 2017-06-08 321 two 1 13.5 32.323 23.232
    [2] => 2017-06-08 210 three 1 19.5 23.432 43.232
)

Upvotes: 1

Views: 3881

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246888

\copy is not an SQL command and cannot be used with PHP.

You could read the lines into an array and use pg_copy_from() to use COPY ... FROM STDIN with PHP.

For that, you'd have to read the data from the file into an array first.

Upvotes: 2

Related Questions