From: | Alfred Perlstein <bright(at)wintelcom(dot)net> |
---|---|
To: | John Draper <crunch(at)webcrunchers(dot)com> |
Cc: | pgsql-general(at)hub(dot)org |
Subject: | Re: How do I add a column to an existing table. |
Date: | 2000-09-17 20:19:56 |
Message-ID: | 20000917131955.N15156@fw.wintelcom.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
* John Draper <crunch(at)webcrunchers(dot)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(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2000-09-17 20:37:35 | Re: All function parameters become NULL if one is? |
Previous Message | John Draper | 2000-09-17 19:55:04 | How do I add a column to an existing table. |