Thread: Re: More PostgreSQL stuff

Martin Schulze wrote:
  >Hi Oliver!
  >
  >I have some more questions wrt. PostgreSQL.

Forwarded to PostgreSQL list, since I cannot answer them.

  > . Why does pg_dump only write some SQL commands in capital letters
  >   but not all?  I wonder if that's intentional.  Please look at the
  >   following excerpt.  I would have expected CHAR, VARCHAR, INSERT
  >   INTO, VALUES etc. to occur in capital letters.
  >
  >   CREATE TABLE zeitungen (name char(30), typ char(10), ...
  >
  >   insert into zeitungen values ('Mallorca Immobilien           ',...
  >
  > . I wonder how one could add or remove columns from existing tables.
  >
  >   With mSQL this was possible with a trick.  You had to dump the
  >   whole table but you could tell the dump program to add dummy fields
  >   or to leave out existing fields.  Is there any such possibility
  >   with PostgreSQL?


--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Set your affection on things above, not on things on
      the earth."          Colossians 3:2

On Fri, 2 Oct 1998, Oliver Elphick wrote:

>   > . I wonder how one could add or remove columns from existing tables.
>   >
>   >   With mSQL this was possible with a trick.  You had to dump the
>   >   whole table but you could tell the dump program to add dummy fields
>   >   or to leave out existing fields.  Is there any such possibility
>   >   with PostgreSQL?

    chk out the 'ALTER TABLE' man page...you can add columns quite
easily using this.  removing coloumsn is a little more tricky at this
point in time, but we're hoping to add it to the ALTER TABLE syntax 'in
the future'.

    Basically, to drop a column, do an 'INSERT INTO...SELECT FROM' (my
syntax may be slightly off, please check man pages) from the table you
want to remove the column from, such that you only insert the columns you
want to remain.  Then, when finished, do a ... damn, haven't used this one
yet, so don't remember what the 'command' is...but there is one to do a
RENAME of a table...rename the old to something different, and rename the
new to the old...

Marc G. Fournier                               scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org                       ICQ#7615664

> On Fri, 2 Oct 1998, Oliver Elphick wrote:
>
> >   > . I wonder how one could add or remove columns from existing tables.
> >   >
> >   >   With mSQL this was possible with a trick.  You had to dump the
> >   >   whole table but you could tell the dump program to add dummy fields
> >   >   or to leave out existing fields.  Is there any such possibility
> >   >   with PostgreSQL?
>
>     chk out the 'ALTER TABLE' man page...you can add columns quite
> easily using this.  removing coloumsn is a little more tricky at this
> point in time, but we're hoping to add it to the ALTER TABLE syntax 'in
> the future'.
>
>     Basically, to drop a column, do an 'INSERT INTO...SELECT FROM' (my
> syntax may be slightly off, please check man pages) from the table you
> want to remove the column from, such that you only insert the columns you
> want to remain.  Then, when finished, do a ... damn, haven't used this one
> yet, so don't remember what the 'command' is...but there is one to do a
> RENAME of a table...rename the old to something different, and rename the
> new to the old...

This is an FAQ item.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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