Thread: 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!
Attachment
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 3778 > > Remember 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? > > http://archives.postgresql.org -- 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 3778 > > > > Remember 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? > > > > http://archives.postgresql.org > > > -- > 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 19073 Index: doc/src/sgml/ref/copy.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.67 diff -c -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 12 Oct 2005 14:53:17 -0000 *************** *** 709,714 **** --- 709,727 ---- </para> <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> + + <para> Here is a sample of data suitable for copying into a table from <literal>STDIN</literal>: <programlisting>
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 19073
On 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!