COPY TO: provide hint when WHERE clause is used
Hi,
I saw a user wanted to restrict the rows copied by COPY TO using a WHERE
clause, but ran into an error.
As you know, COPY FROM supports WHERE, but COPY TO does not:
=# copy t1 to stdout where i > 5;
ERROR: WHERE clause not allowed with COPY TO
LINE 1: copy t1 to stdout where i > 5;
^
In such cases, we can do with specifying a query as the target of COPY:
=# copy (select i from t1 where i > 5) to stdout;
However, as shown in the first example, no hint is provided in the error
message.
For views or others, COPY TO already provides a helpful hint message:
=# copy v1 to stdout;
ERROR: cannot copy from view "v1"
HINT: Try the COPY (SELECT ...) TO variant.
Considering what the user was trying to do, it might be helpful to
provide a similar hint in this case as well.
I’ve attached a patch that adds such a hint.
What do you think?
--
Regards,
--
Atsushi Torikoshi
Seconded from NTT DATA Japan Corporation to SRA OSS K.K.
Attachments:
v1-0001-provide_hint_when_WHERE_is_used.patchtext/x-diff; name=v1-0001-provide_hint_when_WHERE_is_used.patchDownload
From d1b4e23a1b24782e9de16d748508c46354e42423 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Date: Sun, 3 Aug 2025 17:55:09 +0900
Subject: [PATCH v1] COPY TO: provide hint when WHERE clause is used
Provide a hint suggesting COPY query TO when a WHERE clause
is specified, similar to the hint already given for views or others.
---
src/backend/parser/gram.y | 1 +
1 file changed, 1 insertion(+)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index db43034b9db..9fd48acb1f8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3442,6 +3442,7 @@ CopyStmt: COPY opt_binary qualified_name opt_column_list
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("WHERE clause not allowed with COPY TO"),
+ errhint("Try the COPY (SELECT ... WHERE ...) TO variant."),
parser_errposition(@11)));
n->options = NIL;
--
2.48.1
Hi
On 19.08.25 03:35, torikoshia wrote:
Considering what the user was trying to do, it might be helpful to
provide a similar hint in this case as well.
I’ve attached a patch that adds such a hint.What do you think?
+1
I tested the patch and the error message now has the intended HINT ...
postgres=# COPY t1 TO STDOUT WHERE i > 5;
ERROR: WHERE clause not allowed with COPY TO
LINE 1: COPY t1 TO STDOUT WHERE i > 5;
^
HINT: Try the COPY (SELECT ... WHERE ...) TO variant.
... which aligns with the HINT from COPY TO from views
postgres=# COPY v1 TO STDOUT;
ERROR: cannot copy from view "v1"
HINT: Try the COPY (SELECT ...) TO variant.
Best, Jim
On Sun, Aug 31, 2025 at 3:14 AM Jim Jones <jim.jones@uni-muenster.de> wrote:
Hi
On 19.08.25 03:35, torikoshia wrote:
Considering what the user was trying to do, it might be helpful to
provide a similar hint in this case as well.
I’ve attached a patch that adds such a hint.What do you think?
+1
I tested the patch and the error message now has the intended HINT ...
postgres=# COPY t1 TO STDOUT WHERE i > 5;
ERROR: WHERE clause not allowed with COPY TO
LINE 1: COPY t1 TO STDOUT WHERE i > 5;
^
HINT: Try the COPY (SELECT ... WHERE ...) TO variant.... which aligns with the HINT from COPY TO from views
postgres=# COPY v1 TO STDOUT;
ERROR: cannot copy from view "v1"
HINT: Try the COPY (SELECT ...) TO variant.
Thanks for your review!
Updated the patch to pass regression tests.
Attachments:
v2-0001-COPY-TO-provide-hint-when-WHERE-clause-is-used.patchapplication/x-patch; name=v2-0001-COPY-TO-provide-hint-when-WHERE-clause-is-used.patchDownload
From 90cbddde61c4028a44654005a4e417c74e303d16 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Date: Mon, 1 Sep 2025 07:35:12 +0900
Subject: [PATCH v2] COPY TO: provide hint when WHERE clause is used
Provide a hint suggesting COPY TO when a WHERE clause is
specified, similar to the hint already given for views or others.
---
src/backend/parser/gram.y | 1 +
src/test/regress/expected/copy2.out | 1 +
2 files changed, 2 insertions(+)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index db43034b9db..9fd48acb1f8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3442,6 +3442,7 @@ CopyStmt: COPY opt_binary qualified_name opt_column_list
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("WHERE clause not allowed with COPY TO"),
+ errhint("Try the COPY (SELECT ... WHERE ...) TO variant."),
parser_errposition(@11)));
n->options = NIL;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index caa3c44f0d0..f3fdce23459 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -163,6 +163,7 @@ COPY x TO stdout WHERE a = 1;
ERROR: WHERE clause not allowed with COPY TO
LINE 1: COPY x TO stdout WHERE a = 1;
^
+HINT: Try the COPY (SELECT ... WHERE ...) TO variant.
COPY x from stdin WHERE a = 50004;
COPY x from stdin WHERE a > 60003;
COPY x from stdin WHERE f > 60003;
--
2.48.1
On Mon, Sep 1, 2025 at 7:58 AM Atsushi Torikoshi
<torikoshia.tech@gmail.com> wrote:
On Sun, Aug 31, 2025 at 3:14 AM Jim Jones <jim.jones@uni-muenster.de> wrote:
Hi
On 19.08.25 03:35, torikoshia wrote:
Considering what the user was trying to do, it might be helpful to
provide a similar hint in this case as well.
I’ve attached a patch that adds such a hint.What do you think?
+1
+1
I tested the patch and the error message now has the intended HINT ...
postgres=# COPY t1 TO STDOUT WHERE i > 5;
ERROR: WHERE clause not allowed with COPY TO
LINE 1: COPY t1 TO STDOUT WHERE i > 5;
^
HINT: Try the COPY (SELECT ... WHERE ...) TO variant.... which aligns with the HINT from COPY TO from views
postgres=# COPY v1 TO STDOUT;
ERROR: cannot copy from view "v1"
HINT: Try the COPY (SELECT ...) TO variant.Thanks for your review!
Updated the patch to pass regression tests.
The patch looks good to me. Barring any objections, I'm thinking to
commit the patch.
Regards,
--
Fujii Masao
On 01.09.25 11:09, Fujii Masao wrote:
The patch looks good to me. Barring any objections, I'm thinking to
commit the patch.
LGTM.
The HINT was added to copy2's test output and check-world passes.
Thanks!
Best, Jim
On Mon, Sep 1, 2025 at 7:12 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
On 01.09.25 11:09, Fujii Masao wrote:
The patch looks good to me. Barring any objections, I'm thinking to
commit the patch.LGTM.
I've pushed the patch. Thanks!
Regards,
--
Fujii Masao
On 2025-09-03 08:38, Fujii Masao wrote:
On Mon, Sep 1, 2025 at 7:12 PM Jim Jones <jim.jones@uni-muenster.de>
wrote:On 01.09.25 11:09, Fujii Masao wrote:
The patch looks good to me. Barring any objections, I'm thinking to
commit the patch.LGTM.
I've pushed the patch. Thanks!
Thank you!
--
Regards,
--
Atsushi Torikoshi
Seconded from NTT DATA Japan Corporation to SRA OSS K.K.