Thread: inserting columns with values from a different table
Hello all, This may be (hopefully) a very basic question, but I (admittedly, a pgsql newbie) cannot seem to be able to find an answer to it :( I have in my database tables, say A, B, and C, all of them referring to the same objects (e.g. table A contains customer's name, table B his/her purchases, and table C his/her address). I want to create a "master" table D whose rows would have some values from table A, some from B, and some from C (e.g. name, last purchase, and address). I know I can do an INSERT with SELECT - but that will only allow me to insert values from one table, not from all three (or more). Is there any way to do this in PostgreSQL? I do realize that I could likely imitate everything I need from a master table by having many different tables and executing [more or less] complicated queries, but having a "master" table would greatly simplify my life and avoid multiple potential mistakes when constructing those queries... Thanks! Alex
You could create D as a VIEW with what you need from A, B and C. Then create some RULEs on that view to perform your INSERTs, UPDATEs, and DELETEs. I'm not sure that will make your life easier, but that decision is yours to make :) Greg ----- Original Message ----- From: "Alexander Turchin" <aturchin@chip.org> To: "PostgreSQL Mailing List" <pgsql-general@postgresql.org> Sent: Sunday, July 08, 2001 7:23 PM Subject: inserting columns with values from a different table > Hello all, > > This may be (hopefully) a very basic question, but I (admittedly, a > pgsql newbie) cannot seem to be able to find an answer to it :( > > I have in my database tables, say A, B, and C, all of them referring to > the same objects (e.g. table A contains customer's name, table B his/her > purchases, and table C his/her address). I want to create a "master" > table D whose rows would have some values from table A, some from B, and > some from C (e.g. name, last purchase, and address). I know I can do an > INSERT with SELECT - but that will only allow me to insert values from > one table, not from all three (or more). Is there any way to do this in > PostgreSQL? > > I do realize that I could likely imitate everything I need from a master > table by having many different tables and executing [more or less] > complicated queries, but having a "master" table would greatly simplify > my life and avoid multiple potential mistakes when constructing those > queries... > > Thanks! > > Alex > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Sun, 8 Jul 2001, Alexander Turchin wrote:
> This may be (hopefully) a very basic question, but I (admittedly, a
> pgsql newbie) cannot seem to be able to find an answer to it :(
>
> I have in my database tables, say A, B, and C, all of them referring to
> the same objects (e.g. table A contains customer's name, table B his/her
> purchases, and table C his/her address). I want to create a "master"
> table D whose rows would have some values from table A, some from B, and
> some from C (e.g. name, last purchase, and address). I know I can do an
> INSERT with SELECT - but that will only allow me to insert values from
> one table, not from all three (or more). Is there any way to do this in
> PostgreSQL?
>
> I do realize that I could likely imitate everything I need from a master
> table by having many different tables and executing [more or less]
> complicated queries, but having a "master" table would greatly simplify
> my life and avoid multiple potential mistakes when constructing those
> queries...
You may want to check out CREATE VIEW: from within psql:
\h create view
Command: CREATE VIEW
Description: Constructs a virtual table
Syntax:
CREATE VIEW view AS SELECT query
More information is available in the documentation.
-frank
On Sun, 8 Jul 2001, Alexander Turchin wrote: > Hello all, > > This may be (hopefully) a very basic question, but I (admittedly, a > pgsql newbie) cannot seem to be able to find an answer to it :( > > I have in my database tables, say A, B, and C, all of them referring to > the same objects (e.g. table A contains customer's name, table B his/her > purchases, and table C his/her address). I want to create a "master" > table D whose rows would have some values from table A, some from B, and > some from C (e.g. name, last purchase, and address). I know I can do an > INSERT with SELECT - but that will only allow me to insert values from > one table, not from all three (or more). Is there any way to do this in > PostgreSQL? Although you probably really want a view, insert...select should take values from multiple tables if the select has multiple tables in its from. insert into table select foo.col1, foo2.col2 from foo,foo2; seems to work for me.
Gregory Wood wrote: > > You could create D as a VIEW with what you need from A, B and C. Then create > some RULEs on that view to perform your INSERTs, UPDATEs, and DELETEs. > > I'm not sure that will make your life easier, but that decision is yours to > make :) > Ah updateable views, something for PG 7.2? Nils -- Alles van waarde is weerloos Lucebert
> > You could create D as a VIEW with what you need from A, B and C. Then create > > some RULEs on that view to perform your INSERTs, UPDATEs, and DELETEs. > > > > I'm not sure that will make your life easier, but that decision is yours to > > make :) > > > > Ah updateable views, something for PG 7.2? Nope, you can do them now, you just have to spell everything out explicitly for PostgreSQL: http://postgresql.bteg.net/users-lounge/docs/7.1/programmer/rules-insert.htm l#AEN7370 I believe the feature you are referring to is the automatic generation of RULEs for INSERTing, UPDATEing and DELETEing from VIEWs. Greg