Carlos GordilloPublished on November 11, 2026

ETL with n8n and PostgreSQL

Moving a spreadsheet into a database sounds simple until headers change, rows repeat, dates become ambiguous or a run stops halfway through. A reliable ETL workflow separates intake, validation and loading; records what happened; and can process the same file again without duplicating data. This guide presents a practical n8n and PostgreSQL pattern for recurring imports and one-off loads.

What the ETL workflow needs to solve

ETL stands for extract, transform and load. In this pattern, n8n coordinates file intake, validation and calls to PostgreSQL. The database stores records and import history. Keeping those responsibilities separate makes it possible to investigate a rejected row without reviewing the entire workflow.

Before adding nodes, define the input contract: accepted formats, column names, required fields, types, time zone and the rule used to identify each record uniquely. Also decide what happens when a file is repeated, a row is invalid or the process fails after writing some data. Those decisions shape the architecture more than the number of nodes.

A simple, auditable architecture

A maintainable workflow usually has explicit stages: receive the file, register the batch, read rows, normalize values, validate, load staging data, apply accepted changes to business tables and close the batch with a result. In n8n, give each stage predictable inputs and outputs and names that explain its purpose. Avoid one large code block that reads, transforms and writes everything without checkpoints.

For loads that need validation or review, use a staging table. Rows arrive there with a batch identifier and enough source information to diagnose errors. Only after validation are accepted rows applied to final tables. For a small, simple sync, staging may be unnecessary; choose the design based on the cost of a partial or incorrect load.

Define the schema and row identity

A database needs to know what each column represents. Do not store everything as text just because the source is a spreadsheet: dates, amounts and identifiers should have suitable PostgreSQL types. Also define a natural or technical key that recognizes the same entity across imports. Without a stable key, the workflow cannot distinguish an update from a duplicate.

CREATE TABLE records (
  source_id text PRIMARY KEY,
  amount numeric NOT NULL,
  source_updated_at timestamptz,
  imported_at timestamptz NOT NULL DEFAULT now()
);

The example is intentionally small: adapt names and constraints to the real domain. If an identifier is unique only within a parent entity, use a composite key; if the file has no reliable key, define how one is generated and document its limits. Do not use the row number as identity: it changes when someone sorts or inserts rows.

Normalize data before validating it

Normalization converts representation variants into a consistent form: trimming whitespace, normalizing case when the domain permits, converting numbers and dates, and representing empty values consistently. Keep the original value when it helps explain a transformation. Do not silently turn invalid data into zero or the current date: that changes a visible error into incorrect data that looks legitimate.

Validation should distinguish row errors from batch errors. A malformed email may be quarantined while the rest continues, if the business allows it. A file missing required columns, using unreadable encoding or containing an unknown header should stop before writing to final tables. Return a specific reason such as amount_not_numeric, not a generic “invalid row.”

Make reprocessing safe

A run can finish writing to PostgreSQL but stop before n8n records success. If the workflow retries, an insert-only load may duplicate rows. Design the operation so that processing the same record twice produces the same state as processing it once. This property is commonly called idempotency.

With a unique key, PostgreSQL can insert or update atomically:

INSERT INTO records (source_id, amount, source_updated_at)
VALUES ($1, $2, $3)
ON CONFLICT (source_id) DO UPDATE
SET amount = EXCLUDED.amount,
    source_updated_at = EXCLUDED.source_updated_at,
    imported_at = now();

Use the PostgreSQL node’s query parameters instead of concatenating values into SQL. Also decide whether the newest file always wins, only changed columns are updated or an older row is rejected. Conflict handling is a business rule and should be covered by tests.

Process batches without losing traceability

Reading and writing thousands of rows in one operation can increase memory use and make failures hard to locate. Controlled batches limit the size of each operation and make it possible to record how many rows were accepted, rejected and loaded. The right size depends on volume, service limits and database latency; measure it with representative data instead of assuming the biggest batch is always best.

