Re: COPY example for partial tables

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(at)fetter(dot)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(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!