DataTongs for PostgreSQL

From the directory containing your DataTongs.settings.json:

DataTongs

With a custom config file:

DataTongs --ConfigFile:/etc/schemasmith/extract-reference-data.json

With a full PostgreSQL connection string override:

DataTongs --ConnectionString:"Host=myserver;Port=5432;Database=appmain;Username=postgres;Password=secret;"

DataTongs connects to the source database, reads every table listed in the Tables array, serializes each row to JSON in deterministic key order, and writes a sync script plus a sibling .tabledata file for each table. Progress is logged to DataTongs - Progress.log and any errors to DataTongs - Errors.log in the working directory.

Table name

Specify tables in schema.table format. If you omit the schema, public is assumed:

{ "Name": "public.country" }
{ "Name": "products" }
{ "Name": "config.feature_flags" }

DataTongs validates that each table exists in the source database before attempting extraction. Tables that don't exist are skipped with an error message.

Key columns

Key columns define the row-matching predicate — the ON clause of the generated MERGE.

Auto-detection. When KeyColumns is blank, DataTongs queries the table's indexes and selects the primary key if one exists. If there's no primary key, it falls back to the first available unique index. Nullable columns in the detected key are automatically prefixed with * for NULL-safe comparison. This handles the vast majority of tables without any manual configuration.

Manual override. When you specify KeyColumns, DataTongs uses your list instead of auto-detection. Separate multiple columns with commas:

{ "Name": "public.order_line", "KeyColumns": "order_id, line_number" }

Nullable key columns. If a key column allows NULLs, prefix it with *. This generates NULL-safe matching:

{ "Name": "public.mapping", "KeyColumns": "source_code, *target_code" }

The generated ON clause becomes:

ON "Source"."source_code" = "Target"."source_code"
   AND ("Source"."target_code" = "Target"."target_code"
        OR ("Source"."target_code" IS NULL AND "Target"."target_code" IS NULL))

When auto-detection discovers a nullable column in a unique index, it automatically applies the * behavior.

No key available. If a table has no primary key, no unique index, and no KeyColumns configured, DataTongs skips the table with an error message.

Filter

The Filter field accepts a SQL WHERE clause (without the WHERE keyword). It controls two things:

1. Which rows are extracted from the source database.
2. Which target rows are eligible for deletion when MergeDelete is enabled.

{ "Name": "public.feature_flags", "KeyColumns": "flag_name", "Filter": "is_active = true" }

This extracts only active flags and, if MergeDelete is on, only deletes active flags that no longer exist in the source. Inactive flags in the target are untouched.

What DataTongs writes to disk on PostgreSQL is a native MERGE statement with the source data embedded as a jsonb literal and parsed through jsonb_to_recordset. The script is self-contained — no staging tables, no COPY dependencies, no external files. Run it against any compatible database (PostgreSQL 15 or higher, for native MERGE support) and the data arrives.

MERGE with jsonb_to_recordset

MERGE INTO "public"."shippers" AS "Target"
USING (
  SELECT "company_name", "phone", "shipper_id"
    FROM jsonb_to_recordset('[
      {"company_name":"Speedy Express","phone":"(503) 555-9831","shipper_id":1},
      {"company_name":"United Package","phone":"(503) 555-3199","shipper_id":2},
      {"company_name":"Federal Shipping","phone":"(503) 555-9931","shipper_id":3}
    ]') AS x("company_name" varchar(40), "phone" varchar(24), "shipper_id" integer)
) AS "Source"
ON "Source"."shipper_id" = "Target"."shipper_id"

WHEN MATCHED AND (<change detection>) THEN           -- if MergeUpdate
  UPDATE SET "company_name" = "Source"."company_name",
             "phone"        = "Source"."phone"

WHEN NOT MATCHED THEN
  INSERT ("company_name", "phone", "shipper_id")
  VALUES ("Source"."company_name", "Source"."phone", "Source"."shipper_id")

WHEN NOT MATCHED BY SOURCE THEN                       -- if MergeDelete
  DELETE
;

The comment markers (-- if MergeUpdate, -- if MergeDelete) indicate sections that are conditionally emitted. With all flags on every block lands in the file; flip any of the flags and the relevant block disappears. When DisableTriggers or DisableRules is enabled, the script is wrapped with the matching ALTER TABLE statements before and after the MERGE.

Identity columns

Identity columns declared with GENERATED ALWAYS AS IDENTITY are handled via OVERRIDING SYSTEM VALUE in the INSERT clause so explicit values can be carried from the source. They are excluded from the UPDATE SET clause so existing identity values are never modified.

Generated columns

Generated columns (GENERATED ALWAYS AS ... STORED) are auto-excluded from extraction and from every section of the generated script. They can't be assigned, so attempting to insert or update them would fail at run time.

Partitioned tables

When UpdateDescendents is false, the generated MERGE uses MERGE INTO <table> ONLY AS "Target" so partitioned-table writes stay on the declared parent and do not propagate to descendant tables. Use this when you're delivering data to a partition directly and don't want the merge to cascade.

DataTongs can do more than write scripts — it can wire the extracted data straight into your schema package's declarative delivery pipeline. Pass --ConfigureDataDelivery and DataTongs writes a DataDelivery block into each matching table's JSON file after extracting the data.

DataTongs --ConfigureDataDelivery
DataTongs --ConfigureDataDelivery --TemplatePath:./Templates/Main

The switch can also be enabled via configuration:

{
  "ShouldCast": { "ConfigureDataDelivery": true }
}

What it does

For every table that produces output, DataTongs opens Tables/<schema>.<table>.json inside the template and writes (or overwrites) a DataDelivery block with:

Tables whose JSON file doesn't exist in the template are skipped with a warning. No file is created from scratch — this tool configures existing tables, it doesn't scaffold new ones (use SchemaTongs for that).

Default MergeType derivation

When a table's config doesn't set MergeType explicitly, the default is derived from the global ShouldCast flags:

A per-table MergeType on the Tables[] entry always wins over the derived default.

Template root discovery

--ConfigureDataDelivery needs to know where your template lives. It uses this precedence:

1. --TemplatePath:<path> CLI switch.
2. TemplatePath config key.
3. Auto-discovery: walk upward from ContentPath looking for the nearest directory containing a Template.json.

If nothing is found, DataTongs logs a warning and skips the configuration step — extraction still completes normally. See Schema Packages — DataDelivery for the full property reference and the schema validation that catches misconfigurations at authoring time.