PostgreSQL COPY command with csv file

Question

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.

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
– ocharles, Commented Nov 14, 2011 at 15:58

Erwin Brandstetter · Accepted Answer · 2011-11-14 14:08:45Z

3

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;

edited Nov 14, 2011 at 14:08

answered Nov 14, 2011 at 13:54

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kuberchaun · Accepted Answer · 2011-11-14 19:57:29Z

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.

Collectives™ on Stack Overflow

PostgreSQL COPY command with csv file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related