COPY commands could use an enhancement.

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, Pennsylvania 19026

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