How to create an automated column for update timestamp in my DB?

In Sources, we ask you to select which column states when a record has been updated in the external system you plugged as a Source.

It is extremely important so that we make sure:

We always deliver the most recent version of each record to the Dataset.

We don’t scan your entire DB each time we retrieve data (you don’t want to overload your DB nor pay too much computing power).

If you are using a DB source and you don’t have such a column in your table, here is an example of how to create one (for PostgreSQL):

In your table, create a column to store the current timestamp when a record is created or updated.

In this example the column will be updated_at, please make sure you don’t already have another column with the same name.

plain text
ALTER TABLE "{{schemaName}}"."{{tableName}}"
    ADD COLUMN IF NOT EXISTS "updated_at" timestamp default now();

Please replace {{schemaName}} and {{tableName}} with the appropriate values.

Create the function that will set the current timestamp to the updated_at column.

plain text
create or replace function "{{schemaName}}"."octo_set_updated_at"()
    returns trigger
    language plpgsql
as
$$
BEGIN
    NEW."updated_at" = NOW();
    RETURN NEW;
END;
$$;

Please replace {{schemaName}} with the appropriate value.

Create the trigger that will execute the function each time a record is created or updated in your table.

plain text
CREATE TRIGGER "octo_upsert_trigger_{{tableName}}"
    BEFORE INSERT OR UPDATE
    ON "{{schemaName}}"."{{tableName}}"
    FOR EACH ROW
EXECUTE PROCEDURE "{{schemaName}}"."octo_set_updated_at"()

Please replace {{schemaName}} and {{tableName}} with the appropriate values.

Advanced:

Create columns & triggers for all tables in a schema.

plain text
create or replace function "{{schemaName}}"."octo_set_updated_at"()
    returns trigger
    language plpgsql
as
$$
BEGIN
    NEW."updated_at" = NOW();
    RETURN NEW;
END;
$$;

DO
$$
    DECLARE
        "schema"    text := '{{schemaName}}';
        "tablename" text;
    BEGIN
        FOR "tablename" IN (SELECT "table_name" FROM "information_schema"."tables" WHERE "table_schema" = "schema")
            LOOP
                EXECUTE format('ALTER TABLE "%s"."%s" ADD COLUMN IF NOT EXISTS "updated_at" timestamp default now()', "schema", "tablename");
                EXECUTE format('DROP TRIGGER IF EXISTS "octo_upsert_trigger_%s" on "%s"."%s"', "tablename", "schema", "tablename");
                EXECUTE format('CREATE TRIGGER "octo_upsert_trigger_%s" BEFORE INSERT OR UPDATE ON "%s"."%s" FOR EACH ROW EXECUTE PROCEDURE "%s"."octo_set_updated_at"()', "tablename", "schema", "tablename", "schema");
            END loop;
    END ;
$$ language 'plpgsql';

Please replace {{schemaName}} with the appropriate value.