[GENERAL] Why is default value not working on insert?

2006-08-08 Thread Chris Hoover 
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,'mmdd'), to_date('19000101','mmdd'), to_date('20040318','mmdd'), to_date('1231','mmdd'), 'MSBIUSERID');
ERROR: null value in column cs_create violates not-null constraintThe 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,'mmdd') returns a null, why is the default not working?
ChrisPG 8.1.3RH AS 4

Re: [GENERAL] Why is default value not working on insert?

2006-08-08 Thread Ian Barwick 

2006/8/8, Chris Hoover [EMAIL PROTECTED]:
(...)

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,'mmdd') 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Why is default value not working on insert?

2006-08-08 Thread Martijn van Oosterhout 
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,'mmdd') 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.


signature.asc
Description: Digital signature

Re: [GENERAL] Why is default value not working on insert?

2006-08-08 Thread Erik Jones 

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,'mmdd'), to_date('19000101','mmdd'), 
to_date('20040318','mmdd'), to_date('1231','mmdd'), 
'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,'mmdd') 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 [EMAIL PROTECTED]
software development
emma(r)


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org