Thanks for all of your input. It appears that the best way to do this is to create a default random number in the primary id field in the table definition and then return that value after insert. If an exception occurs because of duplicates I will simple perform the same insert statement again. I doubt there would be many duplicate hits if I use a really large number.
Why use a random number as a primary key? Security via obscurity. I build web applications for a living. In most of my applications it is preferable to use a random primary key. Why? Example: I built a web application called the Form Builder. It allows individuals to create web forms. After a user is done building their web form the tool provides a URL for the user to access the form. Obviously the URL has the random ID of the form in it. Most of the forms created with this tool can be accessed and filled out by the general public. So why not use a sequential number? So if I used a sequential number then a student or outside individual could easily change the number in the URL to see what other forms there are. It is not that they don't have access to the forms but they should not be messing with them if they really don't have a reason to. So by using a very large random number the users filling out a form cannot easily guess what another form ID is. Does that make sense? I have at least a dozen tools that I prefer to use this approach on. This is not a security approach. It is more about not giving obvious access to people that want to mess around. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vivek Khera Sent: Tuesday, March 18, 2008 12:57 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Create on insert a unique random number On Mar 18, 2008, at 1:03 PM, Campbell, Lance wrote: > The field n is not random but is sequential. Is there something I > should do to make the serial number random? Depending on your "randomness" need, you can alter the increment of the sequence so it changes by a different amount than "1" on every increment, though it will still be constant increment. You need to specify what the purpose of it being random is, then you might get more useful responses. Does it need to be random for some security purpose? If so, describe the level of security, specifically, against what threat are you defending? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
