pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRandom Unique Integer
4+ messages / 3 participants
[nested] [flat]
* Random Unique Integer
@ 2007-06-14 13:08 Campbell, Lance <lance@uiuc.edu>
2007-06-14 13:25 ` Re: Random Unique Integer Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Campbell, Lance @ 2007-06-14 13:08 UTC (permalink / raw)
To: pgsql-sql
I have a table T1. It contains a field called F1. Is there a way for
me to set the table T1 up such that F1 can be populated with a random
integer such that F1 is a unique integer? I would rather not create a
stored procedure.
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Random Unique Integer
2007-06-14 13:08 Random Unique Integer Campbell, Lance <lance@uiuc.edu>
@ 2007-06-14 13:25 ` Michael Fuhr <mike@fuhr.org>
2007-06-14 19:40 ` Re: Random Unique Integer Campbell, Lance <lance@uiuc.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2007-06-14 13:25 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: pgsql-sql
On Thu, Jun 14, 2007 at 08:08:26AM -0500, Campbell, Lance wrote:
> I have a table T1. It contains a field called F1. Is there a way for
> me to set the table T1 up such that F1 can be populated with a random
> integer such that F1 is a unique integer?
What problem are you trying to solve? The solution might depend
on what you're trying to do.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Random Unique Integer
2007-06-14 13:08 Random Unique Integer Campbell, Lance <lance@uiuc.edu>
2007-06-14 13:25 ` Re: Random Unique Integer Michael Fuhr <mike@fuhr.org>
@ 2007-06-14 19:40 ` Campbell, Lance <lance@uiuc.edu>
2007-06-14 20:58 ` Re: Random Unique Integer Kristo Kaiv <kristo.kaiv@skype.net>
0 siblings, 1 reply; 4+ messages in thread
From: Campbell, Lance @ 2007-06-14 19:40 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
I have a web application that is used to create web surveys and web
forms. Users can create any number of surveys or forms at any time.
The primary key on one of my tables defines the ID for any given form or
survey. I do NOT want the number sequential. Since I use the number in
the web URL as a parameter, I don't want people guessing what survey or
form is next. By creating a very large random number the users cannot
easily guess another form or survey's ID. This number is not designed
for security but to keep people from being nosey and poking around
easily into someone else's form or survey.
Can a table have a primary key that is randomly generated?
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: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Thursday, June 14, 2007 8:26 AM
To: Campbell, Lance
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Random Unique Integer
On Thu, Jun 14, 2007 at 08:08:26AM -0500, Campbell, Lance wrote:
> I have a table T1. It contains a field called F1. Is there a way for
> me to set the table T1 up such that F1 can be populated with a random
> integer such that F1 is a unique integer?
What problem are you trying to solve? The solution might depend
on what you're trying to do.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Random Unique Integer
2007-06-14 13:08 Random Unique Integer Campbell, Lance <lance@uiuc.edu>
2007-06-14 13:25 ` Re: Random Unique Integer Michael Fuhr <mike@fuhr.org>
2007-06-14 19:40 ` Re: Random Unique Integer Campbell, Lance <lance@uiuc.edu>
@ 2007-06-14 20:58 ` Kristo Kaiv <kristo.kaiv@skype.net>
0 siblings, 0 replies; 4+ messages in thread
From: Kristo Kaiv @ 2007-06-14 20:58 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
On 14.06.2007, at 22:40, Campbell, Lance wrote:
> I have a web application that is used to create web surveys and web
> forms. Users can create any number of surveys or forms at any time.
> The primary key on one of my tables defines the ID for any given
> form or
> survey. I do NOT want the number sequential. Since I use the
> number in
> the web URL as a parameter, I don't want people guessing what
> survey or
> form is next. By creating a very large random number the users cannot
> easily guess another form or survey's ID. This number is not designed
> for security but to keep people from being nosey and poking around
> easily into someone else's form or survey.
>
> Can a table have a primary key that is randomly generated?
you could do it like this:
test=# create table testsurvey (id_survey serial primary key, survey
text);
NOTICE: CREATE TABLE will create implicit sequence
"testsurvey_id_survey_seq" for serial column "testsurvey.id_survey"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"testsurvey_pkey" for table "testsurvey"
CREATE TABLE
test=# insert into testsurvey (survey) values ('first');
INSERT 0 1
test=# insert into testsurvey (survey) values ('second');
INSERT 0 1
test=# insert into testsurvey (survey) values ('third');
INSERT 0 1
test=# select *, md5(id_survey) from testsurvey;
id_survey | survey | md5
-----------+--------+----------------------------------
1 | first | c4ca4238a0b923820dcc509a6f75849b
2 | second | c81e728d9d4c2f636f067f89cc14862c
3 | third | eccbc87e4b5ce2fe28308fd9f2a7baf3
(3 rows)
so you actually show out only the PK id-s md5 hash
test=# create index idx_survey_md5 on testsurvey (md5(id_survey));
CREATE INDEX
test=# select * from testsurvey where md5(id_survey) =
'c81e728d9d4c2f636f067f89cc14862c';
id_survey | survey
-----------+--------
2 | second
The hashing algorithm you chose depends on the amount of rows you
expect there.
And also how difficult cracking it needs to be.
In case you have many rows and you think collision checking is
necessary you could
store the calculated hash in a new column. for replication management
purposes
its better to keep the PK as serial / bigserial.
Example: 100K surveys,
hashtext(id) : 4B int
100 000 / 256 ** 4 = 42949 (1:42949 chance that the next number will
lead to a valid survey entry)
keep the hashed pk value in a separate row as you will deplete
1/42949-th of the hash space with 100K rows
peace,
Kristo
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-06-14 20:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-14 13:08 Random Unique Integer Campbell, Lance <lance@uiuc.edu>
2007-06-14 13:25 ` Michael Fuhr <mike@fuhr.org>
2007-06-14 19:40 ` Campbell, Lance <lance@uiuc.edu>
2007-06-14 20:58 ` Kristo Kaiv <kristo.kaiv@skype.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox