public inbox for [email protected]
help / color / mirror / Atom feedCOPY example for partial tables
9+ messages / 4 participants
[nested] [flat]
* COPY example for partial tables
@ 2005-10-11 23:02 David Fetter <[email protected]>
2005-10-11 23:12 ` Re: COPY example for partial tables Jim C. Nasby <[email protected]>
2005-10-12 14:55 ` Re: COPY example for partial tables Bruce Momjian <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: David Fetter @ 2005-10-11 23:02 UTC (permalink / raw)
To: pgsql-docs
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 protected] 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>
Attachments:
[text/plain] copy_example.diff (1.1K, 2-copy_example.diff)
download | inline diff:
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>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: COPY example for partial tables
2005-10-11 23:02 COPY example for partial tables David Fetter <[email protected]>
@ 2005-10-11 23:12 ` Jim C. Nasby <[email protected]>
2005-10-11 23:22 ` Re: COPY example for partial tables David Fetter <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Jim C. Nasby @ 2005-10-11 23:12 UTC (permalink / raw)
To: David Fetter <[email protected]>; +Cc: pgsql-docs
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 [email protected] 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 [email protected]
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: COPY example for partial tables
2005-10-11 23:02 COPY example for partial tables David Fetter <[email protected]>
2005-10-11 23:12 ` Re: COPY example for partial tables Jim C. Nasby <[email protected]>
@ 2005-10-11 23:22 ` David Fetter <[email protected]>
2005-10-11 23:33 ` Re: COPY example for partial tables Jim C. Nasby <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: David Fetter @ 2005-10-11 23:22 UTC (permalink / raw)
To: Jim C. Nasby <[email protected]>; +Cc: pgsql-docs
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 [email protected] 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 [email protected]
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
--
David Fetter [email protected] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: COPY example for partial tables
2005-10-11 23:02 COPY example for partial tables David Fetter <[email protected]>
2005-10-11 23:12 ` Re: COPY example for partial tables Jim C. Nasby <[email protected]>
2005-10-11 23:22 ` Re: COPY example for partial tables David Fetter <[email protected]>
@ 2005-10-11 23:33 ` Jim C. Nasby <[email protected]>
2005-10-11 23:49 ` Re: COPY example for partial tables David Fetter <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Jim C. Nasby @ 2005-10-11 23:33 UTC (permalink / raw)
To: David Fetter <[email protected]>; +Cc: pgsql-docs
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 [email protected]
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: COPY example for partial tables
2005-10-11 23:02 COPY example for partial tables David Fetter <[email protected]>
2005-10-11 23:12 ` Re: COPY example for partial tables Jim C. Nasby <[email protected]>
2005-10-11 23:22 ` Re: COPY example for partial tables David Fetter <[email protected]>
2005-10-11 23:33 ` Re: COPY example for partial tables Jim C. Nasby <[email protected]>
@ 2005-10-11 23:49 ` David Fetter <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: David Fetter @ 2005-10-11 23:49 UTC (permalink / raw)
To: Jim C. Nasby <[email protected]>; +Cc: pgsql-docs
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 [email protected] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: COPY example for partial tables
2005-10-11 23:02 COPY example for partial tables David Fetter <[email protected]>
@ 2005-10-12 14:55 ` Bruce Momjian <[email protected]>
2005-10-13 04:08 ` Re: COPY example for partial tables Neil Conway <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2005-10-12 14:55 UTC (permalink / raw)
To: David Fetter <[email protected]>; +Cc: pgsql-docs
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
[email protected] | (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>
Attachments:
[text/plain] /rtmp/diff (924B, 2-%2Frtmp%2Fdiff)
download | inline:
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>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: COPY example for partial tables
2005-10-11 23:02 COPY example for partial tables David Fetter <[email protected]>
2005-10-12 14:55 ` Re: COPY example for partial tables Bruce Momjian <[email protected]>
@ 2005-10-13 04:08 ` Neil Conway <[email protected]>
2005-10-13 14:45 ` Re: COPY example for partial tables Bruce Momjian <[email protected]>
2005-10-13 21:26 ` Re: COPY example for partial tables David Fetter <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: Neil Conway @ 2005-10-13 04:08 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: David Fetter <[email protected]>; pgsql-docs
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: COPY example for partial tables
2005-10-11 23:02 COPY example for partial tables David Fetter <[email protected]>
2005-10-12 14:55 ` Re: COPY example for partial tables Bruce Momjian <[email protected]>
2005-10-13 04:08 ` Re: COPY example for partial tables Neil Conway <[email protected]>
@ 2005-10-13 14:45 ` Bruce Momjian <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2005-10-13 14:45 UTC (permalink / raw)
To: Neil Conway <[email protected]>; +Cc: David Fetter <[email protected]>; pgsql-docs
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
[email protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: COPY example for partial tables
2005-10-11 23:02 COPY example for partial tables David Fetter <[email protected]>
2005-10-12 14:55 ` Re: COPY example for partial tables Bruce Momjian <[email protected]>
2005-10-13 04:08 ` Re: COPY example for partial tables Neil Conway <[email protected]>
@ 2005-10-13 21:26 ` David Fetter <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: David Fetter @ 2005-10-13 21:26 UTC (permalink / raw)
To: Neil Conway <[email protected]>; +Cc: Bruce Momjian <[email protected]>; pgsql-docs
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 [email protected] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-10-13 21:26 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-11 23:02 COPY example for partial tables David Fetter <[email protected]>
2005-10-11 23:12 ` Jim C. Nasby <[email protected]>
2005-10-11 23:22 ` David Fetter <[email protected]>
2005-10-11 23:33 ` Jim C. Nasby <[email protected]>
2005-10-11 23:49 ` David Fetter <[email protected]>
2005-10-12 14:55 ` Bruce Momjian <[email protected]>
2005-10-13 04:08 ` Neil Conway <[email protected]>
2005-10-13 14:45 ` Bruce Momjian <[email protected]>
2005-10-13 21:26 ` David Fetter <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox