Incremental merge on Postgres

Help
,
1

The problem I’m having

I have a table core.table with historical data that is to be updated from a table stage.table.
I want to merge the new data into the historical table. So it should update existing records or add new ones.
Postgres only seems to support delete+insert and append. Since I also want to update existing records, I need to use delete+insert. (Also, I do not understand why merge is not supported, since it is an available command for postgres??)
However, the problem is that is seems to simply delete ALL records in the historical table, instead of just the those that are to be updated…

I am not doing a --full-refresh every time…

The context of why I’m trying to do this

Postgres 15
dbt 1.3.1

Some example code or error messages

{{
    config(
        materialized='incremental',
        unique_key="'GUID'", 
        on_schema_change='fail'
    )
}}

SELECT * FROM {{ source('stage', 'table') }}
2

To your minor question: merge command is new to Postgres as of v15.0 (October 2022). I don’t see an enhancement request to add merge incremental strategy to Postgres in the open dbt enhancement requests, but that would be an excellent submission.

To your major question: do you see the “delete all records” behavior each time you run your model?

Note: @Owen originally posted this reply in Slack. It might not have transferred perfectly.

1 Like
3

Hi a_slack_user!
thank you for your fast answer. Yes, I tested it by staging different subsets of data and always only this subset ends up in the core table. So it basically behaves like a regular table materialization.