Thread: newbie question
Dear users.. I have fastly created a table in a postgresql database.. some columns where edited by hand (columns A, B, C), and some others (columns D, E, F) have been calculated as a result of mathematical equation (where the factors are the A, B, C columns).... now I simply need to change some values in the A, B, C columns and I would like to obtain the correct values in the D, E, F column... I know that this is a tipical problem of a spreadsheet but how can I solve it with a DBMS?? there a way to impose some constrain, also after the table is already filled with values?? I need only some suggestions to start.. then I can go on by myself!! thank you very much Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: marchesini@unipg.it ivan.marchesini@gmail.com tel: +39(0)755853760 fax: +39(0)755853756
ivan marchesini <marchesini@unipg.it> schrieb: > Dear users.. > I have fastly created a table in a postgresql database.. > some columns where edited by hand (columns A, B, C), and some others > (columns D, E, F) have been calculated as a result of mathematical > equation (where the factors are the A, B, C columns).... You should create a table with (a,b,c) and a view. Below a example. test=# create table foo (a int, b int, c int); CREATE TABLE test=# create view foo_view as (select a,b,c,a*b as ab, a*c as ac, b*c as bc from foo); CREATE VIEW > now I simply need to change some values in the A, B, C columns and I > would like to obtain the correct values in the D, E, F column... > I know that this is a tipical problem of a spreadsheet but how can I > solve it with a DBMS?? test=# insert into foo values (2,3,4); INSERT 0 1 test=# select * from foo_view ;a | b | c | ab | ac | bc ---+---+---+----+----+----2 | 3 | 4 | 6 | 8 | 12 (1 row) test=# update foo set a=3; UPDATE 1 test=# select * from foo_view ;a | b | c | ab | ac | bc ---+---+---+----+----+----3 | 3 | 4 | 9 | 12 | 12 (1 row) HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Fri, Mar 03, 2006 at 10:43:09AM +0100, ivan marchesini wrote: > I have fastly created a table in a postgresql database.. > some columns where edited by hand (columns A, B, C), and some others > (columns D, E, F) have been calculated as a result of mathematical > equation (where the factors are the A, B, C columns).... > now I simply need to change some values in the A, B, C columns and I > would like to obtain the correct values in the D, E, F column... > I know that this is a tipical problem of a spreadsheet but how can I > solve it with a DBMS?? Use triggers or a view. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Thanks to all... another question... is it possible to copy a table to a view and then back the view to a table??? thank you very much... ivan Il giorno ven, 03/03/2006 alle 11.51 +0100, Andreas Kretschmer ha scritto: > ivan marchesini <marchesini@unipg.it> schrieb: > > > Dear users.. > > I have fastly created a table in a postgresql database.. > > some columns where edited by hand (columns A, B, C), and some others > > (columns D, E, F) have been calculated as a result of mathematical > > equation (where the factors are the A, B, C columns).... > > You should create a table with (a,b,c) and a view. Below a example. > > test=# create table foo (a int, b int, c int); > CREATE TABLE > > test=# create view foo_view as (select a,b,c,a*b as ab, a*c as ac, b*c as bc from foo); > CREATE VIEW > > > > now I simply need to change some values in the A, B, C columns and I > > would like to obtain the correct values in the D, E, F column... > > I know that this is a tipical problem of a spreadsheet but how can I > > solve it with a DBMS?? > > test=# insert into foo values (2,3,4); > INSERT 0 1 > test=# select * from foo_view ; > a | b | c | ab | ac | bc > ---+---+---+----+----+---- > 2 | 3 | 4 | 6 | 8 | 12 > (1 row) > > test=# update foo set a=3; > UPDATE 1 > test=# select * from foo_view ; > a | b | c | ab | ac | bc > ---+---+---+----+----+---- > 3 | 3 | 4 | 9 | 12 | 12 > (1 row) > > > HTH, Andreas -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: marchesini@unipg.it ivan.marchesini@gmail.com tel: +39(0)755853760 fax: +39(0)755853756
am 03.03.2006, um 12:30:20 +0100 mailte ivan marchesini folgendes: > Thanks to all... > another question... > is it possible to copy a table to a view and then back the view to a > table??? Yes this is possible. > > Il giorno ven, 03/03/2006 alle 11.51 +0100, Andreas Kretschmer ha please, no silly fullquote below the answer. HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
On Fri, Mar 03, 2006 at 12:30:20PM +0100, ivan marchesini wrote: > another question... > is it possible to copy a table to a view and then back the view to a > table??? You need to read a basic textbook about what a view is. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346