Hi Experts,
I'm using logical replication and have the following open issue:
If the table on the publisher side has column with default value, should the
table on the destination (subscriber) be defined with default value as well?
My intuition was not to define it with default value since it gets its values
from publisher, but then I face the following issue that default value given to
existing data when a column is added is not copied to the destination side.
****on source/publisher side ****
create table table1(id int primary key, a int);
insert into table1 select 1,1;
alter table table1 add column b int not null default 1;
select * from table1
output id,a, b:
1 1 1
**** on destination/subscriber side ***
create table table1(id int primary key, a int);
select * from table1
alter table table1 add column b int; -- I purposely defined it without default
value
ALTER SUBSCRIPTION aaa REFRESH PUBLICATION
select * from table1
output id,a, b:
1 1 null
Why the (default) value given to column b for existing row is not synced to the
subscriber. Of course, for new rows the default value is copied to subscriber
table.
Was this done on purpose, that default value for new column is not copied for
existing data? Does this mean that on destination side we must also define the
table with default value?
If instead of the default on the publisher side I do the following it works and
the value is copied to the subscriber.
do $$
begin
alter table table1 add column b int;
update table1 set b = 1;
ALTER TABLE table1 ALTER COLUMN b SET NOT NULL;
end $$;
IMPORTANT - This email and any attachments is intended for the above named
addressee(s), and may contain information which is confidential or privileged.
If you are not the intended recipient, please inform the sender immediately and
delete this email: you should not copy or use this e-mail for any purpose nor
disclose its contents to any person.
