PostgreSQL Bulk INSERT with ON CONFLICT

PostgreSQL-Specific Bulk INSERT

PostgreSQL offers the most flexible INSERT syntax among major databases. The bulk INSERT tool generates idiomatic PostgreSQL output with proper quoting and conflict handling.

PostgreSQL Identifier Quoting

PostgreSQL uses double quotes for identifiers:

INSERT INTO "users" ("id", "first_name", "email")
VALUES
  (1, 'Alice', 'alice@example.com'),
  (2, 'Bob', 'bob@example.com');

ON CONFLICT Syntax

PostgreSQL's upsert uses the ON CONFLICT clause:

DO NOTHING (skip duplicates):

INSERT INTO "users" ("id", "name", "email")
VALUES
  (1, 'Alice', 'alice@example.com')
ON CONFLICT ("id") DO NOTHING;

DO UPDATE SET (upsert):

INSERT INTO "users" ("id", "name", "email")
VALUES
  (1, 'Alice Updated', 'alice.new@example.com')
ON CONFLICT ("id") DO UPDATE SET
  "name" = EXCLUDED."name",
  "email" = EXCLUDED."email";

The EXCLUDED pseudo-table references the row that would have been inserted, making it easy to reference new values in the UPDATE clause.

PostgreSQL Strengths for Bulk Inserts

• No practical row limit per INSERT statement
• JSONB support: Nested objects can be stored in JSONB columns
• COPY command: For ultimate speed, export as CSV and use COPY
• Parallel execution: Multiple batches can run in parallel transactions

Performance Tips

For PostgreSQL, batch sizes of 500-1000 are typically optimal. The database handles large statements efficiently, so you can use larger batches than MySQL or SQLite.