通过网页上的php将CSV文件导入postgres

Question

Hi I'm trying to build a web interface that will allow people to import the contents of a csv file into a postgresql database. Below is the php component of what I'm trying to do, however when I run I just get "There was a problem uploading your data:" from my die command but with no other error.

For this example, I have hardcoded a value for the csv file, however the intent is for the user to point at a csv file on their machine or network location.

<?php

if ($_POST['submit']) {
// attempt a connection
$dbh = pg_connect("host=localhost dbname=blah user=user password=pass123");
if (!$dbh) {
die("Error in connection: " . pg_last_error());
}
// execute query
$sql = "COPY personaldetails(name, email, postaladdress, phone, username, password) FROM '../Test/Book1.csv' WITH DELIMITER ',' CSV";

$result = pg_query($dbh, $sql);
if (!$result) {
die("There was a problem uploading you data: " . pg_last_error());
}

echo "Data successfully inserted!";

// free memory
pg_free_result($result);

// close connection
pg_close($dbh);
}   

?>

I appreciate any help that can be offered.

Answer 1

COPY FROM file must be run by a superuser account and this restriction makes it generally unsuitable for web usage

Excerpt from the documentation:

COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

But PHP provides support for COPY FROM stdin which does not have this restriction. This example from php documentation shows how it's done:

  $conn = pg_pconnect("dbname=foo");
  pg_query($conn, "create table bar (a int4, b char(16), d float8)");
  pg_query($conn, "copy bar from stdin");
  pg_put_line($conn, "3\thello world\t4.5
");
  pg_put_line($conn, "4\tgoodbye world\t7.11
");
  pg_put_line($conn, "\\.
");
  pg_end_copy($conn);

In the case of a file, you'll need to open the file with php functions and feed it line by line to the postgres connection with pg_put_line()