How to verify a PostgreSQL CSV import

Validation, insertion and reconciliation answer different questions

Keep those statements in the UI and handoff. “The CSV parsed” is not “the import passed validation.” “The server received it” is not “the database committed it.” “200 inserted” is not “all customer data is correct.”

A DDL checker belongs before these live outcome checks. PG Import Check can expose declared requirements and analysis gaps in supplied SQL. It does not validate CSV records, query the destination or perform reconciliation.

Freeze what you expect to find

Before execution, preserve a reviewed accepted set. It should identify the source records that will be written, their approved transformed values, the tenant and relevant source-system namespace. Keep the mapping and rule version with it.

Record exclusions explicitly. If a 205-record file has five intentionally excluded contacts, the expected insert set is 200. If those five were lost because a join found no parent, they are unexplained losses until someone approves their treatment.

Choose a stable key. It may be a tenant-scoped external contact reference, a destination-generated ID map, or an import ledger associating source records with destination identities. An email address is not automatically a good key: addresses can change, be shared or be absent.

If the target has no stable matching identity or reliable import attribution, acknowledge that limitation before writing. A timestamp window or a count difference can include concurrent application activity. Add a reviewed way to identify this import’s rows or narrow the verification claim.

Demonstration: equal counts, different records

Run this setup in a disposable PostgreSQL 17 session. Both tables represent only the tenant being reviewed; their primary keys enforce uniqueness inside this example.

CREATE TEMP TABLE import_expected_demo (
  tenant_id integer NOT NULL,
  external_ref text NOT NULL,
  display_name text,
  PRIMARY KEY (tenant_id, external_ref)
);
CREATE TEMP TABLE import_actual_demo (
  tenant_id integer NOT NULL,
  external_ref text NOT NULL,
  display_name text,
  PRIMARY KEY (tenant_id, external_ref)
);

INSERT INTO import_expected_demo VALUES
  (10, 'contact-a', 'Ada'),
  (10, 'contact-b', 'Bela'),
  (10, 'contact-c', NULL);

INSERT INTO import_actual_demo VALUES
  (10, 'contact-a', 'Ada'),
  (10, 'contact-c', ''),
  (10, 'contact-d', 'Dara');

SELECT
  (SELECT count(*) FROM import_expected_demo WHERE tenant_id = 10)
    AS expected_count,
  (SELECT count(*) FROM import_actual_demo WHERE tenant_id = 10)
    AS actual_count;

Both counts are three. Yet contact B is missing, contact D is unexpected, and contact C has an empty name instead of NULL. This is why count equality is necessary for this particular one-to-one expectation but not sufficient for correctness.

Compare membership in both directions

Use the accepted business keys to find missing records:

SELECT external_ref
FROM import_expected_demo WHERE tenant_id = 10
EXCEPT
SELECT external_ref
FROM import_actual_demo WHERE tenant_id = 10;

This returns contact-b. Reverse the comparison to find unexpected records:

SELECT external_ref
FROM import_actual_demo WHERE tenant_id = 10
EXCEPT
SELECT external_ref
FROM import_expected_demo WHERE tenant_id = 10;

This returns contact-d. In a production table containing pre-existing contacts, do not compare the entire tenant against a new-import accepted set and label every old contact unexpected. Scope actual rows using trustworthy import attribution, or compare against an explicitly constructed full post-import expectation.

EXCEPT removes duplicate output rows. Here the primary keys guarantee uniqueness. Without that guarantee, set equality can hide multiplicity errors. Check grouped key counts or use an appropriate multiset comparison such as EXCEPT ALL, with NULL and normalization semantics understood.

Compare values with NULL-safe semantics

Once membership is accounted for, compare the fields that matter. Use the expected transformed representation rather than a raw string that was intentionally normalized.

SELECT expected.external_ref,
       expected.display_name AS expected_name,
       actual.display_name AS actual_name
FROM import_expected_demo AS expected
JOIN import_actual_demo AS actual
  ON actual.tenant_id = expected.tenant_id
 AND actual.external_ref = expected.external_ref
WHERE expected.tenant_id = 10
  AND expected.display_name IS DISTINCT FROM actual.display_name;

This returns contact C. An ordinary <> comparison involving NULL produces unknown, so it can miss the mismatch in a WHERE clause. IS DISTINCT FROM provides the NULL-aware comparison used here. The join deliberately handles matching keys; it does not replace the missing-key checks.

Choose domain invariants with the data owner. For a contacts import, that might mean the approved tenant and company relationship, permitted lifecycle statuses and preservation of external references. For an invoice import, it might mean totals by currency and document state. A single global sum can conceal errors that cancel each other out.

Compare database-generated values according to their intended properties. Do not expect a newly generated ID to equal a legacy ID. For a calculated total, test the agreed arithmetic and rounding. For an audit timestamp, test the documented time rule. See generated values and defaults.

Account for every source record once

Define mutually exclusive states for your workflow. For an insert-only import, a useful final accounting separates approved exclusions, confirmed committed records, confirmed non-inserted records and unresolved records. Unattempted work belongs in the non-inserted category with a separate reason; do not imply it failed a database check.

205 parsed source records
  5 approved exclusions
196 confirmed committed
  2 confirmed non-inserted (rejected batch)
  2 unresolved (commit evidence unavailable)
---
205 accounted for; import is not yet complete

This is an example accounting convention, not a universal schema. An upsert workflow also needs to distinguish inserted, updated and intentionally unchanged records. A deduplicated source may have several source records representing one accepted entity; record that relationship instead of forcing an invalid one-row-to-one-row equation.

A network timeout does not prove a zero-write outcome. Consult a durable receipt or database evidence that identifies the attempt. If the outcome remains unknown, keep it unknown and stop a blind retry-all. The importer design guide explains an atomic write-and-receipt pattern.

Use a consistent snapshot, then test the app

Several queries run at different times can describe different database states. For a live application, decide whether verification uses a controlled write window, a consistent database snapshot, or an immutable import-specific view. Rehearse the cost of that choice; a long transaction has operational consequences.

Under PostgreSQL’s default Read Committed isolation, separate statements can see different committed snapshots. Repeatable Read offers a consistent transaction snapshot, but it does not freeze the business world or include transactions that commit after that snapshot. See the isolation documentation.

After database comparisons, exercise the application with the intended user. A row may exist but be hidden by RLS, attached to another tenant, excluded by a status filter or missing a required relationship. Also review downstream effects: an email or external API call may need its own evidence, beyond the inserted row.

Sampling can help find format or UX problems; it does not prove all rows match. State which checks covered the entire accepted set and which were samples.

Make the acceptance record useful later

Keep a compact handoff with the accepted source identity, mapping/rule version, target and tenant, execution evidence, exclusions, comparison results and unresolved checks. Include who reviewed the business meaning and when the verification ran.

Do not put raw customer records in routine logs or public tickets. Retain diagnostic data only where authorized, restrict access and agree deletion. An external reference can itself be sensitive even when it is not a name or email address.

If a check fails, investigate before changing production. Correcting a source value, repairing a mapping and compensating for a committed write are different actions. Preserve evidence of the original outcome so a repair does not erase what needs explaining.