SELECT Question

[Alex]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Lada 'Ray' Lostak]

> 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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Alex]

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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Manfred Koizar]

On Thu, 20 Nov 2003 16:52:37 +0900, Alex <al**@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Lada 'Ray' Lostak]

> > 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

[Alex]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Kris Jurka]

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Joe Conway]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Manfred Koizar]

On Thu, 20 Nov 2003 16:52:37 +0900, Alex <al**@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Joe Conway]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Joe Conway <ma**@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Joe Conway]

Tom Lane wrote:

Joe Conway <ma**@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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Alex]

All,
thanks for the many suggestions
Alex

Manfred Koizar wrote:

On Thu, 20 Nov 2003 16:52:37 +0900, Alex <al**@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend