5

I've created my first postgres table and i'm trying to work out how to copy a .csv file into the table, whilst simultaneously generating a serial number number in column 1 (ID) which starts at number 1 and counts upwards (i.e. 1, 2 ,3 etc) for each row. I've been using the command:

COPY sales_table FROM '/home/perl_experiments/xmlcsv.txt' (DELIMITER('|'));

This attempts to copy the contents of the csv, but it seems to be placing the first column of data from the .csv into the first column of the table. I have specified the first column of the table to be a bigserial and I assumed this column would automatically fill, while the content of the csv would bypass column 1 and start filling the table from column 2. Obviously this is not the case and I have missed a vital step. Could anyone shed some light on the step I have missed? Here is a summary of the table I have created:

Column  Type   Not Null  Default        
ID      bigint NOT NULL  nextval('"sales_table_Entry        Id_seq"'::regclass)     [pk]        

ProdNo  bigint  

Title   character varying   

url     character varying   

Price   integer
neilH
  • 433
  • 1
  • 8
  • 19

1 Answers1

7

You can specify the individual columns to fill in the COPY command: 

COPY table_name [ ( column [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

(http://www.postgresql.org/docs/9.1/static/sql-copy.html)

Therefore, specify your CSV columns explicitly in the COPY, leaving out the bigserials column.

Guido
  • 4,014
  • 13
  • 22
  • for anyone that needs the actual syntax I used it is COPY sales_table (item_number,title,url,price) FROM '/home/perl_experiments/xmlcsv.txt' (DELIMITER('|')); – neilH Apr 18 '16 at 17:08