Re: Changing referenced primary key column type - Mailing list pgsql-general

From Tom Lane
Subject Re: Changing referenced primary key column type
Date
Msg-id 392.1249681330@sss.pgh.pa.us
Whole thread Raw
In response to Changing referenced primary key column type  ("Andrus Moor" <kobruleht2@hot.ee>)
List pgsql-general
"Andrus Moor" <kobruleht2@hot.ee> writes:
> 8.x Db contains tables
> create table ametikoh ( ametikoht numeric(7)  NOT NULL DEFAULT
> nextval('firma1.ametikoh_ametikoht_seq'::regclass) primary key );
> create table iandmed ( ametikoht numeric(7) references ametikoh);

> I created conversion script which normalizes column types:

> alter table ametikoh alter column ametikoht type integer;
> alter table iandmed alter column ametikoht type integer;

> Running this script causes error:

> ERROR:  foreign key constraint "iandmed_ametikoht_fkey" cannot be
> implemented
> DETAIL:  Key columns "ametikoht" and "ametikoht" are of incompatible types:
> numeric and integer.

> How to create script which performs this change and works in  8.0+ ?

Drop the FK constraint and then add it back afterwards.

            regards, tom lane

pgsql-general by date:

Previous
From: Neil Best
Date:
Subject: Re: \copy: unexpected response (4)
Next
From: Tom Lane
Date:
Subject: Re: [HACKERS] \copy: unexpected response (4)