Question 114306 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use CURRENT_TIMESTAMP for a timestamptz field in Postgres?

tl;dr

NEW.updated_at := NOW(); is not working, where updated_at stores a timestamptz value.

I have this trigger function, where the error seems to happen in the first line. The updated_at field stores a timestamptz value, which NOW() should be, but doesn’t seem to work (throws error). I have tried both CURRENT_TIMESTAMP and NOW() and neither seems to work. Does anyone know what might be causing this issue?

BEGIN
  NEW.updated_at := NOW();
  UPDATE public.projects 
  SET updated_at = NEW.updated_at 
  WHERE id = NEW.project_id;
  INSERT INTO public.document_contributor (document_id, contributor)
  VALUES (NEW.id, NEW.created_by)
  ON CONFLICT
  DO NOTHING;
  INSERT INTO public.commits (message, created_by, project_id, document_id, created_at, previous_content, current_content)
  VALUES (NEW.note, NEW.updated_by, NEW.project_id, NEW.id, NEW.updated_at, OLD.content, NEW.content);
  RETURN NEW;
END

Error

Event message

invalid input syntax for type timestamp with time zone: "Invalid Date"

I’ve noticed that the query being made looks like this.

{
  "query" : "n-- source: dashboardn-- user: 201e7b7c-bb29-409f-9e01-65ca849999e6n-- date: 2022-10-09T01:44:28.100Znnupdate public.documents set (created_at,updated_at,note,data) = (select created_at,updated_at,note,data from json_populate_record(null::public.documents, '{"created_at":"Invalid Date","updated_at":"Invalid Date","note":null,"data":[{"type":"title","children":[{"text":"Untitled"}]},{"type":"paragraph","children":[{"text":"Pool 입니다."}]}]}')) where id = 'c5e2348b-9da7-4db3-8d5e-9d669cfbd7cb' returning *;n"
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Turns out the "time" of the trigger function was the issue.

    In the official document, the operation happens BEFORE.

    CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

    But I have done the opposite, which was AFTER an update happens.

    SO when I recreated the function changing from AFTER to BEFORE, it works fine.

    The reason? Well I am guessing that updating the record by RETURN NEW, AFTER the operation finishes violates some rule. (But I am not sure)


  2. 0 
    BEGIN
  NEW.updated_at = NOW();
  UPDATE public.projects 
  SET updated_at = NEW.updated_at 
  WHERE id = NEW.project_id;
  INSERT INTO public.document_contributor (document_id, contributor)
  VALUES (NEW.id, NEW.created_by)
  ON CONFLICT
  DO NOTHING;
  INSERT INTO public.commits (message, created_by, project_id, document_id, created_at, previous_content, current_content)
  VALUES (NEW.note, NEW.updated_by, NEW.project_id, NEW.id, NEW.updated_at, OLD.content, NEW.content);
  RETURN NEW;
END
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search