Re: [GENERAL] Generating random unique alphanumeric IDs

Thom Brown wrote: > If this results in an unpredictable and non-duplicating loop of generated > sets of characters, that would be ideal. Would a parallel for this be a > 5-character code possibly transcoded from a 6-character GUID/UUID? (a-h + > j+n + p-z + A-H + J-N + P+Z + 2-9 = 56 poss

Re: [GENERAL] Generating random unique alphanumeric IDs

On Thu, 20 Aug 2009 13:34:51 +0100 Thom Brown wrote: Correcting myself. a) it is a bad idea to pad an hex with an hex... so I should still find a quick way to change representation to [g-z] for the padding characters... or just pad with a constant string. select lpad( to_hex(feistel_encrypt(10))

Re: [GENERAL] Generating random unique alphanumeric IDs

> > > Since I'm then converting to_hex to shorten the string I was > thinking to add some more bits of randomness since eg. > > to_hex(10) = 'a' > > In the line of > select lpad( > to_hex(feistel_encrypt(10)),7 , to_hex((rand()*2^31)::int) > ); > > I was wondering if there is any better way to ge

Re: [GENERAL] Generating random unique alphanumeric IDs

On Mon, 17 Aug 2009 12:37:33 +0200 "Daniel Verite" wrote: > http://archives.postgresql.org/pgsql-general/2009-07/msg00194.php As an exercise I wrote the decrypt version create or replace function feistel_encrypt(value int) returns int as $$ declare l1 int; l2

Re: [GENERAL] Generating random unique alphanumeric IDs

On Mon, Aug 17, 2009 at 04:00:54PM +0200, Harald Fuchs wrote: > In article <20090816122526.gw5...@samason.me.uk>, > Sam Mason writes: > > > I've just had a look and PG does actually seem to be returning values as > > I'd expect, i.e. 0 <= n < 1. > > That's what everyone would expect. If it's re

Re: [GENERAL] Generating random unique alphanumeric IDs

In article <20090816122526.gw5...@samason.me.uk>, Sam Mason writes: > I've just had a look and PG does actually seem to be returning values as > I'd expect, i.e. 0 <= n < 1. That's what everyone would expect. If it's really implemented like that the documentation is wrong, isn't it? -- Sent

Re: [GENERAL] Generating random unique alphanumeric IDs

On Mon, Aug 17, 2009 at 12:17:29PM +, Jasen Betts wrote: > On 2009-08-17, Sam Mason wrote: > (i.e. their internal state is the same as > > it was before) but individual numbers *will* be repeated. > > numbers will not be repeated intil the state wraps if the number > returned represents the e

Re: [GENERAL] Generating random unique alphanumeric IDs

On 2009-08-17, Sam Mason wrote: > On Sun, Aug 16, 2009 at 04:53:01PM -0600, Bob Gobeille wrote: >> One way is to use a LFSR (linear feedback shift register function). I >> haven't used one in a long time but I recall generating pseudo random >> numbers that are guaranteed not to repeat after

Re: [GENERAL] Generating random unique alphanumeric IDs

Thom Brown wrote: > This would preferrably not resort to trying to generate the ID, then > checking for a clash, and if there is one, do it again, although that could > do as I can't think of how the ideal solution of a ID hashing algorithm > would be possible. As suggested upthread, this

Re: [GENERAL] Generating random unique alphanumeric IDs

On Sun, Aug 16, 2009 at 6:12 PM, Alvaro Herrera wrote: > It's in the wiki, in the Snippets area. > wiki.postgresql.org/wiki/Snippets > (pseudo encrypt or something like that I think it's called) Here's a simple 255 value linear feedback shift register. It's nothing fancy, but works as an example.

Re: [GENERAL] Generating random unique alphanumeric IDs

On Sun, Aug 16, 2009 at 04:53:01PM -0600, Bob Gobeille wrote: > One way is to use a LFSR (linear feedback shift register function). I > haven't used one in a long time but I recall generating pseudo random > numbers that are guaranteed not to repeat after billions of > iterations. It's very

Re: [GENERAL] Generating random unique alphanumeric IDs

Ivan Sergio Borgonovo escribió: > Sometimes ago Daniel Verite posted an implementation of a fiestel > cipher in plpgsql. It's in the wiki, in the Snippets area. wiki.postgresql.org/wiki/Snippets (pseudo encrypt or something like that I think it's called) -- Alvaro Herrera

Re: [GENERAL] Generating random unique alphanumeric IDs

On Aug 16, 2009, at 5:07 AM, Thom Brown wrote: Does anyone know a way to generate a random and unique lowercase alphanumeric ID (preferably without using 0, 1, o or i to prevent problems with users manually typing the ID) using SQL without resorting to a prerendered table or using GUIDs.

Re: [GENERAL] Generating random unique alphanumeric IDs

> I would have thought that duplications would become equally likely at the > 16,777,216 mark. Yes, at that point you're as likely to get a duplicate as a unique one--every time you do it. You're likely to see your first duplicate long before that point. In fact, it would be extremely unlikely to

Re: [GENERAL] Generating random unique alphanumeric IDs

Thom Brown wrote: I'm not sure why you're saying that there's a 50% chance of duplication after 7240 values though. With 33 million combinations, I would have thought that duplications would become equally likely at the 16,777,216 mark. Basic probability.

Re: [GENERAL] Generating random unique alphanumeric IDs

On Sun, Aug 16, 2009 at 12:57:34PM +0100, Thom Brown wrote: > > SELECT array_to_string(array(( > >SELECT SUBSTRING('abcdefghjklmnpqrstuvwxyz23456789' > > FROM mod((random()*32)::int, 32)+1 FOR 1) > >FROM generate_series(1,5))),''); I've just had a look and PG does actu

Re: [GENERAL] Generating random unique alphanumeric IDs

On Sun, 16 Aug 2009 12:48:39 +0100 Sam Mason wrote: > On Sun, Aug 16, 2009 at 12:07:27PM +0100, Thom Brown wrote: > > Does anyone know a way to generate a random and unique lowercase > > alphanumeric ID > > If you want it to be unique then it's not going to be random. The > easiest way to keep

Re: [GENERAL] Generating random unique alphanumeric IDs

> > The following is the obvious PGSQL code, you'd obviously need something > else to stop duplicates. > > SELECT array_to_string(array(( >SELECT SUBSTRING('abcdefghjklmnpqrstuvwxyz23456789' > FROM mod((random()*32)::int, 32)+1 FOR 1) >FROM generate_series(1,5))),''); >

Re: [GENERAL] Generating random unique alphanumeric IDs

On Sun, Aug 16, 2009 at 12:07:27PM +0100, Thom Brown wrote: > Does anyone know a way to generate a random and unique lowercase > alphanumeric ID If you want it to be unique then it's not going to be random. The easiest way to keep it from producing duplicates is to have some monotonically increas

[GENERAL] Generating random unique alphanumeric IDs

Does anyone know a way to generate a random and unique lowercase alphanumeric ID (preferably without using 0, 1, o or i to prevent problems with users manually typing the ID) using SQL without resorting to a prerendered table or using GUIDs. For example, if I were to ask for an ID of 5 characters,