Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT Question

SELECT Question

From
Alex
Date:
Hi,
I have a column with a sequence in a table and want to allocate the
first 200 for special purpose thus starting the sequence from 200. For
the first 200 I have entries that will be added and removed.

Is there an easy way to write a select statement that returns me the
frist free number or any within the range of 200?
For example if 1-30, and 32-50 are occupied then i would like to fill in
the new entry with id 31.
I currently do it with a function but I was just wondering if there is a
way without it..

Thanks
Alex

Re: SELECT Question

From
"Lada 'Ray' Lostak"
Date:
> Is there an easy way to write a select statement that returns me the
> frist free number or any within the range of 200?
> For example if 1-30, and 32-50 are occupied then i would like to fill in
> the new entry with id 31.
> I currently do it with a function but I was just wondering if there is a
> way without it..
If I understand well, what something about

    SELECT min(xxx) FROM table WHERE xxx<50

Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net


--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.

Re: SELECT Question

From
Alex
Date:
Thanks,
but that only gives me smallest number of the ones in use but not the
first free number.
Alex


Lada 'Ray' Lostak wrote:

>>Is there an easy way to write a select statement that returns me the
>>frist free number or any within the range of 200?
>>For example if 1-30, and 32-50 are occupied then i would like to fill in
>>the new entry with id 31.
>>I currently do it with a function but I was just wondering if there is a
>>way without it..
>>
>>
>If I understand well, what something about
>
>    SELECT min(xxx) FROM table WHERE xxx<50
>
>Best regards,
>Lada 'Ray' Lostak
>Unreal64 Develop group
>http://www.orcave.com
>http://www.unreal64.net
>
>
>--------------------------------------------------------------------------
>In the 1960s you needed the power of two C64s to get a rocket
>to the moon. Now you need a machine which is a vast number
>of times more powerful just to run the most popular GUI.
>
>
>
>
>
>

Re: SELECT Question

From
"Lada 'Ray' Lostak"
Date:
> > Is there an easy way to write a select statement that returns me the
> > frist free number or any within the range of 200?
> > For example if 1-30, and 32-50 are occupied then i would like to fill in
> > the new entry with id 31.
> > I currently do it with a function but I was just wondering if there is a
> > way without it..
> If I understand well, what something about
>
>     SELECT min(xxx) FROM table WHERE xxx<50
>
After I sent it, I saw I understand bad... Just woke up... Sorry :)

I personally think, you need small procedure do to that, because you want to
perform condition 'min(xxx)' on "unexisting" columns.

R.

Re: SELECT Question

From
Alex
Date:
yes i am doing it that way now, but though there may be another way ...
more out of curiosity

thanks anyway

Lada 'Ray' Lostak wrote:

>>>Is there an easy way to write a select statement that returns me the
>>>frist free number or any within the range of 200?
>>>For example if 1-30, and 32-50 are occupied then i would like to fill in
>>>the new entry with id 31.
>>>I currently do it with a function but I was just wondering if there is a
>>>way without it..
>>>
>>>
>>If I understand well, what something about
>>
>>    SELECT min(xxx) FROM table WHERE xxx<50
>>
>>
>>
>After I sent it, I saw I understand bad... Just woke up... Sorry :)
>
>I personally think, you need small procedure do to that, because you want to
>perform condition 'min(xxx)' on "unexisting" columns.
>
>R.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>
>

Re: SELECT Question

From
Kris Jurka
Date:

On Thu, 20 Nov 2003, Alex wrote:
> >>Is there an easy way to write a select statement that returns me the
> >>frist free number or any within the range of 200?
> >>For example if 1-30, and 32-50 are occupied then i would like to fill in
> >>the new entry with id 31.

If you had a table with an id column and 200 rows 1-200 you could do

SELECT MIN(idtab.id) FROM idtab LEFT JOIN realtab ON (idtab.id =
realtab.id AND realtab.id IS NULL)

A useful generic function would be one something like range(min,max) that
would return a set of rows so you wouldn't have to actually have a table.

Kris Jurka

Re: SELECT Question

From
Manfred Koizar
Date:
On Thu, 20 Nov 2003 16:52:37 +0900, Alex <alex@meerkatsoft.com> wrote:
>>>>Is there an easy way to write a select statement that returns me the
>>>>frist free number or any within the range of 200?
>>>>For example if 1-30, and 32-50 are occupied then i would like to fill in
>>>>the new entry with id 31.

Fortunately this is not the performance mailing list :-)

First free number:
SELECT max(t1.id) + 1
  FROM t AS t1 INNER JOIN t AS t2
       ON (t1.id < 200 AND t1.id < t2.id AND t2.id <= 200)
 GROUP BY t2.id
HAVING max(t1.id) + 1 < t2.id
 ORDER BY t2.id
 LIMIT 1;

Make sure that there is always a row with id=0 and a row with id=200.

Any free number:
SELECT id - 1
  FROM t
 WHERE 1 < id AND id <= 200
   AND NOT EXISTS (SELECT * FROM t AS t2 WHERE t2.id = t.id - 1)
 LIMIT 1;

Always having a row with id=200 helps avoid unwanted corner cases.

One more:
SELECT coalesce(max(id), 0) + 1
  FROM t
 WHERE id <= 200
   AND id = (SELECT count(*) FROM t AS t2 WHERE t2.id <= t.id);

This should work without any dummy rows.  And it will not work, if id
is not unique or there is any row with id < 1.

