Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vnRyx-007QDk-3D for pgsql-hackers@arkaria.postgresql.org; Wed, 04 Feb 2026 01:47:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vnRyw-008OwI-2s for pgsql-hackers@arkaria.postgresql.org; Wed, 04 Feb 2026 01:47:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vnRyw-008OwA-0W for pgsql-hackers@lists.postgresql.org; Wed, 04 Feb 2026 01:47:02 +0000 Received: from mail-oo1-xc2f.google.com ([2607:f8b0:4864:20::c2f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vnRyt-00000000Rvi-3IzY for pgsql-hackers@lists.postgresql.org; Wed, 04 Feb 2026 01:47:00 +0000 Received: by mail-oo1-xc2f.google.com with SMTP id 006d021491bc7-66106a2f8d1so4354918eaf.1 for ; Tue, 03 Feb 2026 17:46:59 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770169619; cv=none; d=google.com; s=arc-20240605; b=SIcYwRbyPA1PqiBehy4brIi4d3+WSmqID2h0Q/AJ8OTZ24jcaCiTd5j39gQTYSzBYl lDq6DhIQLgPjYG4xYhimzjCCOd+fV7hpPMdP0ONkGqisU3RHOwP07qURq0epAcWBJt1/ nC27Hv5RrOExiYQD7NLa485BYTbRgNVu9vIW+0jFeIVbr8078OGpJ+ZDqt7OBImE7Gbo nN1yrkU3RwO4IwRpywHT1+iUSqjCfMSMolwZrGgBde6MVcs22h06jD8ZNP8EWhAHACo2 IJhrRrBhxJAHc9pK6ea3EbscdUtEKuHSuaixz0fhSmNMWeTE2XG9TMglrgw1KvYTozIA kdEQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=jyhkwOcAO2sb3KJQNlpqCtT8XuVHQfPfg8wTCM3ggaI=; fh=qTQNuqiUCj2o0u8kzTbCidsmy9pxHHtaz0gQ29cCaeI=; b=ePw7utgxWIazJJoaE6GHFbljFCdhnEYp0e6Nk+izK4L8qxAdKdy/IjHrmTzjKEcGXf 3/ptfmrclhR8fHPxBZIl3O4NwSSjBpvuzM3ZPuQ56m/IDVKzg3lAw13ymPm6X3wMXY4I DMJw67wfSX/3UKVfbPoly4bNrmNSXekGq1o4o78DBRUnUBxuVG1Oq1iKnY4rwsclmSUq OJt4pogAqYfkD+Xwk92OBIILiIQNLCfB2boBNkioRxFcKEL4bGYo6uAjYRxSI8MNosiw bYXZUxotq81gF2vZdQXD026Hy67tpR3v3yvnb2XcJD8rFzNZgc87i4e3/x0sajtR+o+4 cUzg==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1770169619; x=1770774419; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=jyhkwOcAO2sb3KJQNlpqCtT8XuVHQfPfg8wTCM3ggaI=; b=b/k7He4dEU/+Wfi1ls5X1e9mFM5wikA41YnSBuCZlAAm24p7caQC6zpubV/EjQEsI/ Q5NPLJMRD4dlyc/u8BIRTcyCb7ahwZBg5KhxXrtuV5aJ3gTGgD7LzgxyEFoj+10IItB6 8DV8cmzA4XN86mi7B9BQ+XuEPIBGIMRgf0RUK6vyk1TmJptLHiDx02kFSbWECWCYROHD SR1eJkmh9SrYBj/wCDyUdC02HooS4QoNTa0lu4Teg2FJCpU4lYaZ3zyl92g252dISm/F pQnbGcttSZ0c+jtnyFczhJMMWvkYNddNSBnriSTOeoAnFX35vCCHYiYdLMCg6DSVKW5/ q2Kw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770169619; x=1770774419; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=jyhkwOcAO2sb3KJQNlpqCtT8XuVHQfPfg8wTCM3ggaI=; b=Up6ClxHn/FZArhI5h0fFyJIq3NLY1Vx9nHZl1QeXyEPqgJ0yd2slxmAT5m3uAc5GL5 4FSDFC04gmMkrOuo6L0GgTef4yeEvtfq+/XO844pYUIoz6I4U4PjrYitJcJsOk5Ip2Sl 84LKl8Hczy4D4+wymvsm0YiL8UOc1Ww6y/TsTYCvnGkCSybJuCB0ZNowIQ4KWG3BqMSo fqmfBvj2ygYB1lS0qw1l40dkcU+D/5PrkMwxdWwjYMSLZxpJKabCT7+Iaz0xL22wMUUj Z/YHS58O6Y6UjN63/+wIwP7K9A77gAKtHi5QNFX56wUXAon8pmsRtRvp0+r5haJH/pSW LEtA== X-Forwarded-Encrypted: i=1; AJvYcCWR7Oq/BlJorOFQugIA5jr1YsDgdJFpSQddogQS5vVTIGTVSsZwfgB77DxwTpC2GWWVwIMyYF1sEGMNK9Tc@lists.postgresql.org X-Gm-Message-State: AOJu0YyhlPiBa2UMBlou6UOyHunyEkguIo2aMwV1uEIz1yspZPJXDROH LOf89X6slRn//nWXsFbY0qVdkQgm0kM3u7h+RD6g8lIDgO0sncrl2E9FDHV8WggCsqGxBQb7dSF TAjPwq2WJupniB+e6fw4f+HObRVqEXDl4W75X X-Gm-Gg: AZuq6aKq0BQeUWprWSymzYMi2QpTyQm4ze/HSefZHq/CiF58a/0PdnjKdLsxCZbZwXi DaMt+/AtPKIk1ixP2dZCpeF7GtkiuySmCEjAzgyMfzZZA4IVdzbOc/8CInYzHWl2m3i/wIksnXB mXocIdDanEMNfOFqGv41X2XuPoikKdGsCjfuAvMxQ4T3UTc4n0683krQX6VbQsGNRM0RztUmxlA 3GM8E8g5p5raOZ3V+uY8Q6rfYF5lFtTgJ2T7umt13bkMWdBMATI/NZIMEPa5qlvaC1wyUs0 X-Received: by 2002:a05:6820:994:b0:662:c94e:7ab1 with SMTP id 006d021491bc7-66a2151c220mr830975eaf.35.1770169619001; Tue, 03 Feb 2026 17:46:59 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Tue, 3 Feb 2026 18:46:22 -0700 X-Gm-Features: AZwV_Qij96Lb6T_pfj4AhiY610mySWHqDgWTIYFlPTqav5fOLo38NcCtUN_EWP0 Message-ID: Subject: Re: Add LIMIT option to COPY FROM To: Shinya Kato Cc: Florents Tselai , Tom Lane , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="0000000000005537800649f5bb5e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005537800649f5bb5e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Feb 3, 2026 at 6:07=E2=80=AFPM Shinya Kato wrote: > > Would want it paired with offset for this use case. > > COPY FROM already accepts HEADER to skip N leading lines, so > you can combine it with LIMIT to get OFFSET + LIMIT semantics today: > =3D# COPY t FROM 'file.csv' (HEADER 100, LIMIT 50); > Indirect, but fair, assuming the limit is indeed pre-parsed lines. You need OFFSET to match post-processed counted lines. > >> Design: > >> - The LIMIT count applies after WHERE filtering and ON_ERROR skipping, > >> so it represents the actual number of rows inserted. > > > > > > Not sure about that choice. I=E2=80=99d go with pre-eval or implement = both and > default to pre-eval. > > It is consistent with SQL semantics =E2=80=94 SELECT ... WHERE ... LIMIT = N > counts rows that pass the filter, not rows scanned. Ok, I suppose I'd name it "stop N" instead of "limit N" to avoid this. Pre-eval behavior > is already achievable externally (head -n), while post-eval can only > be done server-side, which makes it the more valuable choice for a > built-in option. > Not seeing the need for either-or; and everywhere else you want to support this feature by assuming that external tools aren't available. So; HEADER+STOP, HEADER?+OFFSET+LIMIT > This is not a novel feature either. Oracle SQL*Loader provides LOAD, > and SQL Server BULK INSERT provides LASTROW. The absence of LIMIT in > COPY FROM is arguably a gap relative to other RDBMSes. > > There'd be a lot less friction of this sort if someone just bites the bullet and devises a core-managed ETL tool instead of attaching pieces one-by-one into COPY. Or at least maybe we get a fast-path version to handle typical dump-restore commands and then branch to the ETL path if the command options indicate doing so is needed. There is apparently too much demand for this stuff for a nonproliferation agreement. David J. --0000000000005537800649f5bb5e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Feb 3, 2026 at 6:07=E2=80=AFPM Shinya Kato <shinya11.kato@gmail.com> wro= te:
> Would want it paired wit= h offset for this use case.

COPY FROM already accepts HEADER <integer> to skip N leading lines, s= o
you can combine it with LIMIT to get OFFSET + LIMIT semantics today:
=3D# COPY t FROM 'file.csv' (HEADER 100, LIMIT 50);

Indirect, but fair, assuming the limit is indeed pre-par= sed lines.=C2=A0 You need OFFSET to match post-processed counted lines.


>> Design:
>> - The LIMIT count applies after WHERE filtering and ON_ERROR skipp= ing,
>> so it represents the actual number of rows inserted.
>
>
> Not sure about that choice.=C2=A0 I=E2=80=99d go with pre-eval or impl= ement both and default to pre-eval.

It is consistent with SQL semantics =E2=80=94 SELECT ... WHERE ... LIMIT N<= br> counts rows that pass the filter, not rows scanned.

Ok, I suppose I'd name it "stop N" instead=C2=A0of &qu= ot;limit N" to avoid this.

Pre-eval behavior
is already achievable externally (head -n), while post-eval can only
be done server-side, which makes it the more valuable choice for a
built-in option.

Not seeing the need= for either-or; and everywhere else you want to support this feature by ass= uming that external tools aren't available.

<= div>
So;=C2=A0 HEADER+STOP, HEADER?+OFFSET+LIMIT


This is not a novel feature either. Oracle SQL*Loader provides LOAD,
and SQL Server BULK INSERT provides LASTROW. The absence of LIMIT in
COPY FROM is arguably a gap relative to other RDBMSes.

=

There'd be a lot less friction of this sort if someon= e just bites the bullet and devises a core-managed ETL tool instead of atta= ching pieces one-by-one into COPY.=C2=A0 Or at least maybe we get a fast-pa= th version to handle typical dump-restore commands and then branch to the E= TL path if the command options indicate doing so is needed.=C2=A0 There is = apparently too much demand for this stuff for a nonproliferation agreement.=

David J.

--0000000000005537800649f5bb5e--