Equivalent of ON CONFLICT DO NOTHING

Question

i’m having a hard time understanding possibility of achieving “on conflict do nothing” behavior with DBT. I have a predefined PostgreSQL table with an unique two-column index - CREATE UNIQUE INDEX my_index ON my_table USING btree (col1, col2);. Now i want to make an incremental model on the top of this table. The problem is that I want to ignore all insert conflict while building the model. With PostgreSQL it looks smth like insert into table (....) values(....) on conflict(col1,col2) do nothing;

I’ve seen ‘unique_key’ and ‘incremental_strategy’ options, but they are not much of a help.

Is there any way?

Albin Albin · Accepted Answer · 2022-01-14T10:56:25

As PostgreSQL only supports the default incremental_strategy = 'merge' you do not have to specify this in your setup.

I am not completely sure if I understand your question correctly. But first, you need to specify your incremental model in the config settings.

Secondly, as far as I understand your issue, you can specify your concern in the pre_hook configurations as shown below:

{{

config(    
  materialized = 'incremental',   
  unique_key = 'id',
  pre_hook="""
    {% if is_incremental() %}
      pre_hook="<sql-statement>" | ["<sql-statement>"]
    {% endif %}  
    """
}}

Equivalent of ON CONFLICT DO NOTHING

1 Answers