Mark Woodward wrote:
...
>>>> create table as select ...; followed by a copy of that table
>>>> if it really is faster then just the usual select & fetch?
>>> Why "create table?"
>> Just to simulate and time the proposal.
>> SELECT ... already works over the network and if COPY from a
>> select (which would basically work like yet another wire
>> protocol) isnt significantly faster, why bother?
>
> Because the format of COPY is a common transmiter/receiver for PostgreSQL,
> like this:
>
> pg_dump -t mytable | psql -h target -c "COPY mytable FROM STDIN"
>
> With a more selective copy, you can use pretty much this mechanism to
> limit a copy to a sumset of the records in a table.
Ok, but why not just implement this into pg_dump or psql?
Why bother the backend with that functionality?
For example if you copy numbers, int4 (4 bytes)
gets expanded to up to 10 bytes. Of course
can get the same wire load if you use to_char()
with regular select.
>>> The idea is that you would have one or more redundent databases and use
>>> the COPY TO/FROM to keep them up to date.
>> Well, if you have databases you would have regular tables - and
>> can use copy as it is now :-)
>
> But COPY copies all the records, not some of the records.
yes, that would be the point in having them "up to date"
and not "partially maybe something up to date" ;)
COPY is fine for import of data, but for export
I think it should be implemented in the frontend.
Regards
Tino