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...
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!
1 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, ',');
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
