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 12 2284
> 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
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)
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
> > 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
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
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
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
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mohammed Mazid |
last post by:
Hi folks!
Can anyone please help me with this?
I am developing a Quiz program but I am stuck with "multiple answers".
Basically I need some sort of code that would select multiple answers...
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I...
|
by: marco |
last post by:
Dear List,
as it seems, MS SQL as used in Access does not allow a select INTO
within a UNION query. Also, it seems that a UNION query can not be used
as a subquery.
Maybe my (simplified)...
|
by: @sh |
last post by:
Need help again! Here's the head script...
----------------------------------------------------------------------------------------
function Ash_ChangePicFromSelect(TheSource,TheDestination) {...
|
by: Christopher DeMarco |
last post by:
Hi all...
I've written a class to provide an interface to popen; I've included
the actual select() loop below. I'm finding that "sometimes" popen'd
processes take "a really long time" to...
|
by: GTi |
last post by:
Whats wring with this code?
<select class=EditField size="1" name="PlantUnitID" title="Select
line">
<option value="0" >Standalone Unit</option>
<option value="1" selected >Connected Unit...
|
by: Eitan |
last post by:
Hello,
I want a solutions for a compicateds sql select statments.
The selects can use anything : views, stored procedures, analytic functions,
etc...
(not use materialized view, unless it is...
|
by: beacon |
last post by:
Hello to everybody,
I have a section on a form that has 10 questions, numbered 1-10, with 3 option buttons per question. Each of the option buttons have the same response (Yes, No, Don't know),...
|
by: trose178 |
last post by:
Good day all, I am working on a multi-select list box for a standard
question checklist database and I am running into a syntax error in
the code that I cannot seem to correct. I will also note...
|
by: bonneylake |
last post by:
Hey Everyone,
Well i am not sure if my question needs to be here or in coldfusion. If i have my question is in the wrong section i am sorry in advance an will move it to the correct section.
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |