insert additional column values(not available in file) in postgresql table using COPY

Question

Table columns:

col1, col2, col3, fname, lname

CSV file contains values like:

col1,col2,col3
val1,val2,val3
val1,val2,val3

I want to add data from csv along with additional data i.e. col1,col2,col3,fname,lname in table using COPY functionality of postgres.

Can this be done?

Andomar · Answer 1 · 2015-12-30 13:42:02Z

up vote 0 down vote

You can specify a list of columns in the copy command:

copy YourTable(col1,col2,col3) 
    from '/home/you/yourfile.csv' 
    delimiter ','
    csv header;

The header option tells copy to ignore the first row.

answered yesterday

Andomar

141k20196246

I need to add additional values. These values are not available in the CSV. – ketu b yesterday

You set the other columns after the copy with an update query – Andomar yesterday

Using copy only is not possible? – ketu b yesterday

add a comment |

ketu b · Answer 2 · 2015-12-30 15:42:51Z

I am using following workaround, I have created a shell script to prefix additional column values in the CSV file.

additional_cols="Fred,Solo";#fname,lname
csv_file="filename.csv";
sed -i -e "s/^/$additional_cols\,/" $csv_file

once the file is edited, its easy to import values using COPY of postgres. Use link to COPY documentation.

2 Answers 2