By Chris Davidson, founder of yForest · Updated September 26, 2026
psql's \copy command is the usual answer for a CSV load into Postgres, and it works well from a terminal you're already comfortable in. Mapping the file's columns instead trades a command for a drag-and-drop, and adds Update and Upsert modes \copy doesn't have on its own.
No credit card. 14 days. Cancel in one click.
Quick answer: Add the CSV as a file connection, then build a Data Sync: pick the CSV as source and your Postgres table as target. Map columns, choose Insert, Update or Upsert, and run it, or save it as a recurring job. Pipelines tier.
psql's \copy is genuinely simple for a one-time load: point it at a local file and a table, and it's fast. What it doesn't give you natively is an upsert. If the load needs to update existing rows rather than just append, that means writing a staging table, a COPY into it, and an INSERT ... ON CONFLICT from staging into the real table by hand, three steps for something that should be one.
A payroll provider's monthly headcount export needs to refresh a Postgres table without duplicating anyone already in it:
-- target table CREATE TABLE IF NOT EXISTS hr.headcount ( employee_id text PRIMARY KEY, department text, title text, start_date date );
Map the export's EmpID, Dept, Title and StartDate columns, set MODE to Upsert, and MATCH ON to employee_id. A promotion or department change updates the existing row instead of adding a second record for the same person. Save it as a job running the day after payroll's export lands each month.
Dry Run shows what would write before anything does. After a real run, compare row counts and check the job's history for type mismatches.
| If you see | Fix |
|---|---|
| Choose at least one key field | Pick a MATCH ON column for Update or Upsert modes. |
| Duplicate key values in source | Check the export for repeated employee IDs before syncing. |
| Employee ID loses leading zeros | Type the target column as TEXT, not numeric. |
A one-time load of a genuinely large file, millions of rows, where you're already at a terminal and don't need an upsert, is still a good fit for \copy: no connection to configure, no mapping UI, just speed. The tradeoff shows up the moment the load needs to run again next month with different rows to reconcile against what's already there; at that point the hand-built staging-table pattern is more to maintain than a saved, mapped sync.
If next month's export has the same columns, point the same file connection at the new file and re-run the sync without rebuilding the mapping. A renamed or reordered column is the actual trigger to revisit it.
If the payroll provider ever switches employee ID formats, alphanumeric instead of purely numeric, for instance, the existing MATCH ON column and the new export's values won't line up, and every row looks new instead of matching what's already in the table. A payroll system change is worth treating as a reason to review the mapping and check for a spike in the row-synced count before trusting the next month's run.
A headcount or payroll-adjacent export often carries columns nobody outside HR should see in a general-purpose reporting schema. If the same Postgres instance serves other teams' reports, put the synced table in a schema with access restricted to who actually needs it, and map in only the columns a downstream report genuinely requires rather than the full export by default.
An employee ID, a cost center code, or any identifier that happens to look numeric is safer stored as text in Postgres than as an integer, even when every current value is digits only. It costs nothing to store a numeric-looking ID as text, and it avoids an entire category of leading-zero and overflow bugs the moment a future ID format includes a letter or a longer digit sequence than the column was sized for.
Matching row counts between file and table is a good first check, but it won't catch a case where the right number of rows landed with the wrong values, a shifted column mapping that happened to produce the same row count. A quick spot check, pulling three or four specific employee records from the file and comparing them directly against the same rows in Postgres, catches a shifted-mapping bug that a bare count would miss entirely.
A one-time load is genuinely just as fast by hand, open psql, run \copy, done. The gap opens up the second month, when the same file arrives again with the same structure and the manual process has to be remembered and re-run correctly by whoever's on duty that day. A saved, mapped sync removes that dependency on someone remembering the right sequence of commands.
If headcount data eventually needs to reach more than one destination, a data warehouse for reporting, an internal directory tool, in addition to this Postgres table, it's worth keeping the file connection and its mapping logic consistent across each sync rather than maintaining separate, slightly different mappings for each destination that can drift out of sync with each other over time.
A straightforward Upsert never removes a row, so an employee who leaves the company stays in the Postgres table indefinitely unless the payroll export itself stops including them and a separate cleanup query removes rows no longer present. Whether that's the right behavior depends entirely on what reads the table; a historical headcount report wants terminated employees kept, while a current-staff directory wants them filtered out or removed.
Fivetran's file-based connectors meter the same way as any other source under its pricing page (September 26, 2026): 500,000 MAR free, then a $5 base charge per connection between 1 and 1,000,000 MAR, usage above that behind a quote. A monthly headcount file in the hundreds of rows stays comfortably inside the free tier regardless, which makes this mostly a setup-time and workflow decision rather than a cost one.
Same mechanism, different file type and a different common case. A CSV load is usually a raw export or a one-off dump; the Excel version usually involves a workbook someone's actively editing, with sheet selection as an extra step.
For a one-time load of a huge file where you're comfortable at a terminal already, \copy is still fast and needs no setup beyond psql itself. It doesn't have a built-in Update or Upsert mode, though, an ON CONFLICT clause layered on top is a separate step you write by hand.
Type that column as TEXT or VARCHAR on the Postgres side, not a numeric type, or the leading zeros get dropped on load.
Yes, save the sync as a job and set a schedule. Update the file connection first if the file's location changes each time.
14-day free trial, no card. Map it once, run it whenever the file shows up.
No credit card. 14 days. Cancel in one click.