Keep an import record with a batch identifier, file name or fingerprint, start time, status, counts and error message. If the same file must not be applied twice, a cryptographic fingerprint can help detect it, but it does not replace the per-row key: two different files may contain the same entities, and one file may need reprocessing after a rule is fixed.

Design failures, retries and recovery

Not every error should be retried. A temporary timeout may justify a limited number of retries with a delay; a value that violates the schema needs correction or quarantine. Avoid infinite retries and include the batch identifier in every message so an operator can trace a run end to end.

Think about the transaction boundary too. If an import must be all-or-nothing, validation and final writes need to follow a transaction strategy compatible with the nodes and connections in use. If partial loads are acceptable, make that behavior explicit in the batch status and in the summary sent to the operator. Do not report success while leaving unexplained rows pending.

Monitor outcomes, not just executions

A green workflow only means its steps completed according to their logic; it does not prove that the data makes sense. Track measures such as rows received, valid, rejected, inserted and updated; duration; last processed batch; and data freshness. An alert for a missing expected import may be more useful than a notification for every successful run.

Logs should help diagnose problems without exposing secrets or unnecessary personal data. Store errors with a row or batch identifier and a stable code; limit or mask sensitive content. Define who receives alerts and what action is expected: retry, fix the file or review the schema.

Test before automating the workflow

Prepare test files with normal and edge cases: changed header, blank row, duplicate, time-zone date, decimal number, accented characters and a missing field. Verify that a valid file produces the expected state, an invalid file does not contaminate final tables and running the same batch twice does not duplicate data.

Also test an interruption during loading and the recovery afterward. Check PostgreSQL’s final state, not only whether n8n displays a green mark. Keep an anonymized sample as a regression test when changing mappings or validation rules.

Example run, from file to table

Imagine a recurring file with supplier ID, product code, quantity and update date. When it arrives, the workflow registers a batch and checks that required columns are present. It then converts quantities and dates to defined types, trims key fields and verifies that quantity is not negative. Valid rows move to staging; invalid rows remain associated with the batch and a rejection code.

After validation, the workflow applies accepted rows using the supplier and product key. If the same file runs again, the unique constraint and conflict rule update the record instead of creating another one. Closing the batch stores counts, duration and status. A summary can say, for example, “98 accepted, 2 rejected” and include reasons without copying sensitive data into the message. This is a generic example: each system must adapt keys, rules and load policy to its own process.

Checklist

Documented file contract, columns, types and time zone.
Stable record key and conflict rule defined.
Separate validation for individual rows and the full batch.
Staging where inspection or atomic loading is required.
Limited retries for transient errors, not invalid data.
Batch history with counts, duration, status and errors.
Tests for reprocessing, interruption and recovery.
Credentials kept outside workflow content and source control.

Common mistakes

Using the row number as a key and duplicating data after sorting the sheet.
Turning format errors into default values without reporting them.
Retrying validation errors as if they were temporary failures.
Marking a batch successful while rows were not loaded.
Writing entire files to logs and exposing sensitive data.
Building SQL through string concatenation instead of parameters.

Frequently asked questions

Does every ETL workflow need a staging table?

Not always. It helps when you need to validate the full set before publishing it, investigate rejected rows or recover from partial loads. For a small sync with a well-defined schema and key, a direct load may be enough.

How do I prevent duplicates when n8n retries a run?

Define a unique key for each record and use an insert-or-update operation based on that key. Keep a batch record for auditing retries as well. A file fingerprint alone does not replace row identity.

Should one invalid row stop the whole batch?

It depends on the impact of accepting a partial load. If one bad row can change a total or decision, stop and correct the batch. If rows are independent, invalid ones can be quarantined as long as the result clearly reports how many were excluded.

How should I choose batch size?

Measure memory, duration, connection limits and recovery effort with representative volumes. Smaller batches often make diagnosis easier; larger ones may reduce database round trips. The right balance depends on the workload and should be tested.