title: Adding primary key changes column type
body: <p>Our database currently doesn't define primary keys on any tables. All of the <code>id</code> columns are simply unique indexes. I'm dropping those indexes and replacing them with proper primary keys.</p>
  
  <p><strong>My problem:</strong> In Postgres 8.4.7, one table in particular changes the data type from <code>bigint</code> to <code>integer</code> when I add the primary key to the table.</p>
  
  <p>I've got the following table definition:</p>
  
  <pre><code>psql=# \d events
                                           Table "public.events"
          Column         |           Type           |                      Modifiers                      
  -----------------------+--------------------------+-----------------------------------------------------
   id                    | bigint                   | not null default nextval('events_id_seq'::regclass)
   [more columns omitted]
  
  Indexes:
      "events_id_unique_pk" UNIQUE, btree (id)
  Foreign-key constraints:
      "events_clearing_event_ref_fk" FOREIGN KEY (clearing_event_id) REFERENCES events(id)
      "events_event_configs_id_fk" FOREIGN KEY (event_config_id) REFERENCES event_configs(id)
      "events_pdu_circuitbreaker_id_fk" FOREIGN KEY (pdu_circuitbreaker_id) REFERENCES pdu_circuitbreaker(id)
      "events_pdu_id_fk" FOREIGN KEY (pdu_id) REFERENCES pdus(id) ON DELETE CASCADE
      "events_pdu_outlet_id_fk" FOREIGN KEY (pdu_outlet_id) REFERENCES pdu_outlet(id)
      "events_sensor_id_fk" FOREIGN KEY (sensor_id) REFERENCES sensors(id)
      "events_user_id_fk" FOREIGN KEY (clearing_user_id) REFERENCES users(id)
  Referenced by:
      TABLE "events" CONSTRAINT "events_clearing_event_ref_fk" FOREIGN KEY (clearing_event_id) REFERENCES events(id)
      TABLE "event_params" CONSTRAINT "events_params_event_id_fk" FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
  Triggers:
      event_validate BEFORE INSERT OR UPDATE ON events FOR EACH ROW EXECUTE PROCEDURE event_validate()
  </code></pre>
  
  <p>This is what happens:</p>
  
  <pre><code>psql=# ALTER TABLE events ADD PRIMARY KEY (id);
  NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "events_pkey" for table "events"
  ALTER TABLE
  psql=# \d events
                                           Table "public.events"
          Column         |           Type           |                      Modifiers                      
  -----------------------+--------------------------+-----------------------------------------------------
   id                    | integer                  | not null default nextval('events_id_seq'::regclass)
   [more columns omitted]
  
  Indexes:
      "events_pkey" PRIMARY KEY, btree (id)
      "events_id_unique_pk" UNIQUE, btree (id)
  Foreign-key constraints:
      "events_clearing_event_ref_fk" FOREIGN KEY (clearing_event_id) REFERENCES events(id)
      "events_event_configs_id_fk" FOREIGN KEY (event_config_id) REFERENCES event_configs(id)
      "events_pdu_circuitbreaker_id_fk" FOREIGN KEY (pdu_circuitbreaker_id) REFERENCES pdu_circuitbreaker(id)
      "events_pdu_id_fk" FOREIGN KEY (pdu_id) REFERENCES pdus(id) ON DELETE CASCADE
      "events_pdu_outlet_id_fk" FOREIGN KEY (pdu_outlet_id) REFERENCES pdu_outlet(id)
      "events_sensor_id_fk" FOREIGN KEY (sensor_id) REFERENCES sensors(id)
      "events_user_id_fk" FOREIGN KEY (clearing_user_id) REFERENCES users(id)
  Referenced by:
      TABLE "events" CONSTRAINT "events_clearing_event_ref_fk" FOREIGN KEY (clearing_event_id) REFERENCES events(id)
      TABLE "event_params" CONSTRAINT "events_params_event_id_fk" FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
  Triggers:
      event_validate BEFORE INSERT OR UPDATE ON events FOR EACH ROW EXECUTE PROCEDURE event_validate()
  </code></pre>
  
  <p>I considered a few workarounds, but I'd really rather know why it's happening. There are a few other tables that also use <code>bigint</code>, so I don't want to just hack a solution in place.</p>
  
  <p>This is scripted with Liquibase, but it happens directly in the Postgres console too.</p>
  
  <hr>
  
  <p><strong>Update</strong></p>
  
  <p>Two other points:</p>
  
  <ul>
  <li>I can create a simple table with a <code>bigint</code> id and a unique index on <code>id</code>, add the primary key, and the column type stays the same.</li>
  <li>All tables are empty at the time execution.</li>
  </ul>
  
  <p>Could it have something to do with the constraints?</p>