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

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.

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?

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?

John

* 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."

>Please don't take offense but there's a few points you need to
>realize:
>
>1) I'm just a volunteer and don't have time to research the
>   exact sequence of commands for you, (it looks like you didn't
>   even try to read the docs between getting my response and
>   emailing me back)

I did read the docs and I don't understand them,  and I cannot possibly
guess what commend to make...  I can ONLY learn by example.

>2) you need to read the docs.

I did - over and over and over again... linearly,   and reference it
constantly - their examples do NOT cover every case.

>3) in the future please post your replies back to the main list
>   so that if I happen to go someplace else or miss your mail someone
>   else may be able to pick it up.

Ok,    I was just trying to cut down on bandwidth,  thats all.
>
>read on, I'll give you more info on how to achive what you want.

>Of course you care, pg_dump can be told to dump to an output file.
>
>And don't loose it!  in fact it's probably a good idea to view it
>and make sure it contains all your data then making sure you back
>it up to another machine before dropping the table.
>
>> >You then should be able to run the insert statements and get a unique
>> >key for each row.
>>
>> Again,   an exact set of commands to do this,   would be most helpful.
>> I'm having problems understanding the Docs.   SQL is really new to me.
>
>you should be able to just run 'psql < dumpfile' and psql will run
>all the commmands in the file to do the insertion.

Do I run this from the system shell promot like "bash" or do I run it from
within the "psql" program?   And where in the docs do they explain this?
>
>> >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.

I'm just guessing here,   but would it look like this?

CREATE SEQUENCE users_seq;    <--- to create the sequence

CREATE TABLE temp_users (key int DEFAULT nextval('users_seq'),  first_name
char(15),  last_name char(15)    <etc>

Is THAT how I would do it?

John