Обсуждение: Copy from a SELECT
I know that the COPY command requires that you work with plain tables. Is there any other way at all of export only part of a table
> > I know that the COPY command requires that you work with plain > tables. > > Is there any other way at all of export only part of a table > What about CREATE [ possibly TEMPORARY ]TABLE table_name AS <your-query> and then COPY ... Regards, Christoph
That's exactly what I don't want to do. I've got *very* limited HDD space for the amount of information I need to copy out. The place to where the CSV file is would be output is NTFS and from the Postgres documentation, it states that I should not put the table oid files there and link in order to free up space in the default drive. I've tried using \o to stream the output to a file on the NTFS area, but the machine ran out of memory (memory = 2G). I was looking for a work around to copy out the section of data I wanted to a temporary table and then copy that table out ..... -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Christoph Haller Sent: 27 February 2003 12:45 To: pgsql-sql@postgresql.org Cc: val@webtribe.net Subject: Re: [SQL] Copy from a SELECT > > I know that the COPY command requires that you work with plain > tables. > > Is there any other way at all of export only part of a table > What about CREATE [ possibly TEMPORARY ]TABLE table_name AS <your-query> and then COPY ... Regards, Christoph ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > I've got *very* limited HDD space for the amount of information I need to > copy out. The place to where the CSV file is would be output is NTFS and > from the Postgres documentation, it states that I should not put the table > oid files there and link in order to free up space in the default drive. > > I've tried using \o to stream the output to a file on the NTFS area, but the > machine ran out of memory (memory = 2G). Are you using PgAccess? Did you think about using psql, possibly via script? I can hardly imagine psql would run a 2G machine out of memory. > > I was looking for a work around to copy out the section of data I wanted to > a temporary table and then copy that table out ..... Using \o FILENAME within psql does not even require a temp table, because query results are simply sent to FILENAME. > Or did I misunderstand your intentions completely? Regards, Christoph
I'm not using PgAccess. Using psql with scripts.
The machine did indeed run out of memory. When it happened, I
could not even get 'ps' to run at the Sun prompt. Told me no
memory.
Below are the error messages.
> unexpected character M following empty query response ("I"
message)
> unexpected character T following empty query response ("I"
message)
> unexpected character T following empty query response ("I"
message)
> unexpected character T following empty query response ("I"
message)
> server sent data ("D" message) without prior row description
("T" message)
The amount of data I'm trying to copy out is appr 45G in total
from a single 170G table.
I know \o does not require a tmp table.
At the minute, I do something a bit "naughty"/risky.
I have gzipped the OID files for the other big tables not
involved in the select in order to free up HDD space locally.
Am running this "CREATE table tmp_event_t_2000 AS SELECT * from
event_t where end_t < 978307200;"
Once that is finished running, I will copy out that table as CSV
to the NTFS location drop the tmp_event_t_2000 table, and gunzip
the compressed oid files.
I hope it doesn't screw anything up. I have tested this bodge
method on a test dB on another machine. After I uncompressed
the oid files and re-started the postmaster, I was able to
successfully use the table whose oids where compressed.
I have a tight deadline to get this all done and am desperate.
This project was properly scoped and the person who started it
left. Enter me. Who is now grumpy. But is appreciative of
the help from this mailing list.
Thanks,
Val
>>
>> I've got *very* limited HDD space for the amount of
information I need
>to
>> copy out. The place to where the CSV file is would be output
is NTFS
>and
>> from the Postgres documentation, it states that I should not
put the
>table
>> oid files there and link in order to free up space in the
default
>drive.
>>
>> I've tried using \o to stream the output to a file on the
NTFS area,
>but the
>> machine ran out of memory (memory = 2G).
>Are you using PgAccess? Did you think about using psql,
possibly via
>script?
>I can hardly imagine psql would run a 2G machine out of memory.
>>
>> I was looking for a work around to copy out the section of
data I
>wanted to
>> a temporary table and then copy that table out .....
>Using \o FILENAME within psql does not even require a temp
table,
>because
>query results are simply sent to FILENAME.
>>
>Or did I misunderstand your intentions completely?
>
>Regards, Christoph
>
>
I would suggest creating a view and copying that, but views can't be copied. The basic limitation is that COPY deals with data at the heap level, rather than as a query result/source. Of course, this is done to make it fast. --------------------------------------------------------------------------- Valerie Goodman wrote: > That's exactly what I don't want to do. > > I've got *very* limited HDD space for the amount of information I need to > copy out. The place to where the CSV file is would be output is NTFS and > from the Postgres documentation, it states that I should not put the table > oid files there and link in order to free up space in the default drive. > > I've tried using \o to stream the output to a file on the NTFS area, but the > machine ran out of memory (memory = 2G). > > I was looking for a work around to copy out the section of data I wanted to > a temporary table and then copy that table out ..... > > > > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Christoph Haller > Sent: 27 February 2003 12:45 > To: pgsql-sql@postgresql.org > Cc: val@webtribe.net > Subject: Re: [SQL] Copy from a SELECT > > > > > > I know that the COPY command requires that you work with plain > > tables. > > > > Is there any other way at all of export only part of a table > > > What about > CREATE [ possibly TEMPORARY ]TABLE table_name AS <your-query> > and then COPY ... > > Regards, Christoph > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073