current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 3 down vote favorite
1

I have a csv file with two columns: productId, productName.
My table has the following columns: productId, productName, productprice.

I want to be able to copy this file into a Postgres table using the COPY command and at the same time set a value for a third column: Productprice.

productPrice is same for all rows. I use:

COPY product_master(productId, productName)
FROM 'product.txt' DELIMITERS ',' CSV;

I want to attach a price to all rows.

share|improve this question
    
You want to .. what now? Attect? – Erwin Brandstetter Nov 14 '11 at 14:05
    
You haven't specified how you want to the price to be derived. If it's constant (for now) then Erwin's answer will suffice, but you haven't given us enough to answer accurately. – ocharles Nov 14 '11 at 15:58

2 Answers 2

up vote 2 down vote

The remaining column will be filled in with the default, as the manual tells us:

If there are any columns in the table that are not in the column list, COPY FROM will insert the default values for those columns.

So, if the price is the same for all rows, create a column default. Delete it afterwards, if you don't need it any more.

ALTER TABLE product_master ALTER COLUMN productrice SET DEFAULT 12.5;
COPY ...
ALTER TABLE product_master ALTER COLUMN productrice DROP DEFAULT;
share|improve this answer
up vote 0 down vote

This is more of a high level solution to importing data as such is your question.

There're a few ways to do this, but I normally always use a staging table when moving data into a database system. Such as product_master_stg which can be very generic such as it might have column types all set to text and null or you can get more specific and match the data types to the expected file columns types. After using copy to get the data into staging I write a stored function to extract the data from the staging table and transform and load it into the target table at this point you can calculate your productprice value. This lends itself well to varying requirements with the the main downside being a call to a stored function to get it to your final table, but everything has some cost associated with it so don't consider this a bad cost. If you constantly follow this approach it will scale well and is very logical to use and maintain.

share|improve this answer

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.