Columns Excluded From a PostgreSQL Publication Show as NULL in the Destination

Issue

During incremental syncs using PostgreSQL logical replication, columns that contain values in the source database appear as NULL in the destination when those columns aren't included in the PostgreSQL publication used by Fivetran.

Environment

• Connector: Amazon RDS for PostgreSQL
• Incremental sync method: Logical replication

Resolution

To resolve this issue:

1. Update the publication to include the missing columns.
   • If you added new columns to a table after adding it to the publication, refresh the table's publication configuration by removing and re-adding the table.
   • To add the entire table to the publication, run:

     ALTER PUBLICATION <publication_name>
     ADD TABLE schema_name.table_name;
     

   • If you use column lists in PostgreSQL 15 or later, specify the columns to include:

     ALTER PUBLICATION <publication_name>
     SET TABLE schema_name.table_name (col1, col2, col3,..);
     

2. Run a historical re-sync to backfill values that were previously set to NULL. To learn how, see How to Trigger Historical Re-Syncs for Fivetran Tables.

Cause

This issue occurs when the affected columns aren't included in the PostgreSQL publication configured for logical replication. Columns may be excluded if they were added after the publication was created or if the publication was defined with a limited set of columns.

During incremental syncs, Fivetran captures only the columns included in the publication. Any columns excluded from the publication are treated as NULL during the merge process.