COPY example for partial tables
Folks,
Please find enclosed a patch (should work for 7.3 and up) that
illustrates a workaround for using COPY on parts of tables using
temporary tables. It's helped me, and it seems popular via a very
brief and un-scientific poll.
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Attachments:
copy_example.difftext/plain; charset=us-asciiDownload+16-0
Why the vacuum? Seems a bit sever to do a vacuum of an entire database
just because you created a temp table.
On Tue, Oct 11, 2005 at 04:02:17PM -0700, David Fetter wrote:
Folks,
Please find enclosed a patch (should work for 7.3 and up) that
illustrates a workaround for using COPY on parts of tables using
temporary tables. It's helped me, and it seems popular via a very
brief and un-scientific poll.Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778Remember to vote!
Index: doc/src/sgml/ref/copy.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.67 diff -c -r1.67 copy.sgml *** doc/src/sgml/ref/copy.sgml 5 Sep 2005 14:44:05 -0000 1.67 --- doc/src/sgml/ref/copy.sgml 11 Oct 2005 23:00:40 -0000 *************** *** 709,714 **** --- 709,730 ---- </para><para> + To copy just the countries whose names start with 'A' into a file + using a temporary table which goes away at the end of the + transaction. <note><para>This workaround will probably not be + needed for <productname>PostgreSQL</productname> 8.2 and + later.</para></note> + <programlisting> + BEGIN; + CREATE TEMP TABLE a_list_COUNTRIES AS + SELECT * FROM country WHERE country_name LIKE 'A%'; + COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy'; + ROLLBACK; + VACUUM; + </programlisting> + </para> + + <para> Here is a sample of data suitable for copying into a table from <literal>STDIN</literal>: <programlisting>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, Oct 11, 2005 at 06:12:53PM -0500, Jim C. Nasby wrote:
Why the vacuum? Seems a bit sever to do a vacuum of an entire database
just because you created a temp table.
Excess enthusiasm about reclaiming space. It doesn't really need to
be there :)
Cheers,
D
On Tue, Oct 11, 2005 at 04:02:17PM -0700, David Fetter wrote:
Folks,
Please find enclosed a patch (should work for 7.3 and up) that
illustrates a workaround for using COPY on parts of tables using
temporary tables. It's helped me, and it seems popular via a very
brief and un-scientific poll.Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778Remember to vote!
Index: doc/src/sgml/ref/copy.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.67 diff -c -r1.67 copy.sgml *** doc/src/sgml/ref/copy.sgml 5 Sep 2005 14:44:05 -0000 1.67 --- doc/src/sgml/ref/copy.sgml 11 Oct 2005 23:00:40 -0000 *************** *** 709,714 **** --- 709,730 ---- </para><para> + To copy just the countries whose names start with 'A' into a file + using a temporary table which goes away at the end of the + transaction. <note><para>This workaround will probably not be + needed for <productname>PostgreSQL</productname> 8.2 and + later.</para></note> + <programlisting> + BEGIN; + CREATE TEMP TABLE a_list_COUNTRIES AS + SELECT * FROM country WHERE country_name LIKE 'A%'; + COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy'; + ROLLBACK; + VACUUM; + </programlisting> + </para> + + <para> Here is a sample of data suitable for copying into a table from <literal>STDIN</literal>: <programlisting>---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
On Tue, Oct 11, 2005 at 04:22:40PM -0700, David Fetter wrote:
On Tue, Oct 11, 2005 at 06:12:53PM -0500, Jim C. Nasby wrote:
Why the vacuum? Seems a bit sever to do a vacuum of an entire database
just because you created a temp table.Excess enthusiasm about reclaiming space. It doesn't really need to
be there :)
I think it needs to be commented on, one way or another. Better to
explain that this will slowly bloat pg_class than have a mystery vacuum
that many people have no idea why it's there...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, Oct 11, 2005 at 06:33:42PM -0500, Jim C. Nasby wrote:
On Tue, Oct 11, 2005 at 04:22:40PM -0700, David Fetter wrote:
On Tue, Oct 11, 2005 at 06:12:53PM -0500, Jim C. Nasby wrote:
Why the vacuum? Seems a bit sever to do a vacuum of an entire
database just because you created a temp table.Excess enthusiasm about reclaiming space. It doesn't really need
to be there :)I think it needs to be commented on, one way or another. Better to
explain that this will slowly bloat pg_class than have a mystery
vacuum that many people have no idea why it's there...
Patch fixes always welcome :)
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
David Fetter wrote:
Folks,
Please find enclosed a patch (should work for 7.3 and up) that
illustrates a workaround for using COPY on parts of tables using
temporary tables. It's helped me, and it seems popular via a very
brief and un-scientific poll.
I have attached and applied a modified version of this patch. I removed
the VACUUM (because this is just an example and does not need to be a
complete solution, e.g. pg_class bloat), and removed the 8.2 mention
because it seemed unnecessary.
--
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, Pennsylvania 19073Attachments:
/rtmp/difftext/plainDownload+13-0
On Wed, 2005-12-10 at 10:55 -0400, Bruce Momjian wrote:
<para> + To copy into a file just the countries whose names start with 'A' + using a temporary table which is automatically deleted: + </para> + <programlisting> + BEGIN; + CREATE TEMP TABLE a_list_COUNTRIES AS + SELECT * FROM country WHERE country_name LIKE 'A%'; + COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy'; + ROLLBACK; + </programlisting> + </para>
The capitalization of "a_list_countries" is inconsistent -- both
references should all be in lowercase, IMO.
-Neil
Neil Conway wrote:
On Wed, 2005-12-10 at 10:55 -0400, Bruce Momjian wrote:
<para> + To copy into a file just the countries whose names start with 'A' + using a temporary table which is automatically deleted: + </para> + <programlisting> + BEGIN; + CREATE TEMP TABLE a_list_COUNTRIES AS + SELECT * FROM country WHERE country_name LIKE 'A%'; + COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy'; + ROLLBACK; + </programlisting> + </para>The capitalization of "a_list_countries" is inconsistent -- both
references should all be in lowercase, IMO.
Fixed. Thanks.
--
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, Pennsylvania 19073On Thu, Oct 13, 2005 at 12:08:51AM -0400, Neil Conway wrote:
On Wed, 2005-12-10 at 10:55 -0400, Bruce Momjian wrote:
<para> + To copy into a file just the countries whose names start with 'A' + using a temporary table which is automatically deleted: + </para> + <programlisting> + BEGIN; + CREATE TEMP TABLE a_list_COUNTRIES AS + SELECT * FROM country WHERE country_name LIKE 'A%'; + COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy'; + ROLLBACK; + </programlisting> + </para>The capitalization of "a_list_countries" is inconsistent -- both
references should all be in lowercase, IMO.
Good catch :)
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!