Обсуждение: Why is default value not working on insert?
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
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
2006/8/8, Chris Hoover <revoohc@gmail.com>: (...) > 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? Because you're trying to explicitly insert a NULL into a column which is specified as NOT NULL. (If you can't change the script to output DEFAULT or the explicit default value, the best workaraound would be to create a trigger which converts any attempt to insert a NULL value into that column to the intended default value). Ian Barwick
On Tue, Aug 08, 2006 at 04:41:04PM -0400, Chris Hoover wrote: > I have the following table: <snip> > 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? If you try to insert a NULL into a not null column, you get an error. To trigger the default you either need to omit the column from the insert statement, or use the DEFAULT keyword. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
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)