CODEWITHSUNDEEP
postgresqlcsv
Andurit
Andurit

Reputation: 5762

POSTGRES - COPY from file with seting one value which is not in FILE

I COPY data from my .csv to my postgres table. My command looks like:

 COPY w_spare_part_cze (country, manufacturer, model, submodel, guid, model_options, oen, price, description) FROM '/tmp/SpareParts.csv' WITH CSV HEADER DELIMITER ',' QUOTE '"';

This looks cool, however in my database I have column country but in my .csv I dont have this column at all ( just have manufacturer, model, submodel, guid, model_options,etc...)

As soon as all the values from this IMPORT is going to have same value for country. Is there a way how to SET IT without locally upload it to another table, add new column with default value, dump and upload again to real table.

Thanks for any advise

Upvotes: 0

Views: 80

Answers (1)

klin
klin

Reputation: 121784

Remove country from field list in copy :

COPY w_spare_part_cze (manufacturer, model, submodel, guid, model_options, oen, price, description) 
FROM '/tmp/SpareParts.csv' WITH CSV HEADER DELIMITER ',' QUOTE '"';

and fill the column with desired value:

UPDATE w_spare_part_cze 
SET country = 'Japan';

If the file contains null as country, temporarily drop not null constraint:

ALTER TABLE w_spare_part_cze ALTER country DROP NOT NULL;

COPY w_spare_part_cze (country, manufacturer, model, submodel, guid, model_options, oen, price, description) 
FROM '/tmp/SpareParts.csv' WITH CSV HEADER DELIMITER ',' QUOTE '"';

UPDATE w_spare_part_cze 
SET country = 'Japan';

ALTER TABLE w_spare_part_cze ALTER country SET NOT NULL;

Upvotes: 1

Related Questions