From: msi77 <msi77@yandex.ru>
To: Andreas Gaab <a.gaab@scanlab.de>
To: pgsql-sql@postgresql.org
Subject: Re: is there a distinct function for comma lists ?
Date: Tue, 28 Sep 2010 18:00:45 +0400
Message-ID: <445681285682445@web147.yandex.ru> (raw)
In-Reply-To: <48DA836F3865C54B8FBF424A3B775AF6011D0A5EC1@Exchange-Server>
References: <4C862773.2070900@gmx.net>
<48DA836F3865C54B8FBF424A3B775AF6011D0A5EC1@Exchange-Server>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: msi77@yandex.ru, a.gaab@scanlab.de
Subject: Re: is there a distinct function for comma lists ?
In-Reply-To: <445681285682445@web147.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox