PostgreSQL Data Sync
This comprehensive document will walk you through the step-by-step configuration process for PostgreSQL data sync and address specific use cases tailored to PostgreSQL .
Standard Data Sync functionality
Please refer to Data Sync - Getting Started for the standard data sync functionality. The getting started document covers usages of data sync features valid for all integrations.
Configuring PostgreSQL Data Sync
1. Click on "Add new" button
2. Pick the app that you want to sync with PostgreSQL
3. Select the PostgreSQL from the dropdown
4. Select the schema from the dropdown
5. Click on "Next" button
6. Delete any tables that you don't want to sync. If the table you want to sync is not displayed, add it using "Add table" button
7. Click on "Next" button
8. Select the 'Last updated column' from dropdown
9. Click on "Next" button
10. Mark any of the field as unique
11. Click on "Confirm" button
12. Click on "Save" button
13. Click on "Start Sync"
PostgreSQL - Last Updated Column
Timestamp with timezone data type for the last updated column.The "last updated" column is required for Byteline data sync and other sync tools. So, ensure your data sync works seamlessly by setting up the essential "last updated" column. This column is crucial for identifying modified records that need to be synced. You will configure this column when setting up the data sync, as shown in the below screenshot.
Typically, a timestamp column serves this purpose, but PostgreSQL doesn't automatically update it when other row values change. The solution lies in implementing a PostgreSQL trigger.
To add this trigger to your PostgreSQL database, follow the steps below. Make sure to execute these instructions after connecting to the PostgreSQL database terminal:
a. Create the last updated trigger
my_last_updated_column with your "last updated" column name.CREATE FUNCTION update_updated_on_user_task()
RETURNS TRIGGER AS $$
BEGIN
NEW.my_last_updated_column = now();
RETURN NEW;
END;
$$ language 'plpgsql';
b. Associate this trigger with the table
my_table with your table name.CREATE TRIGGER update_user_task_updated_on
BEFORE UPDATE
ON
my_table
FOR EACH ROW
EXECUTE PROCEDURE update_updated_on_user_task();
