Thread: [NOVICE] Setting a DEFAULT when NULL is inserted
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 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
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
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
Am 12.07.2017 um 12:32 schrieb Andreas Kretschmer: > i would suggest a TRIGGER on Insert. as a short example: test=# CREATE TABLE my_table ( id integer, insertion_datetime timestamp DEFAULT now() ); CREATE TABLE test=*# create or replace function set_timestamp() returns trigger as $$begin new.insertion_datetime := now(); return new; end; $$language plpgsql; CREATE FUNCTION test=*# create trigger trg_set_timestamp before insert on my_table for each row when (new.insertion_datetime is null) execute procedure set_timestamp(); CREATE TRIGGER test=*# commit; COMMIT test=# insert into my_table (id) values (1); INSERT 0 1 test=*# commit; COMMIT test=# insert into my_table (id, insertion_datetime) values (2, NULL); INSERT 0 1 test=*# commit; COMMIT test=# select * from my_table ; id | insertion_datetime ----+---------------------------- 1 | 2017-07-12 15:44:57.946964 2 | 2017-07-12 15:45:05.083043 (2 Zeilen) test=*# note that the trigger fires only if the new.insertion_datetime is null (a so called conditional trigger) Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com