Skip to content
Advertisement

What is the Array format of pg_copy_from()’s input?

I am attempting to load many CSV files of historical data to a PGSQL server with PHP. I noticed PHP provides a pg_copy_from() function for loading local data into a remote server, but the exact format of the input is vague.

The documentation says it expects an array of rows, each row being a delimited string of the values in that row. PHP’s file() function returns an array in a format as described, but feeding it into pg_copy_from() returns an error of:

$rows = file('path/to/data.csv', FILE_IGNORE_NEW_LINES); // Same result without ignore_new_lines
pg_copy_from($db, 'dest_table', $rows, ',');

'PHP Warning:  pg_copy_from(): Copy command failed: ERROR:  invalid input syntax for integer: ""col_2""'

I checked the array, $rows, and it’s of a format like this:

[index]=>""foo",3,7,"bar",500,"z""

Where all string values and the string itself are in double quotes. I assume it’s trying to load the entire string into the 1st column of my table, hence the error that col_2 is unable to load (cause there’s nothing to load).

Are my assumptions about the array wrong or does the delimited string need a different format (if so, how)?

Thanks for any help/tips!

Advertisement

Answer

As noted in comments by @Wolph – just remove first element of your $rows array, because it contains headers. To remove first element of array array_shift can be used without reversing the array:

$rows = file('path/to/data.csv', FILE_IGNORE_NEW_LINES);
array_shift($rows);
pg_copy_from($db, 'dest_table', $rows, ',');
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement