postgres/scd2.sql

{{- /* scd2.sql — two-statement SCD2 MERGE for PostgreSQL (SCD Type 2) */ -}}
{{- /* Split into UPDATE then INSERT because PostgreSQL CTE sub-statements see the same   */ -}}
{{- /* snapshot — the close_step UPDATE is not visible to the INSERT within a single CTE.  */ -}}
{{- /* With NULLS NOT DISTINCT on UNIQUE(hpk, valid_to), the INSERT would fail trying to   */ -}}
{{- /* insert (hpk, NULL) while the old active row still appears as (hpk, NULL) in the     */ -}}
{{- /* snapshot, even though close_step already set valid_to to a timestamp.                */ -}}
{{ template "compare_prepare" . -}}
UPDATE "{{ .HistorySchema }}"."{{ .HistoryTable }}" hst
SET    amf_valid_to   = {{ template "valid_from_expr" . }},
       amf_updated_at = NOW()
FROM   {{ template "compare_rows" . }} stg
WHERE  stg.amf_cdc_action IN (2, 3)
  AND  hst.amf_hash_primary_key = stg.amf_hash_primary_key
  AND  hst.amf_valid_to IS NULL;

INSERT INTO "{{ .HistorySchema }}"."{{ .HistoryTable }}" (
    {{ template "business_col_list" . }}, {{ template "amf_system_cols" . }}
)
SELECT {{ template "business_col_list" . }}, {{ template "amf_system_vals" . }}
FROM   {{ template "compare_rows" . }} stg
WHERE  stg.amf_cdc_action IN (1, 2)
{{- template "compare_release" . }}