Postgresql – Set field values to newly imported rows in PostgreSQL table with existing data

csvpostgispostgresqlpostgresql-9.1

I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import.

enter image description here

Question: For the newly imported rows, how do I set the values for a specific column (not found in the CSV)? I cant do UPDATE staging.tablename SET colname = 'somename' the way I could when importing into an empty table. Or is this approach wrong?

Best Answer

I'd recommend using the COPY command from psql. You can set a DEFAULT value for a column and omit that value from the COPY command, eg:

\copy tablename(col1,col2,col3) FROM 'thefile.csv' WITH (FORMAT CSV)

Alternately, you can create a new TEMPORARY table in PgAdmin-III wtih just the columns in the CSV, import the CSV into it, and use SQL like this to merge it into the main table:

INSERT INTO realtable (col1, col2, col3, colwithdefault)
SELECT
    col1, col2, col, 'some default value'
FROM tempcsvtable;

You can use this to calculate columns based on expressions, combine and split columns, omit some rows, etc.

Related Question