pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedis there a distinct function for comma lists ?
4+ messages / 4 participants
[nested] [flat]
* is there a distinct function for comma lists ?
@ 2010-09-07 11:52 Andreas <maps.on@gmx.net>
0 siblings, 2 replies; 4+ messages in thread
From: Andreas @ 2010-09-07 11:52 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is there a distinct function for comma lists ?
@ 2010-09-07 12:19 Andreas Gaab <A.Gaab@scanlab.de>
parent: Andreas <maps.on@gmx.net>
1 sibling, 1 reply; 4+ messages in thread
From: Andreas Gaab @ 2010-09-07 12:19 UTC (permalink / raw)
To: pgsql-sql
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?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is there a distinct function for comma lists ?
@ 2010-09-08 02:14 Lew <noone@lewscanon.com>
parent: Andreas <maps.on@gmx.net>
1 sibling, 0 replies; 4+ messages in thread
From: Lew @ 2010-09-08 02:14 UTC (permalink / raw)
To: pgsql-sql
On 09/07/2010 07:52 AM, Andreas wrote:
> 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 ... ) )
I am not clear on what you're asking here. From what you say, there's nothing
to do. The two forms of the SQL you show have the same result.
The fact that 11 or 13 or whatever appear in the IN list more than once
doesn't affect the result of the query; 13 is in the IN list no matter how
many times (> 0) that 13 appears in the IN list. So a row from mytable with
id=13 is selected regardless. It's not like the row will be selected more
than once.
From the manual:
'The result of IN is "true" if any equal subquery row is found.'
<http://www.postgresql.org/docs/8.4/interactive/functions-subquery.html;
It's still true of more than one equal subquery row is found. It's not true
multiple times, it's just true.
If mytable.id is not unique, then every row with that value will be selected,
but adding DISTINCT to the IN list won't change that either.
--
Lew
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is there a distinct function for comma lists ?
@ 2010-09-28 14:00 msi77 <msi77@yandex.ru>
parent: Andreas Gaab <A.Gaab@scanlab.de>
0 siblings, 0 replies; 4+ messages in thread
From: msi77 @ 2010-09-28 14:00 UTC (permalink / raw)
To: Andreas Gaab <a.gaab@scanlab.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-09-28 14:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-07 11:52 is there a distinct function for comma lists ? Andreas <maps.on@gmx.net>
2010-09-07 12:19 ` Andreas Gaab <A.Gaab@scanlab.de>
2010-09-28 14:00 ` msi77 <msi77@yandex.ru>
2010-09-08 02:14 ` Lew <noone@lewscanon.com>
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