Servus
 Manfred

Re: SELECT Question

From
Joe Conway
Date:
Kris Jurka wrote:
> A useful generic function would be one something like range(min,max) that
> would return a set of rows so you wouldn't have to actually have a table.
>

You mean like this?

CREATE OR REPLACE FUNCTION test(int,int) RETURNS SETOF int AS '
BEGIN
   FOR i IN $1..$2 LOOP
     RETURN NEXT i;
   END LOOP;
   RETURN;
END;
' LANGUAGE 'plpgsql' STRICT IMMUTABLE;

regression=# select * from test(4, 8);
  test
------
     4
     5
     6
     7
     8
(5 rows)

HTH,

Joe

Re: SELECT Question

From
Tom Lane
Date:
Joe Conway <mail@joeconway.com> writes:
> Kris Jurka wrote:
>> A useful generic function would be one something like range(min,max) that
>> would return a set of rows so you wouldn't have to actually have a table.

> You mean like this?

> CREATE OR REPLACE FUNCTION test(int,int) RETURNS SETOF int AS '
> BEGIN
>    FOR i IN $1..$2 LOOP
>      RETURN NEXT i;
>    END LOOP;
>    RETURN;
> END;
> ' LANGUAGE 'plpgsql' STRICT IMMUTABLE;

I was thinking of proposing that we provide something just about like
that as a standard function (written in C, not in plpgsql, so that it
would be available whether or not you'd installed plpgsql).  There are
some places in the information_schema that desperately need it ---
right now, the value of FUNC_MAX_ARGS is effectively hard-wired into
some of the information_schema views, which means they are broken if
one changes that #define.  We could fix this if we had a function like
the above and exported FUNC_MAX_ARGS as a read-only GUC variable.

            regards, tom lane

Re: SELECT Question

From
Joe Conway
Date:
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>CREATE OR REPLACE FUNCTION test(int,int) RETURNS SETOF int AS '
>>BEGIN
>>   FOR i IN $1..$2 LOOP
>>     RETURN NEXT i;
>>   END LOOP;
>>   RETURN;
>>END;
>>' LANGUAGE 'plpgsql' STRICT IMMUTABLE;
>
> I was thinking of proposing that we provide something just about like
> that as a standard function (written in C, not in plpgsql, so that it
> would be available whether or not you'd installed plpgsql).  There are
> some places in the information_schema that desperately need it ---
> right now, the value of FUNC_MAX_ARGS is effectively hard-wired into
> some of the information_schema views, which means they are broken if
> one changes that #define.  We could fix this if we had a function like
> the above and exported FUNC_MAX_ARGS as a read-only GUC variable.

I've been really busy on other-than-postgres stuff lately, but I'm
planning to carve out time next week to start doing some 7.5
development. I'll take this one if you want.

Joe

Re: SELECT Question

From
Alex
Date:
All,
thanks for the many suggestions
Alex

Manfred Koizar wrote:

>On Thu, 20 Nov 2003 16:52:37 +0900, Alex <alex@meerkatsoft.com> wrote:
>
>
>>>>>Is there an easy way to write a select statement that returns me the
>>>>>frist free number or any within the range of 200?
>>>>>For example if 1-30, and 32-50 are occupied then i would like to fill in
>>>>>the new entry with id 31.
>>>>>
>>>>>
>
>Fortunately this is not the performance mailing list :-)
>
>First free number:
>SELECT max(t1.id) + 1
>  FROM t AS t1 INNER JOIN t AS t2
>       ON (t1.id < 200 AND t1.id < t2.id AND t2.id <= 200)
> GROUP BY t2.id
>HAVING max(t1.id) + 1 < t2.id
> ORDER BY t2.id
> LIMIT 1;
>
>Make sure that there is always a row with id=0 and a row with id=200.
>
>Any free number:
>SELECT id - 1
>  FROM t
> WHERE 1 < id AND id <= 200
>   AND NOT EXISTS (SELECT * FROM t AS t2 WHERE t2.id = t.id - 1)
> LIMIT 1;
>
>Always having a row with id=200 helps avoid unwanted corner cases.
>
>One more:
>SELECT coalesce(max(id), 0) + 1
>  FROM t
> WHERE id <= 200
>   AND id = (SELECT count(*) FROM t AS t2 WHERE t2.id <= t.id);
>
>This should work without any dummy rows.  And it will not work, if id
>is not unique or there is any row with id < 1.
>
>Servus
> Manfred
>
>
>
>

Re: SELECT Question

From
Holger Klawitter
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Am Thursday 20 November 2003 08:32 schrieben Sie:
> > Is there an easy way to write a select statement that returns me the
> > frist free number or any within the range of 200?

As long as the set of numbers is not too big, the following might work:

CREATE TABLE legal_numbers ( num int );
INSERT INTO into legal_numbers VALUES ( 1 );
... do this with values from 1 to 200;

You can create this table once and for all.
Now the select is rather simple:

SELECT
    min(num)
FROM
    legal_numbers
WHERE
    num not in ( SELECT id FROM other_table )
;

Mit freundlichem Gruß / With kind regards
    Holger Klawitter
- --
info@klawitter.de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/vIha1Xdt0HKSwgYRAtGbAJ4xR+Naws0vHugt40GH+BJYY/3bAwCdG7t6
2ijqnA8Fm5Z8h4Zhw5H7p3s=
=xsBO
-----END PGP SIGNATURE-----