Re: 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