Soumyadeep Koley
Soumyadeep Koley
Programming language:SQL
2021-02-15 04:42:58
0
Q:

postgres updatet_at field

Jennifer
Jennifer
Code: SQL
2021-02-15 04:46:36 
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();
1
user43908
user43908
Code: SQL
2021-03-23 18:21:10 
-- Step 1: Create the function
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Step 2: Create the table
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()
);

-- Step 3: Create the trigger
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON my_table
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();

-- Step 4: Profit
-- Now we can insert and update rows in the table, and both 
-- the created_at and updated_at columns will be saved correctly :)
0

Tags

Related

New to Communities?

Join the community