3

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
NAC
  • 135
  • 2
  • 11

2 Answers2

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;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

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.

Kuberchaun
  • 29,160
  • 7
  • 51
  • 59