Re: HOWTO - Random character generation for primary key

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