How to update a column only when a row has any changes?

Question

I'm using Python to insert JSON data into a PostgreSQL table and I wanted to update a column automatically when a row is updated.

Table definition is:

CREATE TABLE public.customer_data (
id serial4 NOT NULL,
sno int4 NOT NULL,
org public.org NULL,
cust_nbr int8 NULL,
fdc_customer_number int8 NOT NULL,
gender bpchar(1) NULL DEFAULT NULL::bpchar,
mar_status public.mar_status NULL,
spous_name varchar(40) NULL DEFAULT NULL::character varying,
employer varchar(40) NULL DEFAULT NULL::character varying,
designation varchar(30) NULL DEFAULT NULL::character varying,
c_statement_flag public.c_statement_flag NULL,
c_city_code bpchar(2) NULL DEFAULT NULL::bpchar,
c_marital_status public.c_marital_status NULL,
card_vip int4 NULL,
createdon timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
updatedon timestamp NULL,
CONSTRAINT customer_data_pk PRIMARY KEY (fdc_customer_number));

createdon and updatedon columns should have the same timestamp in case of new inserted row. In case of update, only the updatedon column should be updated automatically. How can I achieve this? Or should this be done from Python?.

I don't understand what you want to achieve. Could you use clearer English or add some examples? — Laurenz Albe, Aug 25 '22 at 07:53
Why not adding the timestamps, when you are inserting/updating ? — Maurice Meyer, Aug 25 '22 at 08:54
I think this is what you're looking for: https://stackoverflow.com/q/36799104/2422776 — Mureinik, Aug 25 '22 at 12:11

score 1 · Answer 1 · answered Aug 25 '22 at 15:47

I use a trigger for this:

CREATE OR REPLACE FUNCTION public.ts_update()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.updatedon := clock_timestamp();
RETURN NEW;
END;
$function$

customer_data_ts_update BEFORE UPDATE ON public.customer_data FOR EACH ROW EXECUTE FUNCTION ts_update()

This example clock_timestamp() which represents wall clock time. For other choices see Current Date/Time. The plus to the trigger approach is that the field will get set no matter what client is updating the row.

How to update a column only when a row has any changes?

1 Answers1

Linked