Thanks David for the reply.
I also thought that default value on the subscriber side are immaterial. However, with the case I showed without having default value on subscriber side it get null when the following occurs:
- Table was created with two columns on publisher and subscriber side
- Data inserted into this table
- A third column is added to table with default value on publisher side, but without default value on subscriber side
- The default value column has value for existing rows on publisher, but null on the subscriber side.
- Doing refresh publication etc. does not help and the column on subscriber side remains with nulls
Your input is most welcome
From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Tuesday, November 9, 2021 5:55 PM
To: Avi Weinberg <AviW@gilat.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Logical Replication - Should Destination Table Columns Be Defined With Default Value
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?
Logical replication has two modes, initial synchronization and ongoing change push. The ongoing change push sends entire rows, when they change, from the publisher to subscriber.
The initial sync happens once, when the subscriber initially subscribes to the publication.
As entire rows are getting sent, defaults on the subscriber are immaterial so far as the published rows are concerned.
If you run a command on the publisher that causes every row to change then of course every row will be published with those new values to the subscriber.
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.