Logical Replication - Should Destination Table Columns Be Defined With Default Value

[Avi Weinberg <AviW@xxxxxxxxx>]

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.