ALTER table query in Postgres

Question

I am trying to change the data type of one column in the table from biginteger to varchar.

myproject-# \d+ product_awbstock
                         Table "public.product_awbstock"
      Column       |           Type           | Modifiers | Storage | Description 
-------------------+--------------------------+-----------+---------+-------------
 airwaybill_number | bigint                   | not null  | plain   | 
 used              | boolean                  | not null  | plain   | 
 created           | timestamp with time zone | not null  | plain   | 
 modified          | timestamp with time zone | not null  | plain   | 
Indexes:
    "product_awbstock_pkey" PRIMARY KEY, btree (airwaybill_number)
    "product_awbstock_used" btree (used)

I am using this query, the error is also given.

alter table product_awbstock ALTER  COLUMN airwaybill_number TYPE varchar(15);

ERROR: foreign key constraint "awbstock_id_refs_airwaybill_number_d438187b" cannot be implemented

DETAIL: Key columns "awbstock_id" and "airwaybill_number" are of incompatible types: bigint and character varying.

The error message states that this column is referred from other table. You can not have a reference between columns with different types. — Ihor Romanchenko
You should delete constrains between product_awbstock.airwaybill_number and ?.awbstock_id — Ing. Gerardo Sánchez
You also have to alter that other table's column to varchar. But what's wrong with the current bigint data type? — jarlh

Rodrigo Hjort Rodrigo Hjort · Accepted Answer · 2016-03-16T14:11:07

You should:

drop primary key constraint on first table

ALTER TABLE product_awbstock DROP CONSTRAINT product_awbstock_pkey;

drop foreign key constraint on second table

ALTER TABLE ??? DROP CONSTRAINT awbstock_id_refs_airwaybill_number_d438187b;

alter column data types on both tables

ALTER TABLE product_awbstock ALTER COLUMN airwaybill_number TYPE varchar(15);

ALTER TABLE ??? ALTER COLUMN airwaybill_id TYPE varchar(15);

recreate previously removed constraints

ALTER TABLE product_awbstock ADD CONSTRAINT product_awbstock_pkey PRIMARY KEY (airwaybill_number);

ALTER TABLE ??? ADD CONSTRAINT awbstock_id_refs_airwaybill_number_d438187b FOREIGN KEY (awbstock_id) REFERENCES product_awbstock (airwaybill_number);

ALTER table query in Postgres

1 Answers