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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "hernan gonzalez" <hgonzalez(at)gmail(dot)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