Postgres trigger DateUpdated failed

Question

I have a column named DTUpdated of type timestamp with time zone.

I created a function to automatically update that column with every modification:

CREATE OR REPLACE FUNCTION public."RowModifiedFunction"()
    RETURNS trigger AS $$

BEGIN
    NEW.DTUpdated = clock_timestamp();  
    RETURN NEW;
END;

and apply it with a trigger in that table

CREATE TRIGGER "RowModifiedTrigger"
    BEFORE UPDATE 
    ON public."Departments"
    FOR EACH ROW
    EXECUTE PROCEDURE public."RowModifiedFunction"();

But when I modify a column I get the error:

"Record new has no field dtupdated" (in lowercase).

Jasen · Accepted Answer · 2017-08-25 10:29:45Z

2

Postgresql requires "these quotes" on any name that contains uppercase.

CREATE OR REPLACE FUNCTION public."RowModifiedFunction"()
    RETURNS trigger AS $$

BEGIN
    NEW."DTUpdated" = clock_timestamp();  
    RETURN NEW;
END;

answered Aug 25, 2017 at 10:29

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jasen Over a year ago

it's best to just use lowercase for everything, then you don't need quotes :)

Vao Tsun · Accepted Answer · 2017-08-25 10:30:51Z

0

if you column is case sensitive, use double quotes:

NEW."DTUpdated" = clock_timestamp();

NB: usually setting default value to now() instead of such trigger. The only case you need trigger if you want it to fire on specific modifications only

answered Aug 25, 2017 at 10:30

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Postgres trigger DateUpdated failed

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related