CODEWITHSUNDEEP
sqlpostgresqlddl
onkar
onkar

Reputation: 4547

ALTER table query in Postgres

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.

Upvotes: 1

Views: 1944

Answers (1)

Rodrigo Hjort
Rodrigo Hjort

Reputation: 311

You should:

  1. drop primary key constraint on first table

ALTER TABLE product_awbstock DROP CONSTRAINT product_awbstock_pkey;

  1. drop foreign key constraint on second table

ALTER TABLE ??? DROP CONSTRAINT awbstock_id_refs_airwaybill_number_d438187b;

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

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

Upvotes: 2

Related Questions