lundi 29 décembre 2014

PostgreSQL replace primary key and converting non-serial to serial type


I'm basically building my own version of phpPgAdmin (if I get good enough to make it good enough I'll post it on a repository hosting service) and currently I'm revising my primary key tool to replace an existing primary key.


Unfortunately I've run in to two issues as I'm used MySQL though have been learning PostgreSQL for about two months and can get around fairly well.




  1. Setting a primary key initially is not a problem, changing a column to replace an existing key doesn't seem to work as pgAdmin III reports the original primary key column is still the primary key column.



    1. Unlike MySQL that sets the type to autoincrement when setting the type PostgreSQL uses serial and bigserial however apparently it's not possible to convert an existing (all empty rows) column from numeric to bigserial. Using the following query...


    ALTER TABLE custaddress ALTER COLUMN id1 TYPE bigserial;




...I receive the error...



ERROR: type "bigserial" does not exist



How do I properly change the primary key of a table if a column is already set to be the primary key?


Do I have to convert the original primary key type to numeric before changing the primary key?


Do I have to do something different when setting the primary key if I have to delete the original primary key first or change it's type?





Aucun commentaire:

Enregistrer un commentaire