Skip site navigation (1) Skip section navigation (2)

Re: tricky query

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "Cosimo Streppone" <cosimo(at)streppone(dot)it>
Cc: "Postgresql Performance list" <pgsql-performance(at)postgresql(dot)org>,"John A Meinel" <john(at)arbash-meinel(dot)com>
Subject: Re: tricky query
Date: 2005-06-28 19:45:53
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3415C2C08@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-performance
Cosimo wrote:
> I'm very interested in this "tricky query".
> Sorry John, but if I populate the `id_test' relation
> with only 4 tuples with id values (10, 11, 12, 13),
> the result of this query is:
> 
>    cosimo=> create table id_test (id integer primary key);
>    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> 'id_test_pkey'
> for table 'id_test'
>    CREATE TABLE
>    cosimo=> insert into id_test values (10); -- and 11, 12, 13, 14
>    INSERT 7457570 1
>    INSERT 7457571 1
>    INSERT 7457572 1
>    INSERT 7457573 1
>    INSERT 7457574 1
>    cosimo=> SELECT t1.id+1 as id_new FROM id_test t1 WHERE NOT EXISTS
> (SELECT
> t2.id FROM id_test t2 WHERE t2.id = t1.id+1) ORDER BY t1.id LIMIT 1;
>     id_new
>    --------
>         15
>    (1 row)
> 
> which if I understand correctly, is the wrong answer to the problem.
> At this point, I'm starting to think I need some sleep... :-)

Correct, in that John's query returns the first empty slot above an
existing  filled slot (correct behavior in my case).  You could flip
things around a bit to get around thist tho.

Merlin

Responses

pgsql-performance by date

Next:From: Sebastian HennebruederDate: 2005-06-28 20:38:54
Subject: Re: tricky query
Previous:From: John A MeinelDate: 2005-06-28 19:42:21
Subject: Re: tricky query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group