Re: HOWTO - Random character generation for primary key
От | Alan Wayne |
---|---|
Тема | Re: HOWTO - Random character generation for primary key |
Дата | |
Msg-id | 20020504190949.4310.qmail@web21210.mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: HOWTO - Random character generation for primary key (Jean-Michel POURE <jm.poure@freesurf.fr>) |
Ответы |
Re: HOWTO - Random character generation for primary key
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: