How to Import CSV Into PostgreSQL

Importing a CSV into PostgreSQL from inside your own app is one statement carrying many rows, aimed at a table whose constraints decide which of them survive. A payroll product meets that statement every time a customer arrives holding a pay run exported from the system they are leaving.

Their export looks like this.

1Line Ref,Emp No,Period End,Element,Hours,Rate/hr,Cost Ctr2PR-4471,E-0148,05/07/2026,BASIC,37.50,18.90,CC-2003PR-4472,E-0148,31/07/2026,O/T,6.25,28.35,CC-2004PR-4473,E-0209,31/07/2026,Bonus (annual),n/a,(45.00),CC-4105PR-4473,E-0233,31/07/2026,BASIC,37.50,22.10,⋮407 rows not shown413PR-4882,E-0331,31/07/2026,BASIC,37.50,19.75,CC-310

Seven headers. Three of them carry names your table never uses. Rows 4 and 5 share the line reference PR-4473. One period end reads 05/07/2026 and the next reads 31/07/2026. The hours cell on the bonus line says n/a, and the rate beside it reads (45.00), which is how the old system wrote a negative. The element column says BASIC, O/T and Bonus (annual). The cost centre on row 5 is empty.

The person opens the importer inside your app and drops the pay-run export into it. Updog Importer parses the export in the browser, lines its headers up with your fields, and shows them every pay line. Your onComplete handler receives the rows. The handler slices those rows into chunks and posts each chunk to a route you own. The route holds the connection string and sends one INSERT per chunk. PostgreSQL writes the table.

Between that browser and PostgreSQL there is no Updog server.

Step 1. Create the table and its constraints

Start with the table, because everything the browser checks later is a copy of what this declares.

create table public.pay_run_lines (
  id bigint generated always as identity primary key,
  tenant_id uuid not null,
  line_ref text not null,
  emp_no text not null,
  period_end date not null,
  pay_element text not null,
  hours numeric(6, 2) not null,
  hourly_rate numeric(8, 2) not null,
  cost_centre text not null,
  updated_at timestamptz not null default now(),
  constraint pay_run_lines_ref_key unique (tenant_id, line_ref),
  constraint pay_run_lines_rate_check check (hourly_rate >= 0),
  constraint pay_run_lines_element_check
    check (pay_element in ('Basic', 'Overtime', 'Bonus'))
);

The unique constraint on tenant_id and line_ref is the piece that makes a second import safe. The INSERT documentation is explicit, "For ON CONFLICT DO UPDATE, a conflict_target must be provided", and only non-deferrable constraints and unique indexes are allowed to arbitrate one. Two of your customers can both send a line called PR-4471, so identity here takes the tenant as well. Inside one import the tenant is fixed, so the line reference alone identifies a line on the browser side.

The rest of the table is four kinds of refusal. not null refuses a cost centre that arrives as null. numeric(6, 2) refuses the n/a sitting in the hours cell. check (hourly_rate >= 0) refuses -45.00. The check on pay_element refuses anything outside the three elements your product pays.

The empty cost centre in the file gets past the first of those. An empty cell reaches your handler as an empty string, and not null accepts an empty string, so the browser check on that column reaches further than the table does.

Step 2. Mirror those constraints in the column schema

The columns array is that table written for the person looking at the file. Every entry carries a title the person reads, an editor that shapes typing in a cell, and validators that mark what the table would refuse.

import type { DataEditorColumn } from "@updog/data-editor";

const PAY_ELEMENTS = ["Basic", "Overtime", "Bonus"];

export const columns: DataEditorColumn[] = [
  {
    id: "lineRef",
    title: "Line ref",
    size: 130,
    transformer: (value) => String(value).trim(),
    validators: [
      { type: "required" },
      { type: "unique" },
    ],
  },
  {
    id: "empNo",
    title: "Employee number",
    size: 140,
    validators: [{ type: "required" }],
  },
  {
    id: "periodEnd",
    title: "Period end",
    size: 140,
    editor: { type: "date" },
    validators: [
      { type: "required" },
      { type: "date" },
    ],
  },
  {
    id: "payElement",
    title: "Pay element",
    size: 150,
    editor: { type: "select", options: PAY_ELEMENTS, enableCustomValue: false },
    validators: [
      { type: "required" },
      { type: "oneOf", values: PAY_ELEMENTS },
    ],
  },
  {
    id: "hours",
    title: "Hours",
    size: 110,
    editor: { type: "number" },
    validators: [
      { type: "required" },
      { type: "number", decimalPlaces: 2 },
    ],
  },
  {
    id: "hourlyRate",
    title: "Hourly rate",
    size: 130,
    editor: { type: "number" },
    validators: [
      { type: "required" },
      { type: "number", min: 0, decimalPlaces: 2 },
    ],
  },
  {
    id: "costCentre",
    title: "Cost centre",
    size: 140,
    validators: [{ type: "required" }],
  },
];

Every validator answers one line of the create table statement above.

Those four codes are PostgreSQL's own, listed in its error appendix under Class 22 for data exceptions and Class 23 for integrity constraint violations. Common CSV import errors and how to prevent them covers the wider set a file arrives with.

blockSubmitOnError is what makes the mirror hold. Submit stays disabled while any row carries an error, so a row that would raise any of those codes stays on the screen until somebody fixes it. The synonyms prop teaches matching the words the old payroll system used, and the mount ties the whole thing together.

<DataEditor<PayRunLine>
  apiKey="your-license-key"
  variant="uploader"
  open={open}
  onClose={closeImporter}
  columns={columns}
  primaryKey="lineRef"
  blockSubmitOnError
  synonyms={{
    columns: { hourlyRate: ["rate/hr", "hourly pay rate", "std rate"] },
    values: { Overtime: ["o/t", "ot", "overtime prem"] },
  }}
  onComplete={onComplete}
/>

Emp No and Period End reach empNo and periodEnd on an exact match, because matching lowercases a header and drops spaces before it scores. Element reaches payElement because payelement contains element. Cost Ctr reaches costCentre on word overlap, since cost matches and ctr matches nothing. Rate/hr reaches no column at all, because the slash survives normalising, so neither string contains the other and they share no word. That header comes from synonyms. Values score the same way. BASIC lands on Basic exactly, and Bonus (annual) lands on Bonus because it contains it. O/T is three characters, which is under the length the contains tier accepts, so it comes from synonyms too.

A date column normalizes 05/07/2026 to 2026-07-05, because 31/07/2026 further down the file carries a day past 12 and settles the whole file as day-first. The number columns read the same file as dot-decimal, since 37.50 and 18.90 vote that way and nothing votes the other. Those two verdicts are taken separately, so a day-first file with dot decimals stays readable.

primaryKey is required, and lineRef points it at the browser half of that conflict target. Every snippet here is React. The web component build carries the same props, so a Vue, Angular or Svelte app writes the same schema and the same handler. For the install and the modal wiring underneath this snippet, see how to import a CSV file into a React app.

Step 3. Post the result in chunks

When the person submits, Updog Importer hands your handler each pay line grouped by source, carrying isNew, isChanged, isDeleted and isValid. Rows nobody touched stay out. One person can drop three files in a single import, so the handler flattens before it slices.

import type { DataEditorResult } from "@updog/data-editor";

const CHUNK_SIZE = 1000;

const onComplete = useCallback(async (result: DataEditorResult<PayRunLine>) => {
  const rows = result.sources
    .flatMap((source) => source.rows)
    .filter((entry) => entry.isValid && !entry.isDeleted)
    .map((entry) => entry.row);

  for (let start = 0; start < rows.length; start += CHUNK_SIZE) {
    const chunk = rows.slice(start, start + CHUNK_SIZE);

    const response = await fetch("/api/pay-run/import", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({ rows: chunk }),
    });

    if (!response.ok) {
      const failure = await response.json();
      throw new Error(
        "Rows " + (start + 1) + " to " + (start + chunk.length) +
          " were not saved. " + failure.message,
      );
    }
  }
}, []);

The chunk size is arithmetic on a published number. PostgreSQL caps a statement at 65,535 query parameters, in the same appendix that caps a table at 1,600 columns. A parameterized query in node-postgres travels through the extended query protocol, whose Bind message carries the parameter count as an Int16. Each row in the statement below writes eight columns, so that statement holds 8,191 rows at the most.

Nobody publishes a batch size for this, so 1,000 is a conservative choice of ours under that ceiling. Two things pull it down. The person waits with the confirm dialog open and a spinner on the button, so a single huge request is a long freeze. A chunk that fails rolls back whole, so the smaller the chunk, the less work a retry repeats. Run the division against your own table before you raise it, because a table of forty columns puts the ceiling at 1,638.

Throw when PostgreSQL refuses a chunk. Updog waits on your handler and empties the editor the instant the promise resolves. A handler that swallows the failure and returns looks like a clean import, and the grid empties with the pay run unwritten. Throwing holds every pay line, every mapping and every correction on the screen, so the person submits again on data still in front of them. Copy anything you want to keep inside the handler, because the editor drops its rows, its sources and its learned synonyms once the promise resolves.

The isValid filter carries a cost worth naming. A failed validator marks the cell and lets submission continue, so without blockSubmitOnError the flagged rows reach your handler and the filter drops them on the floor. Splitting the rows by isValid and posting the failures to a table your support team reads works too.

Step 4. Write one transaction per chunk

The connection string lives in that route, and the browser goes no further.

import express from "express";
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const FIELDS = 8;

export const router = express.Router();

