PHP postgresql pg_copy_from

Question

I have about 200k rows to import into Postgresql database. I found that I should use pg_copy_from function. I tried it, but it should return true or false and it returns null. And of course, it doesn't insert anything into db.

pg_copy_from($this->connection, $table, $data);

I also tried alternative pg_put_line

    pg_query($this->connection, "copy $table from stdin");
    foreach ($data as $row) {
        pg_put_line($this->connection, implode('\t', $row));
    }
    pg_put_line($this->connection, "\\.\n");
    pg_end_copy($this->connection);

It didn't help. It doesn't throw any error and I have no idea what is the problem. Do you have any idea why it returns null or how to solve this problem?

EDIT: my row looks like this

2019-04-01\t8\t3\tH\tA\t0021\t\\N\t\\N\t\\N\t\\N\t15\t\\N\t0\t0\t0\t0

and I have 16 columns ... I can insert the same data using pdo query

Maybe the problem is in your data? Do you have a sample of the $data array? Just the layout and first data row should be enough — Sjoerd Loeve
– Sjoerd Loeve, Commented May 21, 2019 at 13:53

Fernando Mertins · Accepted Answer · 2019-05-21 14:23:36Z

1

I would recommend you to use PostgreSQL tools and not PHP, because it seems to me your main goal is "I have about 200k rows to import into Postgresql database." and not the PHP pg_copy_from() function...

So, my suggestion, run a pg_dump command on terminal and then import your data also on terminal, using the psql command. Examples:

$ pg_dump -U postgres -f mydump.sql -t mytable mydatabase

Then to import run:

$psql -U postgres mydatabase < mydump.sql

Eventually, we come across a PHP function with weird behavior, I remember one time I was trying to use the pg_insert() function I believe with wildcards (? or $ I'm not sure right now) and it didn't work, so I gave up and used the pg_query() function instead. Maybe you have a similar problem.

answered May 21, 2019 at 14:23

Fernando Mertins

1865 bronze badges

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

1 Comment

Matej Vrzala M4 Over a year ago

Thanks. I need to use PHP code to get the data, so it's preferable to stay in PHP. I found out there is pg_last_error() and debugged it. You were right it was a problem with the format. I changed the delimiter to | and it works!

Collectives™ on Stack Overflow

PHP postgresql pg_copy_from

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related