Re: HOWTO - Random character generation for primary key

Could you:

. Keep your 10-letter char keys in table

. create a sequence (manually, with CREATE SEQUENCE)

. create the table with a default value of next_charkey()

. next_charkey() calls nextval() to get the next value of the sequence and
converts this to a 10-char symbol (pick your own int -> char conversion
routine) and returns to the table

There will be more overhead, so if you're adding lots of records,
next_charkey() should be done in C.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Lincoln Yeoh
> Sent: Monday, May 06, 2002 9:47 AM
> To: Alan Wayne; jm.poure@freesurf.fr
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] HOWTO - Random character generation for primary
> key
>
>
> What are you trying to achieve? There doesn't seem to be enough
> information
> to help you properly.
>
> a) What do your 10 character keys look like? visible ASCII? legal Base64
> charset?
> b) How were they generated previously?
> c) Why do you think they need to look that way?
> d) Can you really change your app to use ints?
>
> Regards,
> Link.
>
> At 12:09 PM 5/4/02 -0700, Alan Wayne wrote:
> >Hi!
> >So ultimately, the better way would be to
> >bite-the-bullet and work towards replacing the current
> >character keys with int4 keys?
> >
> >(This will be quite time consuming on several million
> >records and about 35 tables with referential integrity
> >rules--a quick and dirty way would be appreciated.)
> >
> >Cheers,
> >Alan
> >
> >--- Jean-Michel POURE <jm.poure@freesurf.fr> wrote:
> > > Dear Alan,
> > >
> > > When you write me, please CC me on
> > > pgsql-general@postgresql.org so that anyone
> > > can participate.
> > >
> > > > Question: What would happen if I did the
> > > following:
> > > >    1. used some variant of "alter table" to change
> > > the
> > > > character field primary key to a field of type
> > > > 'serial'? i.e., would the binary form of the
> > > current
> > > > 10 length characters be preserved as some kind of
> > > > integer?
> > >
> > > In PostgreSQL, serial values are int4 auto-increment
> > > values. Therefore, there
> > > is no easy way to migrate your 10 characters long
> > > primary keys.
> > >
> > > By the way PostgreSQL does not support type
> > > promotion <-> demotion. You will
> > > have to wait for 7.3 or later to convert column
> > > types. For example, you
> > > cannot change an in4 into an int8, a varchar into a
> > > text column.
> > >
> > > Presently the solution is to add an int4 field to
> > > your tables ... and fill
> > > them with incremental values ... and update sequence
> > > values by hand.
> > >
> > > When your done, rename your tables with '_old',
> > > recreate them without 10
> > > characters primary keys and fill them with data.
> > >
> > > This should be easy in pgAdmin2 because you can copy
> > > table definition
> > > and paste it in the execution window. pgAdmin2 also
> > > gives you access to
> > > sequences.
> > >
> > > >   2. If postgresql does allow me to change the
> > > primary
> > > > key field from character to type serial (i.e.,
> > > > integer) in the first table, what will the
> > > referential
> > > > integrity rules do to the other tables that use
> > > the
> > > > first table's primary key as a secondary key?
> > > i.e.,
> > > > does postgre preceive the change of data-type as a
> > > > change to be echoed to the referencing tables via
> > > the
> > > > referential integrity rules? Furthermore, would
> > > this
> > > > recognition only be on newly added records, or on
> > > the
> > > > records already in the tables? e.g., if no change
> > > is
> > > > detected until a new record is added, could I go
> > > > through the database one table at a time and
> > > change
> > > > the referencing fields to type integer to match
> > > the
> > > > change in the primary key? (Needless to say, I
> > > can't
> > > > alter the keys without all the related records
> > > being
> > > > changed too--or I lose my relationships).
> > > > The current records are using pure characters of
> > > 10
> > > > byte length. If postgre could accept the above
> > > changes
> > > > before adding new records, then could the simple
> > > type
> > > > 'serial' be used without having the default
> > > produced
> > > > integers on new records clobber the existing
> > > > 'characters' ?
> > >
> > > If you don't want to migrate, set primary key column
> > > default value to
> > > random_string(10). See my previous HOWTO.
> > >
> > > Please note this is not a very standard way to
> > > proceed. In a profesionnal
> > > environment, you should use integer primary keys.
> > >
> > > Example :
> > > CREATE TABLE foo (
> > > foo_oid serial,
> > > foo_name varchar(254),
> > > foo_text text)
> > > WITH OIDS;
> > >
> > > is better than
> > >
> > > CREATE TABLE bar (
> > > bar_key char(10) random_string(10),
> > > bar_name varchar(254),
> > > bar_text text)
> > > WITH OIDS;
> >
> >
> >__________________________________________________
> >Do You Yahoo!?
> >Yahoo! Health - your guide to health and wellness
> >http://health.yahoo.com
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>