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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sebastian Hennebrueder | 2005-06-28 20:38:54 | Re: tricky query |
Previous Message | John A Meinel | 2005-06-28 19:42:21 | Re: tricky query |