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 using COPY? If not please suggest workaround.
psql \COPY supports reading from program output. We can modify the file on the fly using cat and sed:
'cat myFile | sed \'s/$/;col1;col2/\''
You can create a temporary table, modify it then copy back real destination table:
# create a temp table with the same structure as dest table, and drop it on commit
CREATE TEMPORARY TABLE temp_dest ON COMMIT DROP AS TABLE dest_table WITH NO DATA;"
COPY temp_dest(col1,col2 ...) FROM '/file/to/data.COPY'
# Do update in temp_dest, only affect the data just copied
UPDATE temp_dest set col_x=x where ...
INSERT INTO dest_table SELECT * FROM temp_dest
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.
copy with an update querycopy only is not possible?
false, and it worked. No need to add the values in csv file before copying it. Thanks.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.