postgresql automatic timestamp on insert code example
Example 1: postgresql create trigger update timestamp
CREATE OR REPLACE FUNCTION public.set_current_timestamp_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
_new record;
BEGIN
_new := NEW;
_new."updated_at" = NOW();
RETURN _new;
END;
$function$
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON your_table
FOR EACH ROW
EXECUTE FUNCTION set_current_timestamp_updated_at();
Example 2: postgres updatet_at field
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE my_table (
id SERIAL NOT NULL PRIMARY KEY,
content TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON my_table
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();