Thread: COPY commands could use an enhancement.
It would be very helpful if the COPY command could be expanded in order to provide positional parameters. I noticed that it didn't a while back and it can really hurt someone when they happen to try to use pg_dump to move data from one database to another database and they happened to create the feilds in the tables in different orders. Basically: COPY "webmaster" FROM stdin; could become: COPY "webmaster" FIELDS "id", "name", "ssn" FROM stdin; this way when sourcing it would know where to place the feilds. -- -Alfred Perlstein - [alfred@freebsd.org] Daemon News Magazine in your snail-mail! http://magazine.daemonnews.org/
On Mon, 30 Apr 2001, Alfred Perlstein wrote: > Basically: > COPY "webmaster" FROM stdin; > > could become: > COPY "webmaster" FIELDS "id", "name", "ssn" FROM stdin; We'd need some way of making field name dumping optional, because one of the nice things about not having the field names appear is that I can dump, change the field names, and re-slurp in the old dump. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
* Joel Burton <jburton@scw.org> [010430 06:26] wrote: > On Mon, 30 Apr 2001, Alfred Perlstein wrote: > > > Basically: > > COPY "webmaster" FROM stdin; > > > > could become: > > COPY "webmaster" FIELDS "id", "name", "ssn" FROM stdin; > > We'd need some way of making field name dumping optional, because > one of the nice things about not having the field names appear is that > I can dump, change the field names, and re-slurp in the old dump. Of course! I meant this as an additional option, not as a replacement. -- -Alfred Perlstein - [alfred@freebsd.org] Daemon News Magazine in your snail-mail! http://magazine.daemonnews.org/
Alfred Perlstein <bright@wintelcom.net> writes:
> It would be very helpful if the COPY command could be expanded
> in order to provide positional parameters.
I think it's a bad idea to try to expand COPY into a full-tilt data
import/conversion utility, which is the direction that this sort of
suggestion is headed in. COPY is designed as a simple, fast, reliable,
low-overhead data transfer mechanism for backup and restore. The more
warts we add to it, the less well it will serve that purpose.
Example: if we allow selective column import, what do we do with missing
columns? Must COPY now be able to handle insertion of default-value
expressions?
I think it'd be better to put effort into an external data translation
utility that can deal with column selection, data reformatting, CR/LF
conversion, and all those other silly little issues that come up when
you need to move data from one DBMS to another. Sure, we could make
the backend do some of this stuff, but it'd be more maintainable as a
separate program ... IMHO anyway. I think that pgaccess and pgadmin
already have some capability in this line, BTW.
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [010430 08:37] wrote: > Alfred Perlstein <bright@wintelcom.net> writes: > > It would be very helpful if the COPY command could be expanded > > in order to provide positional parameters. > > I think it's a bad idea to try to expand COPY into a full-tilt data > import/conversion utility, which is the direction that this sort of > suggestion is headed in. COPY is designed as a simple, fast, reliable, > low-overhead data transfer mechanism for backup and restore. The more > warts we add to it, the less well it will serve that purpose. Honestly it would be hard for COPY to be any more less serving of people's needs, it really makes sense for it to be able to parse positional paramters for both speed and correctness. > Example: if we allow selective column import, what do we do with missing > columns? What is already done, if you initiate a copy into a 5 column table using only 4 columns of copy data the fifth is left empty. > Must COPY now be able to handle insertion of default-value > expressions? No, copy should be what it is simple but at the same time useful enough for bulk transfer without painful contortions and fear of modifying tables. -- -Alfred Perlstein - [alfred@freebsd.org] Represent yourself, show up at BABUG http://www.babug.org/
> Alfred Perlstein <bright@wintelcom.net> writes: > > It would be very helpful if the COPY command could be expanded > > in order to provide positional parameters. > > I think it's a bad idea to try to expand COPY into a full-tilt data > import/conversion utility, which is the direction that this sort of > suggestion is headed in. COPY is designed as a simple, fast, reliable, > low-overhead data transfer mechanism for backup and restore. The more > warts we add to it, the less well it will serve that purpose. What is really cool is Informix's UNLOAD/LOAD commands. It combines COPY with SELECT/INSERT: UNLOAD TO '/tmp/x'SELECT * FROM tab and LOAD is similar: LOAD FROM '/tmp/x'INSERT INTO TAB This leverages SELECT and INSERT's column and WHERE capabilities to do almost anything you want with flat files. I think it is superior to our COPY. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Karen saw me importing data into a database using pgaccess. Again, this could be useful to someone that it is not a "superuser". But only superusers can use pgaccess. What a shame :-( Fernando P.S.: pgaccess has a much more limited import facility - only text files and you can only change the delimiter. But it can be expanded. Tom Lane wrote: > > Alfred Perlstein <bright@wintelcom.net> writes: > > It would be very helpful if the COPY command could be expanded > > in order to provide positional parameters. > > I think it's a bad idea to try to expand COPY into a full-tilt data > import/conversion utility, which is the direction that this sort of > suggestion is headed in. COPY is designed as a simple, fast, reliable, > low-overhead data transfer mechanism for backup and restore. The more > warts we add to it, the less well it will serve that purpose. > > Example: if we allow selective column import, what do we do with missing > columns? Must COPY now be able to handle insertion of default-value > expressions? > > I think it'd be better to put effort into an external data translation > utility that can deal with column selection, data reformatting, CR/LF > conversion, and all those other silly little issues that come up when > you need to move data from one DBMS to another. Sure, we could make > the backend do some of this stuff, but it'd be more maintainable as a > separate program ... IMHO anyway. I think that pgaccess and pgadmin > already have some capability in this line, BTW. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
On Mon, 30 Apr 2001, Tom Lane wrote: > I think it'd be better to put effort into an external data translation > utility that can deal with column selection, data reformatting, CR/LF > conversion, and all those other silly little issues that come up when > you need to move data from one DBMS to another. Sure, we could make > the backend do some of this stuff, but it'd be more maintainable as a > separate program ... IMHO anyway. I think that pgaccess and pgadmin > already have some capability in this line, BTW. Real conversion should happen in userland. However, allowing people to COPY in a different order does prevent a userland tool from having to re-arrange a dump file. (Of course, really, with perl, re-ordering a dump file should take more than a few lines anyway.) Are there any generalized tools for re-ordering delimited columns, without having to use sed/perl/regexes, etc.? If people can point to some best practices/ideas, I'd be happy to turn them into a HOWTO. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
At 11:36 30/04/01 -0400, Tom Lane wrote: > >COPY is designed as a simple, fast, reliable, >low-overhead data transfer mechanism for backup and restore. The more >warts we add to it, the less well it will serve that purpose. > Do you have a alternate suggestion as to how to solve the problems it has backing up the regression DB? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
> Do you have a alternate suggestion as to how to solve the problems it has
> backing up the regression DB?
One possibility is to fix ALTER TABLE ADD COLUMN to maintain the same
column ordering in parents and children.
COPY with specified columns may in fact be the best way to deal with
that particular issue, if pg_dump is all we care about fixing. However
there are a bunch of things that have a problem with it, not only
pg_dump. See thread over in committers about functions and inheritance.
regards, tom lane