Add LIMIT option to COPY FROM

From: Shinya Kato <shinya11(dot)kato(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Add LIMIT option to COPY FROM
Date: 2026-02-03 13:49:14
Message-ID: CAOzEurR2N3iEG6fgQ34EjDghQEv-3u7t69dMw6KqOTq12J3zNg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

I'd like to propose adding a LIMIT option to COPY FROM, which limits
the number of rows to load.

With COPY TO, we can use the LIMIT clause in the query to restrict
output rows, but COPY FROM has no equivalent way to limit the number
of rows to load (except using the PROGRAM option with external tools
like head). This patch resolves that asymmetry.

Syntax example:
- COPY t FROM STDIN (LIMIT 100);

This feature is useful for:
- Loading only the first N rows from a huge CSV file to verify data or
table definitions before a full import
- Sampling production data for staging or testing environments
- Preventing unexpectedly large data loads in ETL pipelines

Design:
- The LIMIT count applies after WHERE filtering and ON_ERROR skipping,
so it represents the actual number of rows inserted.
- When the source is STDIN, the server reads and discards the
remaining stream to keep the frontend/backend protocol synchronized.

The patch is attached. Thoughts?

--
Best regards,
Shinya Kato
NTT OSS Center

Attachment Content-Type Size
v1-0001-Refactor-defGetCopyRejectLimitOption.patch application/octet-stream 2.7 KB
v1-0002-Add-LIMIT-option-to-COPY-FROM.patch application/octet-stream 12.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Matheus Alcantara 2026-02-03 14:06:47 Show expression of virtual columns in error messages
Previous Message Bertrand Drouvot 2026-02-03 13:39:22 Re: rename and move AssertVariableIsOfType