router.post("/api/pay-run/import", async (request, response) => {
  const session = await getVerifiedSession(request);
  if (!session) {
    return response.status(401).json({ message: "Not signed in" });
  }

  const rows = request.body.rows;
  const values = [];

  const tuples = rows.map((row, index) => {
    values.push(
      session.tenantId, row.lineRef, row.empNo, row.periodEnd,
      row.payElement, row.hours, row.hourlyRate, row.costCentre,
    );
    const base = index * FIELDS;
    const holders = [];
    for (let n = 1; n <= FIELDS; n++) holders.push("$" + (base + n));
    return "(" + holders.join(", ") + ")";
  });

  const text = [
    "insert into pay_run_lines (tenant_id, line_ref, emp_no, period_end,",
    "  pay_element, hours, hourly_rate, cost_centre)",
    "values " + tuples.join(", "),
    "on conflict (tenant_id, line_ref) do update set",
    "  emp_no = excluded.emp_no,",
    "  period_end = excluded.period_end,",
    "  pay_element = excluded.pay_element,",
    "  hours = excluded.hours,",
    "  hourly_rate = excluded.hourly_rate,",
    "  cost_centre = excluded.cost_centre,",
    "  updated_at = now()",
  ].join("\n");

  const client = await pool.connect();
  try {
    await client.query("begin");
    await client.query(text, values);
    await client.query("commit");
    response.json({ written: rows.length });
  } catch (error) {
    await client.query("rollback");
    response.status(400).json(describeFailure(error));
  } finally {
    client.release();
  }
});

One INSERT carries the whole chunk. on conflict (tenant_id, line_ref) do update set turns a second import of the same line into an update, and excluded is Postgres's name for the row that was proposed. The alternative is a loop of single-row inserts, which sends one Parse and one Bind per row and holds the person on the spinner while it runs. A hosted Postgres reaches the same table through its own client library, and how to import CSV into Supabase writes this route with upsert() and a secret key.

The begin and the commit earn nothing while the chunk is one statement. PostgreSQL wraps every lone statement in an implicit BEGIN and COMMIT of its own, and its tutorial says exactly that. The pair starts earning the moment the route makes a second write in the same chunk, an audit row or a header update. Until then it keeps the chunk boundary and the rollback boundary on one line of code.

The client comes from pool.connect() and goes back in a finally. node-postgres is blunt about why, "You must use the same client instance for all statements within a transaction", and pool.query hands out whichever client happens to be idle. getVerifiedSession() stands in for your own server-side check. tenant_id comes off that session and never off the request body, so one customer cannot write rows into another customer's pay run.

A chunk PostgreSQL refuses takes the catch and the rollback before the person hears anything about it.

Step 5. Carry the constraint error back to its row

A rejected chunk comes back as one error object carrying the fields the wire protocol defines.

const FIELD_BY_CONSTRAINT = {
  pay_run_lines_ref_key: "Line ref",
  pay_run_lines_rate_check: "Hourly rate",
  pay_run_lines_element_check: "Pay element",
};

const describeFailure = (error) => ({
  code: error.code,
  field: FIELD_BY_CONSTRAINT[error.constraint] ?? error.column ?? null,
  message: error.message,
  detail: error.detail ?? null,
});

23505 and 23514 both name the rule they broke in error.constraint, so a lookup turns pay_run_lines_rate_check into the words Hourly rate. 23502 leaves that field empty and fills error.column instead, which is why the mapping reads one and then the other. 22P02 fills neither, because a value that fails to become a numeric never reaches a constraint, so the message carrying invalid input syntax for type numeric is what travels back.

error.detail holds the half a person can act on. A unique violation puts Key (tenant_id, line_ref)=(...) already exists. there, and a check violation puts the failing row. Postgres withholds it when the connecting role cannot read every column in the key, so the mapping falls back to the message when it arrives empty. What it does carry is values out of the row the person submitted, going back to the browser they came from.

The conflict target moves the unique code. A line already stored under the same key comes back as an update, and a duplicate that arrives inside one chunk stops the statement with 21000, where Postgres answers ON CONFLICT DO UPDATE command cannot affect row a second time. Its hint asks you to ensure that no rows proposed for insertion within the same command have duplicate constrained values, which is the whole job of the unique validator on lineRef. That failure carries no constraint name and no column, so the lookup falls through to the message.

Chunks that already committed stay committed. The same chunk sent again writes the same values through the same conflict target, so a retry after a failure costs a rewrite of what already landed.

The connector that does not exist

Updog Importer integrates with nobody. There is no PostgreSQL connector, no destination list, no webhook and no server of ours. onComplete hands your code an object of pay lines, and the route that writes them is yours to build.

PostgreSQL already ships its own import paths for the other case. COPY reads a file the server itself can reach, and its documentation allows that to superusers and to roles granted pg_read_server_files. \copy in psql reads a file on the machine running psql, which is the shorter path when the file is yours. The pgAdmin Import/Export dialog does the same work through a form. Each one starts from the seat of somebody holding a database connection, and a bad value fails the whole command by default, since COPY ships ON_ERROR as stop.

Your customer holds none of that. They hold a pay-run export and a login to your app. Client-side and server-side CSV import lays out what each model does well.

The rules that run in both places

You wrote a table whose constraints say what a pay-run line has to be, a schema that repeats each of those rules as a validator, a handler that chunks at 1,000 rows, and a route that opens one transaction per chunk. The pay-run export never leaves the machine that opened it. The rows travel from your own front end to your own route, and from there into PostgreSQL, and the only party added to the chain is yourself.

The error codes in step 5 are there for the rows nobody fixed. The same rules already ran in a grid where the person could see them and type over them, which is the point of writing those rules twice.