Обсуждение: [NOVICE] 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

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