Import CSV to postgresql (Zend/PHP)

Question

I want to load test data into a table in my postgresql database.

I am doing this inside my unit test class.

$this->Application_Model_UserMapper->getDbTable()->getAdapter()->query("COPY users FROM '" . APPLICATION_PATH . "/../tests/dbtestdata/users.csv' CSV HEADER NULL AS E'\N'");

and I get an error message that says I must be a super user to copy to and from a file. Does that mean I need to log in as postgres user? I have read that that could cause security issues, so I am not keen on doing that.

Other option seems to be \COPY, but the following doesnt work..

$this->Application_Model_UserMapper->getDbTable()->getAdapter()->query("

  \COPY users FROM '" . APPLICATION_PATH . "/../tests/dbtestdata/users.csv' 
  CSV HEADER NULL AS E'\N'

");

I get a syntax error.

Frank Heikens · Accepted Answer · 2012-07-05 15:30:44Z

1

\copy is used by psql, a PostgreSQL client. The SQL statement COPY is done by the user Postgres, your code needs this user or a different solution.

I really like pg_put_line, then you can read the file in PHP and stream it to PostgreSQL.

answered Jul 5, 2012 at 15:30

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

3 Comments

crazyphoton Over a year ago

Thanks for the tip! I have almost made it work using the code here : php.net/manual/en/function.pg-put-line.php but for some reason, COPY is not recognizing my column and row delimiters (\t and \n respectively). I solved the column delimiter issue by changing it to '|', but I am unable to provide more than 1 row of data. Would you have any suggestions?

crazyphoton Over a year ago

pastebin.com/srwFLdut It is not recognizing the \n so i cannot insert more than 1 line.

crazyphoton Over a year ago

I fixed it by replacing the \n with an actual enter key. Any idea why this would happen? Is it because I am using a mac?

Collectives™ on Stack Overflow

Import CSV to postgresql (Zend/PHP)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related