From: | Jonathan Moules <jonathan-lists(at)lightpear(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Setting a DEFAULT when NULL is inserted |
Date: | 2017-07-12 10:25:06 |
Message-ID: | 15d3653c208.fdb0a636212344.106420915297522733@lightpear.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2017-07-12 10:32:24 | Re: Setting a DEFAULT when NULL is inserted |
Previous Message | David G. Johnston | 2017-07-11 19:06:17 | Re: COPY from temp table to main table insted of INSERT INTO |