Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add HINT for COPY TO when WHERE clause is used.

  1. COPY TO: provide hint when WHERE clause is used

    torikoshia <torikoshia@oss.nttdata.com> — 2025-08-19T01:35:28Z

    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.

  2. Re: COPY TO: provide hint when WHERE clause is used

    Jim Jones <jim.jones@uni-muenster.de> — 2025-08-30T18:14:29Z

    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

  3. Re: COPY TO: provide hint when WHERE clause is used

    Atsushi Torikoshi <torikoshia.tech@gmail.com> — 2025-08-31T22:58:21Z

    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.

  4. Re: COPY TO: provide hint when WHERE clause is used

    Fujii Masao <masao.fujii@gmail.com> — 2025-09-01T09:09:38Z

    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

  5. Re: COPY TO: provide hint when WHERE clause is used

    Jim Jones <jim.jones@uni-muenster.de> — 2025-09-01T10:12:57Z

    
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

  6. Re: COPY TO: provide hint when WHERE clause is used

    Fujii Masao <masao.fujii@gmail.com> — 2025-09-02T23:38:31Z

    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

  7. Re: COPY TO: provide hint when WHERE clause is used

    torikoshia <torikoshia@oss.nttdata.com> — 2025-09-03T00:08:10Z

    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.