Updating partitioned tables in PostgreSQL?

Question

PostgreSQL

The task was to split a large table in PostgreSQL into partitions by date. I created tables, indexes and restrictions and everything is clear with INSERT - we hang up the trigger and select the partition depending on the date.
And how to be with the trigger on UPDATE of record? For some reason, the network is silent about it, maybe it’s something I don’t want. Is this even possible and how? Throw an example or dock at me, please.

Answer 1

An atypical task, usually data is not dragged from partition to partition.
However, there is nothing complicated. You do BEFORE UPDATE FOR EACH ROW the trigger on child tables. When updating on the parent table, an attempt will automatically be made to update the corresponding row of the child table and the trigger will be called. The trigger function must check whether the data should remain in the current partition (and in this case, do not touch anything) or, accordingly, delete it from the current one and transfer it to the desired one. Short example:

-- ключ нужен, чтобы точно идентифицировать строку таблицы
create table t(id int primary key);

-- партиции, без триггеров на INSERT
create table t1(primary key (id), check(id > 0 and id <= 10)) inherits(t);
create table t2(primary key (id), check(id > 10 and id <= 20)) inherits(t);

insert into t1 values (1), (4);
insert into t2 values (15);

test=# select * from t;
 id
----
  1
  4
 15

test=# select * from t1;
 id
----
  1
  4
 
test=# select * from t2;
 id
----
 15


create or replace function t_tg_proc() returns trigger as $$
begin
  raise notice 't_tg_proc called';
  if TG_TABLE_NAME = 't1' and NEW.id > 10 then
    delete from t1 where id = OLD.id; -- без primary key можно удалить лишнего
    insert into t2 values (NEW.*);
    return null; -- проигнорировать UPDATE
  end if;
  
  if TG_TABLE_NAME = 't2' and NEW.id <= 10 then
    delete from t2 where id = OLD.id;
    insert into t1 values (NEW.*);
    return null; -- проигнорировать UPDATE
  end if;
  
  return new; -- нормальный UPDATE
end $$ language plpgsql;

create trigger t1_tg before update on t1 for each row execute procedure t_tg_proc();
create trigger t2_tg before update on t2 for each row execute procedure t_tg_proc();

-- обычный UPDATE
update t set id = 5 where id = 4;
ЗАМЕЧАНИЕ:  t_tg_proc called
UPDATE 1

select * from t;
 id
----
  1
  5
 15
 
-- необычный UPDATE
update t set id = 11 where id = 1;
ЗАМЕЧАНИЕ:  t_tg_proc called
UPDATE 0

select * from t;
 id
----
  5
 15
 11
 
test=# select * from t1;
 id
----
  5
 
test=# select * from t2;
 id
----
 15
 11

Answer 2

And what difficulties to hang up the trigger on an update? CREATE TRIGGER **** AFTER INSERT OR UPDATE OR DELETE ON ***