Selecting data from a BYTEA data type in Postgres that contains CSV data and storing it in a table

Paul Goodier 0
Viewed 10

I have a table ("file_upload") in a postgreSQL (11,8) database, which we use for storing the original CSV file that was used for loading some data to our system (I guess the question of best practices is up for debate here, but for now lets just assume it is). The files are stored in a column ("file") which is of the data type "bytea"

So one row of this table contains

id - file_name - upload_date - uploaded_by - file <-- this being the column in question.

This column then stores the data of a csv file:

item_id;item_type_id;item_date;item_value 11;1;2022-09-22;123.45 12;4;2022-09-20;235.62 13;1;2022-09-21;99.99 14;2;2022-09-19;654.32

What I need to be able to do is query this column, extracrt the data and store it in a temporary table (note: the structure of these csv files are all the same, so the table structure can be pre-defined and does not have to be dynamic or anything).

Any help would be greatly appreciated

bytea data-extraction postgresql
1 Answers

Use

COPY (SELECT file FROM file_upload WHERE id =1)
   TO '/tmp/blob' (FORMAT 'binary');

to re-export the data to a file. Then create the temporary table and use COPY to read them in again. Make sure to use the proper ENCODING.

You can wrap that in a loop that performs this operation for all rows in your table.

Laurenz Albe0
Related