Re: How do I add a column to an existing table.

* John Draper <crunch@webcrunchers.com> [000917 12:58] wrote:
> I have this table I call "new_users".    The existing first col of that
> table is "first_name".     I want to add an additional column in FRONT of
> the existing first column of "first_name"...
>
> Existing colums...    first_name | last_name |  <etc>
>
> I want to add...     key | first_name | last_name | <etc>
>
> How is that done,    then I want to automatically insert into "key" a
> sequence of numbers like 1,2,3,4,5,6,7,  etc so that each number is
> sequentially numbered.

If you don't mind taking the table offline for a bit here's a semi-simple
way of doing that.

use pgdump to dump the table as "data, no schema" and "insert
statements" the output file should look something like this:

insert into new_users (first_name, last_name, whatever) values
   ('frank', 'footz', 'something');
insert into new_users (first_name, last_name, whatever) values
   ('frank', 'footz', 'something');
insert into new_users (first_name, last_name, whatever) values
   ('frank', 'footz', 'something');
etc..

then back that file up, then drop the table and recreate it but _first_
add the column key as a type 'sequence'.

You then should be able to run the insert statements and get a unique
key for each row.

Another way would be to just add the column, then run a query to set
the key == 'oid', you'd then have to create a sequence, and set it's
value to 'max(key)' then make the key column's default to
'nextval(''your sequewnce name'')' unfortunatly you can't make 'key'
the first column.

Just a hint:  relying on column placement is a _bad thing_ _always_
refer to columns by name (see my insert statements above) and things
will be much better for you.

> I'm very new to SQL so I suppose this is prolly a pretty dumb question.
> The docs on ALTER show how to add a column,   but NOT how to add the column
> to the beginning,    or after adding a column,   to re-arrange them so that
> the KEY column is first.     Could someone please help me?

Er, I don't think it's possible, re-read my paragraph above about relying
on column order.

> I have another related question.    I know it is possible to "Join" tables,
> creating a 3rd table.    But can I take this 3rd table and add this table
> to my list of tables in my database?      If so,    now do I do that?

Yup, it's possible, what you want to do is create a 'VIEW' which
is a read-only table that is the result of a query on two other tables.

The process is described in the handbook.

best of luck,
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."