Skip site navigation (1) Skip section navigation (2)

Re: Copying a column from one table to another

From: george young <gry(at)ll(dot)mit(dot)edu>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Copying a column from one table to another
Date: 2006-03-16 01:52:07
Message-ID: 20060315205207.25a1021d.gry@ll.mit.edu (view raw or flat)
Thread:
Lists: pgsql-novice
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)

In response to

pgsql-novice by date

Next:From: s anwarDate: 2006-03-16 17:38:54
Subject: Re: Exception in thread "main" java.lang.OutOfMemoryError
Previous:From: David GaudineDate: 2006-03-15 22:12:59
Subject: Copying a column from one table to another

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group