[DOCS] 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 [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 - 1.67 --- doc/src/sgml/ref/copy.sgml 11 Oct 2005 23:00:40 - *** *** 709,714 --- 709,730 +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. This workaround will probably not be +needed for PostgreSQL 8.2 and + later. + + 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; + + + + Here is a sample of data suitable for copying into a table from STDIN: ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] COPY example for partial tables
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.sgml5 Sep 2005 14:44:05 - 1.67 > --- doc/src/sgml/ref/copy.sgml11 Oct 2005 23:00:40 - > *** > *** 709,714 > --- 709,730 > > > > +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. This workaround will probably not be > +needed for PostgreSQL 8.2 and > + later. > + > + 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; > + > + > + > + > Here is a sample of data suitable for copying into a table from > STDIN: > > > ---(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.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [DOCS] 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 [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 - 1.67 > > --- doc/src/sgml/ref/copy.sgml 11 Oct 2005 23:00:40 - > > *** > > *** 709,714 > > --- 709,730 > > > > > > > > +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. This workaround will probably not be > > +needed for PostgreSQL 8.2 and > > + later. > > + > > + 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; > > + > > + > > + > > + > > Here is a sample of data suitable for copying into a table from > > STDIN: > > > > > > > ---(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.comwork: 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! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] COPY example for partial tables
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.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] COPY example for partial tables
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! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[DOCS] Commented, loadable databases as examples?
I would like to see some commented, loadable databases that folks can set up, play with, break, etc. They needn't be huge, just big enough to show off the feature(s) that are being demonstrated. I think this would be a good way to make the documentation a bit more concrete. -r -- email: [EMAIL PROTECTED]; phone: +1 650-873-7841 http://www.cfcl.com- Canta Forda Computer Laboratory http://www.cfcl.com/Meta - The FreeBSD Browser, Meta Project, etc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] Commented, loadable databases as examples?
On Tue, Oct 11, 2005 at 09:57:33PM -0700, Rich Morin wrote: > I would like to see some commented, loadable databases that folks > can set up, play with, break, etc. They needn't be huge, just big > enough to show off the feature(s) that are being demonstrated. I > think this would be a good way to make the documentation a bit more > concrete. Are you looking for something other than the Tutorial and the examples in the source code under src/tutorial? -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
