Inserting a CSV file into PostgreSQL table using bash script

Question

I'm trying to insert a CSV file into a PSQL table using a bash script. My first aim is parsing the CSV file then insert the datas, row by row. COPY command is not convenient for me. I've used the following script but it doesn't worked.

Edit: I didn't create the table by the way. Should I?

#!/bin/sh             
    while IFS=, read col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12
    do
        echo "INSERT INTO table_name ("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l") VALUES ('$col1','$col2','$col3','$col4','$col5','$col6','$col7','$col8','$col9','$col10','$col11','$col12');"
    done < ppr.csv | sudo psql -U pg_user -d test;

Could you provide more informations about your problem ? You say "It does not work", so what happen when it does not work ? Do you have any errors ? — Martin Paucot
– Martin Paucot, Commented Feb 12, 2020 at 11:09
ERROR: relation "table_name" does not exist at character 13 LINE 1: INSERT INTO table_name (a, b, c, d, e, f, g, h, i, j, k, l) VALUES .. — Bertug
– Bertug, Commented Feb 12, 2020 at 11:32
Try using single quotes for the field names or escaping the double ones. — Poshi
– Poshi, Commented Feb 12, 2020 at 11:34
@Poshi - ERROR: syntax error at or near "'a'" at character 18 after single quotes. — Bertug
– Bertug, Commented Feb 12, 2020 at 11:36

Laurenz Albe · Accepted Answer · 2020-02-12 15:35:17Z

1

Don't try to parse CSV with bash, that would be quite hard to get right.

You should use file_fdw to define a foreign table in the database. That allows you to access the data from the database without actually loading it. In a way, you turn PostgreSQL into a CSV parser.

Then you can select from that foreign table like from a normal table and for example insert parts of it into a proper PostgreSQL table.

answered Feb 12, 2020 at 15:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matias Barrios · Accepted Answer · 2020-02-12 12:40:22Z

0

You should escape inner double quotation marks :

#!/bin/sh             
while IFS=, read col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12
do
    echo "INSERT INTO table_name (\"a\", \"b\", \"c\", \"d\", \"e\", \"f\", \"g\", \"h\", \"i\", \"j\", \"k\", \"l\") VALUES ('$col1','$col2','$col3','$col4','$col5','$col6','$col7','$col8','$col9','$col10','$col11','$col12');"
done < ppr.csv | sudo psql -U pg_user -d test;

answered Feb 12, 2020 at 12:40

Matias Barrios

5,0864 gold badges31 silver badges71 bronze badges

3 Comments

Bertug Over a year ago

ERROR: relation "table_name" does not exist LINE 1: INSERT INTO table_name. Should I create the table and columns first or ?

Matias Barrios Over a year ago

@Bertug Yes you should

Laurenz Albe Over a year ago

Your solution won't work if any of the values contain a single quote. Also, that does not account for quoted fields.

Collectives™ on Stack Overflow

Inserting a CSV file into PostgreSQL table using bash script

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related