Re: Copying a column from one table to another

On Wed, 15 Mar 2006 17:12:59 -0500
David Gaudine <davidg(at)alcor(dot)concordia(dot)ca> threw this fish to the penguins:

> Suppose I have a table "people" like this;
>
> Name Age
> David 25
> Simon 19
>
> and a table "occupations" like this:
>
> Name Occupation
> David Teacher
> Simon Student
>
> "Name", "Age", and "Occupation" are just the column names, not data.
> The names are unique and one-to-one, i.e. if there's a David in one
> table then there's exactly one David in each.
...
> But, what I would like to know is, how can I copy the column
> "occupation" to the table "people"? That is, I want to create a new
> column people.occupation and populated it from occupations.occupation.
> Feel free to point me to a relevant section of the documentation.

As you just said, you must first create a new column:
alter table people add column occupation text;

then you update the people table with appropriate values:

update people set people.occupation=o.occupation from occupations o where people.name=o.name;

Read about "alter table" and "update" SQL commands in the postgresql manual:
http://www.postgresql.org/docs/8.1/interactive/dml-update.html
http://www.postgresql.org/docs/8.1/interactive/ddl-alter.html

-- George Young

--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)