Re: HOWTO - Random character generation for primary key

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;