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+1-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+2-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.