Re: in BeginCopyTo make materialized view using COPY TO instead of COPY (query).

On Mon, Mar 31, 2025 at 8:13 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:

> On Mon, Mar 31, 2025 at 11:27 PM Fujii Masao
>
> >
> > The copy.sgml documentation should clarify that COPY TO can
> > be used with a materialized view only if it is populated.
> >
> "COPY TO can be used only with plain tables, not views, and does not
> copy rows from child tables or child partitions"
> i changed it to
> "COPY TO can be used with plain tables and materialized views, not
> regular views, and does not copy rows from child tables or child
> partitions"
>
> Another alternative wording I came up with:
> "COPY TO can only be used with plain tables and materialized views,
> not regular views. It also does not copy rows from child tables or
> child partitions."
>
>
Those don't address the "populated" aspect of the materialized view.

I'm unclear ATM (can check later if needed) if populated means "non-empty"
or simply "had refresh executed at least once on it". i.e., if the refresh
produces zero rows stored in the MV is it still populated? I'm guessing
yes; and this only pertains to "WITH NO DATA", which itself already calls
out that "...and cannot be queried until RMV is used". I find it of
marginal usefulness to bring that distinction over to COPY TO absent people
showing up confused about the error message, which likely will be quite
rare. That said I'd probably settle with:

COPY TO can only be used with plain tables and populated
materialized views. It does not copy rows from child tables
or child partitions (i.e., copy table to copies the same rows as
select * from only table). The syntax COPY (select * from table) TO ...
can be used to dump all of the rows in an inheritance hierarchy,
partitioned table, or foreign table; as well as ordinary view results.

Curious about sequences; no way to name an index here.

I'm second-guessing why "composite type" shows up in the glossary under
"Relation"...though that originally came up IIRC discussing namespaces.

David J.