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

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