Re: Column reordering in pg_dump

On Friday 14 November 2008 13:37:05 hernan gonzalez wrote:
> On Fri, Nov 14, 2008 at 4:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "hernan gonzalez" <hgonzalez@gmail.com> writes:
> >> I've added an option to pg_dump to reorder
> >> columns in the ouput "CREATE TABLE" dump.
> >
> > This doesn't seem like a particularly good idea to me.  In the first
> > place, pg_dump is a tool for reproducing your database, not altering it,
> > so it seems like basically the wrong place to be inserting this type of
> > feature.  (There's been some talk of a Postgres ETL tool, which would be
> > the right place, but so far it's only talk :-(.)  In the second place,
> > column order is actually a pretty delicate affair when you start to
> > think about table inheritance situations and tables that have been
> > altered via ADD/DROP COLUMN.  We had bugs in pg_dump in the past with
> > its ability to deal with column order in such cases.  So I'm not nearly
> > as optimistic as you are that such a feature is incapable of causing
> > problems.
> >
> >                        regards, tom lane
> >
> > In the first placeplace, pg_dump is a tool for reproducing your database,
> > not altering it
>
> Yes, but the standard/recommended procedure for reorder columns in
> postgresql is "pg_dump , edit , restore". I just didn't want to mess
> editing a dump.
>

it's one method, but not the only method, see 
http://wiki.postgresql.org/wiki/Alter_column_position

> Of couse, the standard behavior of pg_dump is not altered when the
> "reorder" option
> is not use. And bear in mind that the reordering hook is guaranteed to
> alter only the order
> of the "CREATE TABLE"  fields. (The original and the modified dump
> will differ only in that;
> even in the case of dropped columns and inherited tables). The only
> possible troubling scenario
> I can imagine: a dump using a COPY without columns names in the data
> dump; but that
> only arises with version < 7.3.
>

yeah, i remember using that trick a lot... ah the good ole days :-P

-- 
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com