Re: Setting a DEFAULT when NULL is inserted

Am 12.07.2017 um 12:25 schrieb Jonathan Moules:
> Hi List,
> 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?
>
> Thanks,
> Jonathan

i would suggest a TRIGGER on Insert.

Regards, Andreas.

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com