MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="=_4908d701b09ab9c08544eb12ac1b1ed5"
Date: Tue, 19 Aug 2025 10:35:28 +0900
From: torikoshia <torikoshia@oss.nttdata.com>
To: pgsql-hackers@lists.postgresql.org
Subject: COPY TO: provide hint when WHERE clause is used
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <3520c224c5ffac0113aef84a9179f37e@oss.nttdata.com>
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/3520c224c5ffac0113aef84a9179f37e%40oss.nttdata.com>
Precedence: bulk

--=_4908d701b09ab9c08544eb12ac1b1ed5
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: quoted-printable

Hi,

I saw a user wanted to restrict the rows copied by COPY TO using a WHERE=20
clause, but ran into an error.

As you know, COPY FROM supports WHERE, but COPY TO does not:

   =3D# 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:

   =3D# copy (select i from t1 where i > 5) to stdout;

However, as shown in the first example, no hint is provided in the error=20
message.

For views or others, COPY TO already provides a helpful hint message:

   =3D# 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=20
provide a similar hint in this case as well.
I=E2=80=99ve attached a patch that adds such a hint.

What do you think?


--=20
Regards,

--
Atsushi Torikoshi
Seconded from NTT DATA Japan Corporation to SRA OSS K.K.
--=_4908d701b09ab9c08544eb12ac1b1ed5
Content-Transfer-Encoding: base64
Content-Type: text/x-diff;
 name=v1-0001-provide_hint_when_WHERE_is_used.patch
Content-Disposition: attachment;
 filename=v1-0001-provide_hint_when_WHERE_is_used.patch;
 size=945

RnJvbSBkMWI0ZTIzYTFiMjQ3ODJlOWRlMTZkNzQ4NTA4YzQ2MzU0ZTQyNDIzIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBdHN1c2hpIFRvcmlrb3NoaSA8dG9yaWtvc2hpYUBvc3MubnR0
ZGF0YS5jb20+CkRhdGU6IFN1biwgMyBBdWcgMjAyNSAxNzo1NTowOSArMDkwMApTdWJqZWN0OiBb
UEFUQ0ggdjFdIENPUFkgVE86IHByb3ZpZGUgaGludCB3aGVuIFdIRVJFIGNsYXVzZSBpcyB1c2Vk
CgpQcm92aWRlIGEgaGludCBzdWdnZXN0aW5nIENPUFkgcXVlcnkgVE8gd2hlbiBhIFdIRVJFIGNs
YXVzZQppcyBzcGVjaWZpZWQsIHNpbWlsYXIgdG8gdGhlIGhpbnQgYWxyZWFkeSBnaXZlbiBmb3Ig
dmlld3Mgb3Igb3RoZXJzLgoKLS0tCiBzcmMvYmFja2VuZC9wYXJzZXIvZ3JhbS55IHwgMSArCiAx
IGZpbGUgY2hhbmdlZCwgMSBpbnNlcnRpb24oKykKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9w
YXJzZXIvZ3JhbS55IGIvc3JjL2JhY2tlbmQvcGFyc2VyL2dyYW0ueQppbmRleCBkYjQzMDM0Yjlk
Yi4uOWZkNDhhY2IxZjggMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3BhcnNlci9ncmFtLnkKKysr
IGIvc3JjL2JhY2tlbmQvcGFyc2VyL2dyYW0ueQpAQCAtMzQ0Miw2ICszNDQyLDcgQEAgQ29weVN0
bXQ6CUNPUFkgb3B0X2JpbmFyeSBxdWFsaWZpZWRfbmFtZSBvcHRfY29sdW1uX2xpc3QKIAkJCQkJ
CWVyZXBvcnQoRVJST1IsCiAJCQkJCQkJCShlcnJjb2RlKEVSUkNPREVfU1lOVEFYX0VSUk9SKSwK
IAkJCQkJCQkJIGVycm1zZygiV0hFUkUgY2xhdXNlIG5vdCBhbGxvd2VkIHdpdGggQ09QWSBUTyIp
LAorCQkJCQkJCQkgZXJyaGludCgiVHJ5IHRoZSBDT1BZIChTRUxFQ1QgLi4uIFdIRVJFIC4uLikg
VE8gdmFyaWFudC4iKSwKIAkJCQkJCQkJIHBhcnNlcl9lcnJwb3NpdGlvbihAMTEpKSk7CiAKIAkJ
CQkJbi0+b3B0aW9ucyA9IE5JTDsKLS0gCjIuNDguMQoK
--=_4908d701b09ab9c08544eb12ac1b1ed5--