tag:blogger.com,1999:blog-265587150912543268.post8386830354398476646..comments2022-10-22T09:41:03.443+02:00Comments on johto's lair: Smallest available IDMarko Tiikkajahttp://www.blogger.com/profile/10981554077352841785noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-265587150912543268.post-25744143950117137122012-11-08T11:06:51.223+01:002012-11-08T11:06:51.223+01:00It is my great pleasure to visit your website and ...It is my great pleasure to visit your website and to enjoy your great post here. I like it very much. <br />Oneida Keelhttp://mattressdirectwarehouse.comnoreply@blogger.comtag:blogger.com,1999:blog-265587150912543268.post-69961761125339284992011-09-26T13:15:19.240+02:002011-09-26T13:15:19.240+02:00Thanks for writing ! I agree with Randolf. Indeed,...Thanks for writing ! I agree with Randolf. Indeed, Stored procedure is working great. I just tested it and it is working phenomenal. Man thank to you :)comment systemhttp://www.pnyxe.com/DiscussIt-comment-systemnoreply@blogger.comtag:blogger.com,1999:blog-265587150912543268.post-65077536955881682192010-12-05T08:10:12.384+01:002010-12-05T08:10:12.384+01:00This stored procedure is working wonderfully well ...This stored procedure is working wonderfully well for me. I'm just starting to use it on PostgreSQL 9, and so far it seems to be working just fine (not surprising).Randolf Richardsonhttp://www.lumbercartel.ca/noreply@blogger.comtag:blogger.com,1999:blog-265587150912543268.post-38949104306983938872010-07-18T21:48:12.594+02:002010-07-18T21:48:12.594+02:00@Randolf:
Thank you for your kind words.
When I ...@Randolf:<br /><br />Thank you for your kind words.<br /><br />When I was still learning all the basic stuff about postgres, I, like you, found the people on #postgresql very helpful and am truly grateful that we have such an awesome community around this great product.<br /><br />I can only speak for myself, but like you guessed, I enjoy challenges. And I think helping people out is the least IMarko Tiikkajahttps://www.blogger.com/profile/10981554077352841785noreply@blogger.comtag:blogger.com,1999:blog-265587150912543268.post-56057213572998212732010-07-18T18:17:58.919+02:002010-07-18T18:17:58.919+02:00@Anonymous:
That would be better written as:
sel...@Anonymous:<br /><br />That would be better written as:<br /><br />select s.id<br />from generate_series(0, (select coalesce(max(id) + 1, 0) from foo)) as s(id)<br />left join foo t on (t.id = s.id)<br />where t.id is null<br />order by s.id<br />limit 1;<br /><br />NOT IN is very unpredictable performance-wise so its use should be avoided. You also didn't account for the empty table case.<Marko Tiikkajahttps://www.blogger.com/profile/10981554077352841785noreply@blogger.comtag:blogger.com,1999:blog-265587150912543268.post-26047164544562660352010-07-17T20:45:08.563+02:002010-07-17T20:45:08.563+02:00Putting up with? Are you kidding?? You put that ...Putting up with? Are you kidding?? You put that together so quickly -- I was truly impressed. Paid support often isn't this fast nor good!<br /><br />I've written a few stored procedures over the years that took me weeks to write (mainly due to learning the plpgsql language at the time; I'm still learning), and you left me with an impression that this is a very strong area of Randolf Richardsonhttp://www.inter-corporate.com/noreply@blogger.comtag:blogger.com,1999:blog-265587150912543268.post-89831545826967835492010-07-17T16:49:02.918+02:002010-07-17T16:49:02.918+02:00Why not this?
select s.id from generate_series(1,...Why not this?<br /><br />select s.id from generate_series(1,(select max(id) + 1 from <table>)) as s(id)<br />where s.id not in (select id from <table>)<br />order by s.id limit 1<br /><br />This appears to be pretty quick with an index on id. You can specify the lower bound of the id range in the generate_series.<br /><br />Thoughts? Comments?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-265587150912543268.post-6061439187346432192010-07-17T12:24:48.843+02:002010-07-17T12:24:48.843+02:00@Randolf:
Thanks for putting up with my slowness ...@Randolf:<br /><br />Thanks for putting up with my slowness and testing my failed attempts. ;-)Marko Tiikkajahttps://www.blogger.com/profile/10981554077352841785noreply@blogger.comtag:blogger.com,1999:blog-265587150912543268.post-42484421503609783522010-07-17T12:20:21.947+02:002010-07-17T12:20:21.947+02:00@Thom:
Thanks, fixed.@Thom:<br /><br />Thanks, fixed.Marko Tiikkajahttps://www.blogger.com/profile/10981554077352841785noreply@blogger.comtag:blogger.com,1999:blog-265587150912543268.post-33089624994581558432010-07-17T12:10:05.702+02:002010-07-17T12:10:05.702+02:00Thanks for writing this code Marko -- I'm the ...Thanks for writing this code Marko -- I'm the one who brought this up in IRC.<br /><br />Your SQL code is robust, and working very well in my project.Randolf Richardsonhttp://www.inter-corporate.com/noreply@blogger.comtag:blogger.com,1999:blog-265587150912543268.post-26544525207040452632010-07-17T12:04:33.334+02:002010-07-17T12:04:33.334+02:00Typo in last select. No comma after "max(id)...Typo in last select. No comma after "max(id) + 1". But nice post :)Thomhttps://www.blogger.com/profile/02563102260614838138noreply@blogger.com