Subject: COPY example for partial tables

From: David Fetter <[email-masked]>
Date: Tue, 11 Oct 2005 16:02:17 -0700

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 [email-masked] 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>

---

From: "Jim C. Nasby" <[email-masked]>
Date: Tue, 11 Oct 2005 18:12:53 -0500

Why the vacuum? Seems a bit sever to do a vacuum of an entire database
just because you created a temp table.

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org

-- 
Jim C. Nasby, Sr. Engineering Consultant      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: David Fetter <[email-masked]>
Date: Tue, 11 Oct 2005 16:22:40 -0700

> just because you created a temp table.

Excess enthusiasm about reclaiming space.  It doesn't really need to
be there :)

Cheers,
D

Remember to vote!

---

From: "Jim C. Nasby" <[email-masked]>
Date: Tue, 11 Oct 2005 18:33:42 -0500

> Excess enthusiasm about reclaiming space.  It doesn't really need to

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      [email-masked]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---

From: David Fetter <[email-masked]>
Date: Tue, 11 Oct 2005 16:49:32 -0700

On Tue, Oct 11, 2005 at 06:33:42PM -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 :)
> 
> 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

Remember to vote!

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 12 Oct 2005 10:55:17 -0400 (EDT)

> 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
  [email-masked]               |  (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>

---

From: Neil Conway <[email-masked]>
Date: Thu, 13 Oct 2005 00:08:51 -0400

> + COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy';

The capitalization of "a_list_countries" is inconsistent -- both
references should all be in lowercase, IMO.

-Neil

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 13 Oct 2005 10:45:09 -0400 (EDT)

> references should all be in lowercase, IMO.

Fixed.  Thanks.

---

From: David Fetter <[email-masked]>
Date: Thu, 13 Oct 2005 14:26:55 -0700

> references should all be in lowercase, IMO.

Good catch :)

Cheers,
D

Remember to vote!