Re: [NOVICE] Setting a DEFAULT when NULL is inserted

Jonathan Moules wrote:
>     I want a column to get a default value of now() (the timestamp) when a row is
> inserted. But it's not working because the application that's doing the insertion appears
> to be putting a null value in, and DEFAULT isn't converting this to now().
> 
>     Example:
>     I have a table with a simple definition:
> 
>     CREATE TABLE my_table
> (
> id integer,
> insertion_datetime timestamp DEFAULT now()
> );
> 
> If I do:
> insert into my_table (id) values (1);
> 
> Then the insertion_datetime gets a value of now() correctly.
> 
> But if I do this:
> insert into my_table (id, insertion_datetime) values (1, null);
> 
> Then the insertion_datetime gets a value of NULL rather than the desired now().
> 
> I can see why this happens (I have explicitly told it to put NULL in there after all), but
> it's not the desired behaviour. I can't change the application, so how do I get any
> inserted NULL values to become the DEFAULT now()? Am I going to need to use a trigger?

There are only two options:

- Fix the application so that it does not insert that column or uses the
  key word DEFAULT when it inserts it.

- Write a BEFORE trigger that sets the column.

The first solution is better.

Yours,
Laurenz Albe