Re: 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(dot)poure(at)freesurf(dot)fr> wrote:
> > Dear Alan,
> >
> > When you write me, please CC me on
> > pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)