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?

  • 2
    The answer to your question can be found in my question here: dba.stackexchange.com/questions/47098/… ... in particular, this part: "SERIAL and BIGSERIAL are kind of pseudo-types. As you noticed, they are really just INT and BIGINT internally. What happens behind the scenes is that PostgreSQL creates a sequence and sets up a dependency on it to the table. You can search pg_class for the sequence name and how it relates to the table." – Joishi Bodio Dec 29 '14 at 19:55
  • 1