How can I import CSV data stored in a bytea?

Question

I have a CSV file that can be loaded into a table easily with COPY FROM, but the catch is that the file is stored in the database in a bytea field.

Is the only way to use COPY on this data to write it out to a temporary file, or is there some clever trick, perhaps involving COPY FROM STDIN?

Answer 1

Precaution

The only reason I can see storing a CSV as a bytea is if you created it with COPY TO BINARY. You'll want to check to make sure this isn't binary data. If so the whole question is a off the mark as you don't have to do this and can just feed the bytea to COPY FROM STDIN

Using convert_from

The question is mostly how do you convert a bytea to text. To do that you'll use convert_from. See my answer here for more information on that process.

After you have a method to get the bytea to text you can either,

• Wrap a psql -d myDb -c "SELECT convert_from(col ...) FROM tbl" in a call to COPY FROM STDIN and make use of it.
• Parse it from within the INSERT/UPDATE using something that can process a CSV-row (suggest Text_CSV::XS). Doing it this way will save you from having to call a second a session to read and output in a format that COPY can accept.