pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Lew <noone@lewscanon.com>
To: pgsql-sql@postgresql.org
Subject: Re: is there a distinct function for comma lists ?
Date: Tue, 07 Sep 2010 22:14:31 -0400
Message-ID: <i66rhi$vpl$1@news.albasani.net> (raw)
In-Reply-To: <4C862773.2070900@gmx.net>
References: <4C862773.2070900@gmx.net>

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

view thread (4+ messages)  latest in thread

Message-ID: <i66rhi$vpl$1@news.albasani.net>
Permalink:  ../i66rhi$vpl$1@news.albasani.net/
Also on:    postgresql.org/message-id/i66rhi$vpl$1@news.albasani.net

copy link · copy postgr.es

reply

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: noone@lewscanon.com
  Subject: Re: is there a distinct function for comma lists ?
  In-Reply-To: <i66rhi$vpl$1@news.albasani.net>

* 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