Want to upsert data from CSV file to existing PostgreSQL table via command line on Linux (Ubuntu/RedHat/CentOS…)?
Using pg-cmd, a Command-Line client for PostgreSQL, you can upsert(merge) CSV/TSV/TXT data to PostgreSQL table easily and fast.
Support Windows, Linux, and MacOS.
No need to install PostgreSQL client library.
Convenient to deploy to cloud. No need to run installation file, just uncompress and run, immediately.
Upsert/Merge CSV/TSV/TXT data into PostgreSQL table via command line on Linux
1. In interactive mode:
pg-cmd> load loadtype=upsert
> updatekey=id
> table=media_messages
> datafile=/data/media_messages.csv
> delimiter=,
> quote="
> header=y;
2. In batch mode:
pg-cmd dsn="pan:withdata@192.168.0.102/test" task=load loadtype=upsert updatekey=id table=media_messages datafile=/data/media_messages.csv delimiter=, quote=" header=y quit=y
“loadtype=upsert” will update row in table if “updatekey” id exists, or insert new row.
See parameters detail.
More about pg-cmd – Command-Line client for PostgreSQL
Download pg-cmd Linux version
CSV → PostgreSQL reference
Type mapping
| Value kind | in CSV | in PostgreSQL |
|---|---|---|
| Int | 1234 | integer |
| Bigint | 9007199254740993 | bigint |
| Decimal | 1234.56 | numeric(p,s) |
| Float | 1.234e+10 | double precision |
| Text | quoted only when it contains a comma or a quote | varchar(n) |
| Long text | must be quoted; line breaks inside force multi-line records | text |
| Date | 2026-09-18 | date |
| Datetime | 2026-09-18 14:30:00 | timestamp |
| Binary | not representable - base64 or hex string | bytea |
| Boolean | TRUE / FALSE, or 1 / 0 depending on the writer | boolean |
| JSON | the whole JSON value quoted, with internal quotes doubled | jsonb |
Dialect differences
CSV is a file format and PostgreSQL is a database server, so the target has to be told the types the file never carried – the rows below are where the two sides diverge.
| Property | in CSV | in PostgreSQL |
|---|---|---|
| port | — | 5432 |
| identifiers | Double quotes; a literal quote is doubled ("" ) | "double quotes" |
| row limit | — | LIMIT n |
| dates | Conventional text: 2026-09-18 or 2026-09-18 14:30:00 | timestamp, timestamptz, date, time |
| long text | — | TEXT |
| binary | Not supported | BYTEA |
| separator | Comma "," | — |
| encoding | No declaration - UTF-8 works only with a BOM | — |
| typing | None - every field is text | — |
| shape | Flat table, optional header row | Tables of rows and columns |
| ceiling | No formal limit; row count bounded by memory | — |
Command line
| Side | Command |
|---|---|
| PostgreSQL | pg_dump or COPY ... TO |
| PostgreSQL (load) | COPY ... FROM |
Convert your data online, free
Free in-browser converters for CSV, Excel, JSON, XML and SQL: Withdata Online Data Conversion. Large files, batch conversion or scheduling? See the offline desktop apps, or browse all data conversion tools.
