Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.