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
– Ihor Romanchenko, Commented Mar 16, 2016 at 11:47
You should delete constrains between product_awbstock.airwaybill_number and ?.awbstock_id — Ing. Gerardo Sánchez
– Ing. Gerardo Sánchez, Commented Mar 16, 2016 at 12:00
You also have to alter that other table's column to varchar. But what's wrong with the current bigint data type? — jarlh
– jarlh, Commented Mar 16, 2016 at 12:02

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

2

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);

answered Mar 16, 2016 at 14:11

Rodrigo Hjort

3113 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ALTER table query in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related