[SQL] How to add column from old_table to new_table?
Hi, I have an old_table with two columns: "id" and "old_attribute". I have new table with the columns "id" and "new_attribute". old_table and new_table contain exactly the same id's. Now I want to copy all the old_attribute from old_table to the corresponding new_attribute in the new_table Is that possible in a SQL script (pgadmin or psql console?) and if so, what is the syntax in this example? I have tried something as: insert into new_table(new_attribute) value( select old_attribute from old_table, new_table where old_table.id = new_table_id) where new_table.id = old_table.id TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to add column from old_table to new_table?
Hi, I made a mistake in describing the query I tried. The query I tried was: update new_table set new_attribute = (select old_attribute from old_table, new_table where old_table.id = new_table.id) where old_table.id = new_table.id It actually returns the error "ERROR: more than one row returned by a subquery used as an expression" if ran. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to add column from old_table to new_table?
am 21.09.2005, um 14:36:26 +0200 mailte Joost Kraaijeveld folgendes: > Hi, > > I have an old_table with two columns: "id" and "old_attribute". I have > new table with the columns "id" and "new_attribute". There are indexes on id? > Is that possible in a SQL script (pgadmin or psql console?) and if so, > what is the syntax in this example? Yes, of course. > > I have tried something as: > > insert into new_table(new_attribute) > value( select old_attribute from old_table, new_table where old_table.id > = new_table_id) > where new_table.id = old_table.id Error, because i don't need a insert on new_table. You need a update! (If there a primary index on id this will raise a error) update t_new set name = (select name from t_old) where t_old.id=t_new.id; (t_old and t_new are the tables with (id, name) similar your example) Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to add column from old_table to new_table?
Hi,
On Wednesday 21 September 2005 14:36, Joost Kraaijeveld wrote:
| Is that possible in a SQL script (pgadmin or psql console?) and if so,
| what is the syntax in this example?
funny that you mention pgadmin :-)
| insert into new_table(new_attribute)
| value( select old_attribute from old_table, new_table where old_table.id
| = new_table_id)
| where new_table.id = old_table.id
from the pgadmin help system (reference|sql commands|insert):
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }
and some lines further an example:
This example inserts some rows into table films from a table tmp_films with
the same column layout as films:
INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
Or - if I was misunderstanding your question and you simply want to update
already existing rows in "new_table", you might try
UPDATE new_table
SET new_attribute = old_table.old_attribute
FROM old_table
WHERE new_table.id = old_table.id;
Ciao,
Thomas
--
Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] How to add column from old_table to new_table?
On 9/21/05, Joost Kraaijeveld <[EMAIL PROTECTED]> wrote: Hi,I have an old_table with two columns: "id" and "old_attribute". I havenew table with the columns "id" and "new_attribute".old_table and new_table contain exactly the same id's. Now I want to copy all the old_attribute from old_table to the correspondingnew_attribute in the new_table Why?. You can simply rename the old_table to new_table including column names (if any). Is that possible in a SQL script (pgadmin or psql console?) and if so,what is the syntax in this example? I have tried something as:insert into new_table(new_attribute)value( select old_attribute from old_table, new_table where old_table.id= new_table_id)where new_table.id = old_table.id You need an update statement and not an insert statement. TIA--Groeten,Joost KraaijeveldAskesis B.V.Molukkenstraat 14 6524NB Nijmegentel: 024-3888063 / 06-51855277fax: 024-3608416e-mail: [EMAIL PROTECTED]web: www.askesis.nl ---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster-- with regards,S.GnanavelSatyam Computer Services Ltd.
Re: [SQL] How to add column from old_table to new_table?
Thanks all. Too much coffee, too little sleep. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to add column from old_table to new_table?
On Wednesday 21 September 2005 14:36, Joost Kraaijeveld wrote: > Hi, > > I have an old_table with two columns: "id" and "old_attribute". I > have new table with the columns "id" and "new_attribute". > > old_table and new_table contain exactly the same id's. Now I want to > copy all the old_attribute from old_table to the corresponding > new_attribute in the new_table test=> create table old_table ( test(> old_table_id integer, test(> old_attribute char(1) test(> ); CREATE TABLE test=> create table new_table ( test(> new_table_id integer, test(> new_attribute char(1) test(> ); CREATE TABLE test=> insert into old_table values (1,'A'); INSERT 807376 1 test=> insert into old_table values (2,'B'); INSERT 807377 1 test=> insert into old_table values (3,'C'); INSERT 807378 1 test=> insert into new_table (new_table_id) values (1); INSERT 807379 1 test=> insert into new_table (new_table_id) values (2); INSERT 807380 1 test=> insert into new_table (new_table_id) values (3); INSERT 807381 1 test=> update new_table set new_attribute = test-> (select old_attribute from old_table test(> where old_table_id = new_table_id); UPDATE 3 test=> select * from new_table; new_table_id | new_attribute --+--- 1 | A 2 | B 3 | C (3 rader) -- Leif Biberg Kristensen http://solumslekt.org/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to add column from old_table to new_table?
On 9/21/05, Joost Kraaijeveld <[EMAIL PROTECTED]> wrote: Hi,I made a mistake in describing the query I tried. The query I tried was:update new_tableset new_attribute = (select old_attribute from old_table, new_tablewhere old_table.id = new_table.id)where old_table.id = new_table.id It should be written has update new_table set new_attribute=old_attribute from old_table where old_table.id=new_table.id; It actually returns the error "ERROR: more than one row returned by asubquery used as an _expression_" if ran. --Groeten,Joost KraaijeveldAskesis B.V.Molukkenstraat 146524NB Nijmegentel: 024-3888063 / 06-51855277fax: 024-3608416e-mail: [EMAIL PROTECTED] web: www.askesis.nl---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster -- with regards,S.GnanavelSatyam Computer Services Ltd.
Re: [SQL] How to add column from old_table to new_table?
am 21.09.2005, um 14:56:25 +0200 mailte A. Kretschmer folgendes: > update t_new set name = ... Sorry, wrong. The corect answer is in the tread. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] R-tree and start/end queries
I have a table like: Create table gf ( pkserial, start int, end int, gfvarchar ); I want to do queries along the lines of: "find all gf that overlap with (1,2)" or "find all gf that overlap with each other" And others. I have read over the documentation, but I still remain unclear about how to implement R-tree indexing in this situation. Any suggestions? Thanks, Sean ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] R-tree and start/end queries
On Wed, Sep 21, 2005 at 13:52:40 -0400, Sean Davis <[EMAIL PROTECTED]> wrote: > I have a table like: > > Create table gf ( > pkserial, > start int, > end int, > gfvarchar > ); > > I want to do queries along the lines of: > > "find all gf that overlap with (1,2)" or > "find all gf that overlap with each other" > > And others. I have read over the documentation, but I still remain unclear > about how to implement R-tree indexing in this situation. Any suggestions? There is a built in type for line segments that uses floating point. That will probably be usable by you directly unless the integers can can large enough that precision is a problem. There is an overlaps operator for the geometric types that could be used to answer your sample questions. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] R-tree and start/end queries
On Wed, 21 Sep 2005, Sean Davis wrote: > I have a table like: > > Create table gf ( > pkserial, > start int, > end int, > gfvarchar > ); > > I want to do queries along the lines of: > > "find all gf that overlap with (1,2)" or > "find all gf that overlap with each other" > > And others. I have read over the documentation, but I still remain unclear > about how to implement R-tree indexing in this situation. Any suggestions? Hi Sean I'm guessing that this is for some kind of genome database, yep? You may want to look at the chado database which has a growing library of functions for this sort of thing; www.gmod.org/schema Here is the code for doing range interval functions; our featureloc is equivalent to your "gf" (though we separate the entity from the entity being located). Our fmin and fmax may be equivalent to your start and end above (unless you indicate directionality with start>end in which case the intersection functions get a bit trickier). Our feature_id is probably equivalent to your gf column. We use the builtin pg types "point" and "box" and make an RTREE index over this: -- -- functions operating on featureloc ranges -- -- create a point CREATE OR REPLACE FUNCTION create_point (int, int) RETURNS point AS 'SELECT point ($1, $2)' LANGUAGE 'sql'; -- create a range box -- (make this immutable so we can index it) CREATE OR REPLACE FUNCTION boxrange (int, int) RETURNS box AS 'SELECT box (create_point(0, $1), create_point($2,5))' LANGUAGE 'sql' IMMUTABLE; -- create a query box CREATE OR REPLACE FUNCTION boxquery (int, int) RETURNS box AS 'SELECT box (create_point($1, $2), create_point($1, $2))' LANGUAGE 'sql' IMMUTABLE; --functional index that depends on the above functions CREATE INDEX binloc_boxrange ON featureloc USING RTREE (boxrange(fmin, fmax)); CREATE OR REPLACE FUNCTION featureloc_slice(int, int) RETURNS setof featureloc AS 'SELECT * from featureloc where boxquery($1, $2) @ boxrange(fmin,fmax)' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION featureloc_slice(varchar, int, int) RETURNS setof featureloc AS 'SELECT featureloc.* FROM featureloc INNER JOIN feature AS srcf ON (srcf.feature_id = featureloc.srcfeature_id) WHERE boxquery($2, $3) @ boxrange(fmin,fmax) AND srcf.name = $1 ' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION featureloc_slice(int, int, int) RETURNS setof featureloc AS 'SELECT * FROM featureloc WHERE boxquery($2, $3) @ boxrange(fmin,fmax) AND srcfeature_id = $1 ' LANGUAGE 'sql'; -- can we not just do these as views? CREATE OR REPLACE FUNCTION feature_overlaps(int) RETURNS setof feature AS 'SELECT feature.* FROM feature INNER JOIN featureloc AS x ON (x.feature_id=feature.feature_id) INNER JOIN featureloc AS y ON (y.feature_id=$1) WHERE x.srcfeature_id = y.srcfeature_idAND ( x.fmax >= y.fmin AND x.fmin <= y.fmax ) ' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION feature_disjoint_from(int) RETURNS setof feature AS 'SELECT feature.* FROM feature INNER JOIN featureloc AS x ON (x.feature_id=feature.feature_id) INNER JOIN featureloc AS y ON (y.feature_id=$1) WHERE x.srcfeature_id = y.srcfeature_idAND ( x.fmax < y.fmin OR x.fmin > y.fmax ) ' LANGUAGE 'sql'; Cheers Chris > Thanks, > Sean > > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] R-tree and start/end queries
How does the performance of the R-tree searches compare to the Bio::GFF binning approach? I've been wondering for a while how well the postgres builtins would do for this application. Dmitri > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Chris Mungall > Sent: Wednesday, September 21, 2005 3:14 PM > To: Sean Davis > Cc: [email protected]; Hilmar Lapp > Subject: Re: [SQL] R-tree and start/end queries > > > > On Wed, 21 Sep 2005, Sean Davis wrote: > > > I have a table like: > > > > Create table gf ( > > pkserial, > > start int, > > end int, > > gfvarchar > > ); > > > > I want to do queries along the lines of: > > > > "find all gf that overlap with (1,2)" or > > "find all gf that overlap with each other" > > > > And others. I have read over the documentation, but I still remain > > unclear about how to implement R-tree indexing in this > situation. Any > > suggestions? > > Hi Sean > > I'm guessing that this is for some kind of genome database, yep? > > You may want to look at the chado database which has a > growing library of functions for this sort of thing; > www.gmod.org/schema > > Here is the code for doing range interval functions; our > featureloc is equivalent to your "gf" (though we separate the > entity from the entity being located). Our fmin and fmax may > be equivalent to your start and end above (unless you > indicate directionality with start>end in which case the > intersection functions get a bit trickier). Our feature_id is > probably equivalent to your gf column. > > We use the builtin pg types "point" and "box" and make an > RTREE index over > this: > > > -- > -- functions operating on featureloc ranges > -- > > -- create a point > CREATE OR REPLACE FUNCTION create_point (int, int) RETURNS > point AS 'SELECT point ($1, $2)' LANGUAGE 'sql'; > > -- create a range box > -- (make this immutable so we can index it) > CREATE OR REPLACE FUNCTION boxrange (int, int) RETURNS box AS > 'SELECT box (create_point(0, $1), > create_point($2,5))' LANGUAGE 'sql' IMMUTABLE; > > -- create a query box > CREATE OR REPLACE FUNCTION boxquery (int, int) RETURNS box AS > 'SELECT box (create_point($1, $2), create_point($1, $2))' > LANGUAGE 'sql' IMMUTABLE; > > --functional index that depends on the above functions > CREATE INDEX binloc_boxrange ON featureloc USING RTREE > (boxrange(fmin, fmax)); > > > CREATE OR REPLACE FUNCTION featureloc_slice(int, int) RETURNS > setof featureloc AS > 'SELECT * from featureloc where boxquery($1, $2) @ > boxrange(fmin,fmax)' LANGUAGE 'sql'; > > CREATE OR REPLACE FUNCTION featureloc_slice(varchar, int, int) > RETURNS setof featureloc AS > 'SELECT featureloc.* >FROM featureloc >INNER JOIN feature AS srcf ON (srcf.feature_id = > featureloc.srcfeature_id) >WHERE boxquery($2, $3) @ boxrange(fmin,fmax) >AND srcf.name = $1 ' > LANGUAGE 'sql'; > > CREATE OR REPLACE FUNCTION featureloc_slice(int, int, int) > RETURNS setof featureloc AS > 'SELECT * >FROM featureloc >WHERE boxquery($2, $3) @ boxrange(fmin,fmax) >AND srcfeature_id = $1 ' > LANGUAGE 'sql'; > > -- can we not just do these as views? > CREATE OR REPLACE FUNCTION feature_overlaps(int) > RETURNS setof feature AS > 'SELECT feature.* > FROM feature >INNER JOIN featureloc AS x ON (x.feature_id=feature.feature_id) >INNER JOIN featureloc AS y ON (y.feature_id=$1) > WHERE >x.srcfeature_id = y.srcfeature_idAND >( x.fmax >= y.fmin AND x.fmin <= y.fmax ) ' > LANGUAGE 'sql'; > > CREATE OR REPLACE FUNCTION feature_disjoint_from(int) > RETURNS setof feature AS > 'SELECT feature.* > FROM feature >INNER JOIN featureloc AS x ON (x.feature_id=feature.feature_id) >INNER JOIN featureloc AS y ON (y.feature_id=$1) > WHERE >x.srcfeature_id = y.srcfeature_idAND >( x.fmax < y.fmin OR x.fmin > y.fmax ) ' > LANGUAGE 'sql'; > > > > Cheers > Chris > > > Thanks, > > Sean > > > > > > ---(end of > > broadcast)--- > > TIP 1: if posting/reading through Usenet, please send an appropriate > >subscribe-nomail command to [EMAIL PROTECTED] > so that your > >message can get through to the mailing list cleanly > > > > > > ---(end of > broadcast)--- > TIP 6: explain analyze is your friend > The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from
Re: [SQL] R-tree and start/end queries
Bruno Wolff III <[EMAIL PROTECTED]> writes: > There is a built in type for line segments that uses floating point. That > will probably be usable by you directly unless the integers can can large > enough that precision is a problem. There is an overlaps operator for the > geometric types that could be used to answer your sample questions. However, there's no built-in rtree opclass for that datatype, so he'd still be stuck with respect to getting indexing support for overlaps queries. I think the contrib/seg datatype might help, though the precision issue is still a possible problem. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] delete item[5] from varchar[] array???
How is it possible to delete an item from a single
dimension varchar[] array? Lets say it has the values
{1,2,3,4,5,6}... how do i delete at position [4]?
Could this be done in a SQL statement or a function? I
appreciate any help. Thank you
MP
__
Yahoo! for Good
Donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] delete item[5] from varchar[] array???
On Wed, Sep 21, 2005 at 06:56:36PM -0700, Matthew Peter wrote:
> How is it possible to delete an item from a single
> dimension varchar[] array? Lets say it has the values
> {1,2,3,4,5,6}... how do i delete at position [4]?
I'm not sure if there's a better way, but you could concatenate the
slices adjacent to the position you want to delete:
test=> SELECT a FROM foo;
a
---
{1,2,3,4,5,6}
(1 row)
test=> UPDATE foo SET a = a[1:3] || a[5:6];
UPDATE 1
test=> SELECT a FROM foo;
a
-
{1,2,3,5,6}
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] delete item[5] from varchar[] array???
Matthew Peter <[EMAIL PROTECTED]> writes: > How is it possible to delete an item from a single > dimension varchar[] array? AFAIR there is no built-in function for this, but it seems like you could write a generic polymorphic function for it easily enough. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] delete item[5] from varchar[] array???
Ya. I didn't see any in the \df. If it's so simple could you show me one? PLEEEASE Thanks MP --- Tom Lane <[EMAIL PROTECTED]> wrote: > Matthew Peter <[EMAIL PROTECTED]> writes: > > How is it possible to delete an item from a single > > dimension varchar[] array? > > AFAIR there is no built-in function for this, but it > seems like you > could write a generic polymorphic function for it > easily enough. > > regards, tom lane > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
