Re: Why is default value not working on insert?

Chris Hoover wrote:
> I have the following table:
>
> CREATE TABLE code_source
> (
>   csn_src int4 NOT NULL,
>   csn_type varchar(8) NOT NULL,
>   cs_code varchar(15) NOT NULL,
>   cs_desc_short varchar(30),
>   cs_desc_long text,
>   cs_remarks varchar(20),
>   cs_work_flag char(1),
>   cs_status char(1),
>   cs_manual_key bool NOT NULL DEFAULT false,
>   cs_create timestamp NOT NULL DEFAULT now(),
>   cs_live date NOT NULL,
>   cs_last_mod timestamp,
>   cs_expire date,
>   cs_last_mod_user varchar(12),
>   CONSTRAINT code_source_pkey PRIMARY KEY (csn_src, csn_type, cs_code),
>   CONSTRAINT code_source_fkey FOREIGN KEY (csn_src, csn_type)
>       REFERENCES code_source_name (csn_src, csn_type) MATCH SIMPLE
>       ON UPDATE CASCADE ON DELETE RESTRICT
> )
> WITHOUT OIDS;
>
> As you can see, cs_create is set to not null with a default value of
> now().
>
> However, when I run the following insert, it errors stating cs_create
> can not be null.  Why is the default not working?
>
> insert into code_source (csn_src, csn_type, cs_code, cs_desc_short,
> cs_desc_long, cs_remarks, cs_work_flag, cs_status, cs_manual_key,
> cs_create, cs_live, cs_last_mod, cs_expire, cs_last_mod_user)
> values ('132', 'CODE', '49', 'Some Code', null, 'NEWCODE', null, null,
> false, to_date(null,'yyyymmdd'), to_date('19000101','yyyymmdd'),
> to_date('20040318','yyyymmdd'), to_date('99991231','yyyymmdd'),
> 'MSBIUSERID');
> ERROR:  null value in column "cs_create" violates not-null constraint
>
> The reason for the null being passed to to_date is this is on of almot
> 90k lines of data we are trying to load, and the script was built to
> generate this code.  Since to_date(null,'yyyymmdd') returns a null,
> why is the default not working?
>
> Chris
>
> PG 8.1.3
> RH AS 4
>
Defaults are set when you do not specify a value, not when you try to
set a value that violates a constraint (which is what NOT NULL  is...).
You need to have the script that generates this insert query leave that
field out.



--
erik jones <erik@myemma.com>
software development
emma(r)