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

Question

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!

I have a different guess. I think your CSV file contains headers and you're trying to set `col_2` as a value for an integer column — Wolph, Aug 18 '20 at 20:06
Your guess is correct! (man I am fried). I just added `array_reverse($rows)`; `array_pop($rows);` And it works! — Optimum, Aug 18 '20 at 20:12
To remove element from the beginning of the array `array_shift` is used. — u_mulder, Aug 18 '20 at 20:59
I'm just avoiding `shift`'s [performance](https://stackoverflow.com/questions/6501160/why-is-pop-faster-than-shift) @Wolph are you OK if I accept mulder's repost of your comment as answer? — Optimum, Aug 19 '20 at 14:11
@optimum yes go ahead. I don't care about the points anyhow :) — Wolph, Aug 19 '20 at 18:10

score 0 · Accepted Answer · answered Aug 18 '20 at 21:01

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, ',');

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

1 Answers1