Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 11.22 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


create table my_table ( id           serial 
                      , cola         text
                      , when_time    time  default now()::time   
                      ) ;
insert into my_table (cola) 
     ( values ('abc'), ('xyz') );

CREATE TABLE



INSERT 0 2


-- create hashing function
create or replace function time_id_hash( id_in integer, time_in time) 
  returns text
  language sql
  immutable
as $$
    select md5(id_in::text||'-'||time_in::text);
$$; 

CREATE FUNCTION


-- add column
alter table my_table add column hash_id_date text;

ALTER TABLE


-- create trigger function 
create or replace function hash_id_time_trig()
  returns trigger 
  language plpgsql
as $$
begin
  new.hash_id_date =  time_id_hash(new.id, new.when_time);
  return new;
end;
$$;

CREATE FUNCTION


-- create trigger 
create trigger my_table_hash_id_date_biur
  before insert or update of id, when_time
      on my_table
     for each row
         execute procedure hash_id_time_trig();

CREATE TRIGGER


insert into my_table (cola) 
     ( values ('def'), ('xyz') );

INSERT 0 2


select * from my_table;


id
cola
when_time
hash_id_date


1
abc
13:04:27.342301
null

2
xyz
13:04:27.342301
null

3
def
13:04:27.391355
cdf61d011e04a9aebf693125ecb863ff

4
xyz
13:04:27.391355
a7315d64e1dcaf5223cefa41fab73264



SELECT 4


update my_table 
   set hash_id_date = time_id_hash(id, when_time);

UPDATE 4


select * from my_table;


id
cola
when_time
hash_id_date


1
abc
13:04:27.342301
e9e467dcc710d4d74d014d8319ebee6a

2
xyz
13:04:27.342301
ba7e7cd02938b5caba2883332876d1d8

3
def
13:04:27.391355
cdf61d011e04a9aebf693125ecb863ff

4
xyz
13:04:27.391355
a7315d64e1dcaf5223cefa41fab73264



SELECT 4


alter table my_table alter column hash_id_date set not null;

ALTER TABLE

id	cola	when_time	hash_id_date
1	abc	13:04:27.342301	null
2	xyz	13:04:27.342301	null
3	def	13:04:27.391355	cdf61d011e04a9aebf693125ecb863ff
4	xyz	13:04:27.391355	a7315d64e1dcaf5223cefa41fab73264

id	cola	when_time	hash_id_date
1	abc	13:04:27.342301	e9e467dcc710d4d74d014d8319ebee6a
2	xyz	13:04:27.342301	ba7e7cd02938b5caba2883332876d1d8
3	def	13:04:27.391355	cdf61d011e04a9aebf693125ecb863ff
4	xyz	13:04:27.391355	a7315d64e1dcaf5223cefa41fab73264