From: | "Chris Hoover" <revoohc(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Why is default value not working on insert? |
Date: | 2006-08-08 20:41:04 |
Message-ID: | 1d219a6f0608081341l5b04b035n313b6af94dede621@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ian Barwick | 2006-08-08 20:53:19 | Re: Why is default value not working on insert? |
Previous Message | Tom Lane | 2006-08-08 20:29:32 | Re: DISTINCT to get distinct *substrings*? |