Skip site navigation (1) Skip section navigation (2)

invalid input syntax for integer: "NULL"

From: Kumar S <ps_postgres(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: invalid input syntax for integer: "NULL"
Date: 2004-10-15 21:55:13
Message-ID: 20041015215513.31540.qmail@web51410.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi All, 
  My data has a columns with numbers and some spaces.
That means no number is predicted for that row and
column. 

In my table definition I defined INTEGER. When I
uploaded my data it did not take empty SPACE as
character type. Following a friends suggestion
(experienced in ORACLE) I made those empty spaces as
NULL and then I tried to input my insert statement. 

I get ERROR:
ERROR:  invalid input syntax for integer: "NULL"

I might want to perform calculations on this column
and if null is not accepted for INTEGER data type,
then the only way for me is to make it varchar. BUT I
CANNOT perform caclulations on this column. It is sad.


Is there a way to get going.  Please help me. 

Below is my table definition and INSERT STATEMENT. 
Many columns has empty spaces. I cannot put Zero's or
any other negative values because these are
statistical data columns and zero and any other number
mean something and might lead to erratic conclusions. 
SORRY these sql statements and table definitions are
big.


Thank you for your help in advance.

-Kumar

TABLE: 
create table cdna_exp(
        cdna_id serial PRIMARY KEY,
        cdna_exp_id integer REFERENCES
experiment(exp_id),
        cdna_exp_chip_id integer REFERENCES
exp_chip(exp_chip_id),
        cdna_sample_id integer REFERENCES
sample(sample_id),
        cdna_sequence_annotation_id integer REFERENCES
sequence_annotation(sequence_annotation_id),
        cdna_SPOT integer,
        cdna_NAME varchar(100),
        cdna_Clone_ID varchar(1400),
        cdna_Gene_Symbol varchar(100),
        cdna_Gene_Name varchar(14000),
        cdna_Cluster_ID varchar(1400),
        cdna_Accession varchar(1400),
        cdna_Preferred_name varchar(14000),
        cdna_SUID integer,
        cdna_CH1I_MEAN integer,
        cdna_CH1D_MEDIAN varchar(100),
        cdna_CH1I_MEDIAN varchar(100),
        cdna_CH1_PER_SAT varchar(100),
        cdna_CH1I_SD varchar(100),
        cdna_CH1B_MEAN integer,
        cdna_CH1B_MEDIAN integer,
        cdna_CH1B_SD varchar(100),
        cdna_CH1D_MEAN integer,
        cdna_CH2I_MEAN integer,
        cdna_CH2D_MEAN integer,
        cdna_CH2D_MEDIAN varchar(100),
        cdna_CH2I_MEDIAN varchar(100),
        cdna_CH2_PER_SAT varchar(100),
        cdna_CH2I_SD varchar(100),
        cdna_CH2B_MEAN integer,
        cdna_CH2B_MEDIAN integer,
        cdna_CH2B_SD varchar(100),
        cdna_CH2BN_MEDIAN integer,
        cdna_CH2DN_MEAN integer,
        cdna_CH2IN_MEAN integer,
        cdna_CH2DN_MEDIAN varchar(100),
        cdna_CH2IN_MEDIAN varchar(100),
        cdna_CORR REAL,
        cdna_DIAMETER varchar(100),
        cdna_FLAG integer,
        cdna_LOG_RAT2N_MEAN REAL,
        cdna_LOG_RAT2N_MEDIAN varchar(100),
        cdna_PIX_RAT2_MEAN integer,
        cdna_PIX_RAT2_MEDIAN varchar(100),
        cdna_PERGTBCH1I_1SD integer,
        cdna_PERGTBCH1I_2SD integer,
        cdna_PERGTBCH2I_1SD integer,
        cdna_PERGTBCH2I_2SD varchar(100),
        cdna_RAT1_MEAN integer,
        cdna_RAT1N_MEAN integer,
        cdna_RAT2_MEAN integer,
        cdna_RAT2_MEDIAN varchar(100),
        cdna_RAT2_SD integer,
        cdna_RAT2N_MEAN integer,
        cdna_RAT2N_MEDIAN integer,
        cdna_REGR integer,
        cdna_SUM_MEAN integer,
        cdna_SUM_MEDIAN varchar(100),
        cdna_TOT_BPIX integer,
        cdna_TOT_SPIX integer,
        cdna_X_COORD varchar(100),
        cdna_Y_COORD varchar(100),
        cdna_TOP integer,
        cdna_BOT integer,
        cdna_LEFT integer,
        cdna_RIGHT integer,
        cdna_SECTOR integer,
        cdna_SECTORROW integer,
        cdna_SECTORCOL integer,
        cdna_SOURCE varchar(100),
        cdna_PLATE integer,
        cdna_PROW varchar(100),
        cdna_PCOL integer,
        cdna_FAILED integer,
        cdna_IS_VERIFIED varchar(100),
        cdna_IS_CONTAMINATED varchar(100),
        cdna_LUID varchar(100)
);






INSERT STATEMENT:

INSERT INTO cdna_exp
(cdna_SPOT,cdna_NAME,cdna_Clone_ID,cdna_Gene_Symbol,cdna_Gene_Name,cdna_Cluster_ID,cdna_Accession,cdna_Preferred_name,cdna_SUID,cdna_CH1I_MEAN,cdna_CH1D_MEDIAN,cdna_CH1I_MEDIAN,cdna_CH1_PER_SAT,cdna_CH1I_SD,cdna_CH1B_MEAN,cdna_CH1B_MEDIAN,cdna_CH1B_SD,cdna_CH1D_MEAN,cdna_CH2I_MEAN,cdna_CH2D_MEAN,cdna_CH2D_MEDIAN,cdna_CH2I_MEDIAN,cdna_CH2_PER_SAT,cdna_CH2I_SD,cdna_CH2B_MEAN,cdna_CH2B_MEDIAN,cdna_CH2B_SD,cdna_CH2BN_MEDIAN,cdna_CH2DN_MEAN,cdna_CH2IN_MEAN,cdna_CH2DN_MEDIAN,cdna_CH2IN_MEDIAN,cdna_CORR,cdna_DIAMETER,cdna_FLAG,cdna_LOG_RAT2N_MEAN,cdna_LOG_RAT2N_MEDIAN,cdna_PIX_RAT2_MEAN,cdna_PIX_RAT2_MEDIAN,cdna_PERGTBCH1I_1SD,cdna_PERGTBCH1I_2SD,cdna_PERGTBCH2I_1SD,cdna_PERGTBCH2I_2SD,cdna_RAT1_MEAN,cdna_RAT1N_MEAN,cdna_RAT2_MEAN,cdna_RAT2_MEDIAN,cdna_RAT2_SD,cdna_RAT2N_MEAN,cdna_RAT2N_MEDIAN,cdna_REGR,cdna_SUM_MEAN,cdna_SUM_MEDIAN,cdna_TOT_BPIX,cdna_TOT_SPIX,cdna_X_COORD,cdna_Y_COORD,cdna_TOP,cdna_BOT,cdna_LEFT,cdna_RIGHT,cdna_SECTOR,cdna_SECTORROW,cdna_SECTORCOL,cdna_SOURCE,cdna_
 PLATE,cd
na_PROW,cdna_PCOL,cdna_FAILED,cdna_IS_VERIFIED,cdna_IS_CONTAMINATED,cdna_LUID)
VALUES ( 
'9215','18491','IMAGE:267638','FLJ22004','hypothetical
protein FLJ22004','Hs.108812','N25427','NULL','115416','224','NULL','NULL','NULL','NULL','289','135','NULL','89','348','58','NULL','NULL','NULL','NULL','390','290','NULL','439','88','527','NULL','NULL','0.625','NULL','0','-0.019','NULL','NULL','0.782','34','NULL','23','NULL','1.534','1.013','0.652','NULL','NULL','0.987','NULL','0.6','NULL','NULL','1126','52','NULL','NULL','960','968','996','1004','16','24','23','GF201:96(69H10):384(24P20)','24','P','20','0','Y','U');


		
_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

Responses

pgsql-novice by date

Next:From: Stephan SzaboDate: 2004-10-15 22:03:38
Subject: Re: invalid input syntax for integer: "NULL"
Previous:From: Tom LaneDate: 2004-10-15 21:39:44
Subject: Re: syntax error at or near "Hs" at character 1182

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group