Copy from CSV file to a Integer Column

[avenirit]

Hello All,

I have someting like this

Create Table Sample(
column 1 bigint,
column 2 varchar,
column 3 varchar)

Data is

, test1,test2 (note that first column is null)

Copy command gives me error saying that invalid value for column integer, my copy command is
copy schema.Sample from 'Table1.csv' delimiter ',' null as '' exceptions '/aim/pmmcdicsvfiles/exception.txt' ;

[JimKnicely]

Hi avenirit ,

Welcome to the forums!

Your example works okay when I try:

Code: Select all

dbadmin=> \! cat /usr/home/dbadmin/Table1.csv;
, test1,test2
dbadmin=> create table sample (
dbadmin(>   column1 bigint,
dbadmin(>   column2 varchar,
dbadmin(>   column3 varchar);
CREATE TABLE
dbadmin=> copy Sample from '/usr/home/dbadmin/Table1.csv' delimiter ',' null as '' exceptions '/usr/home/dbadmin/exception.txt';
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> select * from sample;
 column1 | column2 | column3
---------+---------+---------
         |  test1  | test2
(1 row)

Hmm.

[avenirit]

Hi

I figured out the problem

my csv file looked like this

, ,test1 ,test2

Once I deleted the extra whitespace it is working now.

Thanks for your help

[JimKnicely]

No problem!