Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6967B9FC08D for ; Thu, 28 Jun 2007 03:36:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 19584-03 for ; Thu, 28 Jun 2007 03:36:03 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.181]) by postgresql.org (Postfix) with ESMTP id CDCFC9FC079 for ; Thu, 28 Jun 2007 03:36:04 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id k40so657264wah for ; Wed, 27 Jun 2007 23:36:00 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=fHXWAEnbtOsa4nXLQNr6X7KfQoic/Qkho1ZiN9fGSf8TiTKZ66Fp9RGXkC+zhPU66/9FjZY8UTY8D7PBz6ZR07JHtc0ydypUSR1cUDOkPZwzKnL2D6p7cZzxIcuOd1YhpETPOCW+wDp95mm8GrSmw1ipzWDtFLmwkvKgChse8z8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=QRezg+1sjo0mhjHQSW30nQ/N6mwNb6voDKKu0xwtfBF2N9zikKH5SgO6wt3ohDba3CZJqeb+BsZtL8bPjyH1PikGAY8lxkOlIpHZxrilkfSv3qd64T7Xa9UNAkN+Jbttn6v6V1Ru9nG5NRpAf84QrHP1pHjMItbiXHBV9nLRL8M= Received: by 10.115.91.2 with SMTP id t2mr1207464wal.1183012559848; Wed, 27 Jun 2007 23:35:59 -0700 (PDT) Received: from ?10.1.1.15? ( [203.217.18.65]) by mx.google.com with ESMTP id m28sm15817923poh.2007.06.27.23.35.58 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 27 Jun 2007 23:35:59 -0700 (PDT) Message-ID: <46835737.8090307@gmail.com> Date: Thu, 28 Jun 2007 16:37:43 +1000 From: Chris User-Agent: Thunderbird 2.0.0.4 (Windows/20070604) MIME-Version: 1.0 To: "A. Kretschmer" CC: pgsql-performance@postgresql.org Subject: Re: update query taking too long References: <46834124.5010600@gmail.com> <21072.1183009270@sss.pgh.pa.us> <46835252.5070901@gmail.com> <20070628062826.GC22066@a-kretschmer.de> In-Reply-To: <20070628062826.GC22066@a-kretschmer.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/615 X-Sequence-Number: 25648 A. Kretschmer wrote: > am Thu, dem 28.06.2007, um 16:16:50 +1000 mailte Chris folgendes: >> Is there a better way to write the update? I thought about something >> like this (but couldn't get it working - guess I don't have the right >> syntax): >> >> update t1 set domainname=(select id, SUBSTRING(emailaddress FROM >> POSITION('@' IN emailaddress)) from table t2) AS t2 where t1.id=t2.id > > test=# select * from foo; > id | mail | domain > ----+-------------+-------- > 1 | foo@foo.tld | > 2 | bar@bar.tld | > (2 rows) > > test=*# update foo set domain=SUBSTRING(mail FROM (POSITION('@' IN > mail)+1)); That's what my original query is (apart from the +1 at the end) ;) I was just trying to approach it differently with the other attempt. -- Postgresql & php tutorials http://www.designmagick.com/