From: msi77 <msi77@yandex.ru>
To: Andreas Gaab <a.gaab@scanlab.de>,pgsql-sql@postgresql.org
In-Reply-To: <48DA836F3865C54B8FBF424A3B775AF6011D0A5EC1@Exchange-Server>
References: <4C862773.2070900@gmx.net>
	<48DA836F3865C54B8FBF424A3B775AF6011D0A5EC1@Exchange-Server>
Subject: Re: is there a distinct function for comma lists ?
MIME-Version: 1.0
Message-Id: <445681285682445@web147.yandex.ru>
Date: Tue, 28 Sep 2010 18:00:45 +0400
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=utf-8

Hi,

> And as bonus ... is there a way to find IDs that are in the list but not 
> in the table without creating a temporary table and use a join?

Does below satisfy you?

select * from (values (1), (2), (3), (5), (7), (11), (3), 
(6), (13), (13), (3), (11)) as X(a)
where a not in(select id from mytable)

Serge
http://www.sql-ex.com/


> Hi,
> For the problem 1 perhaps something like
> select distinct unnest(ARRAY[ 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ])
> Regards,
> Andreas
> -----Ursprüngliche Nachricht-----
> Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] Im Auftrag von Andreas
> Gesendet: Dienstag, 7. September 2010 13:52
> An: pgsql-sql@postgresql.org
> Betreff: [SQL] is there a distinct function for comma lists ?
> Hi,
> is there a distinct function for comma separated lists ?
> I sometimes need to update tables where I got a set of IDs, like:
> update mytable
> set someattribute = 42
> where mytable.id in
> ( 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ... )
> So there are double entries in the list but in this case its just 
> overhead but no problem.
> But for calculated values this would not allways be desirable.
> update mytable
> set someattribute = someattribute + 1
> where mytable.id in
> ( 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ... )
> How could I get a distinct list? Those lists can have 2000-3000 IDs 
> sometimes.
> One solution was as follows but perhaps there is something more elegant?
> update mytable
> set someattribute = someattribute + 1
> where mytable.id in
> ( select distinct id from mytable where id in ( 1, 2, 3, 5, 7, 11, 3, 
> 6, 13, 13, 3, 11 ... ) )
> And as bonus ... is there a way to find IDs that are in the list but not 
> in the table without creating a temporary table and use a join?
> 

Здесь спама нет http://mail.yandex.ru/nospam/sign