Changing referenced primary key column type

From: "Andrus Moor" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Changing referenced primary key column type
Date: 2009-08-07 20:57:13
Message-ID: 0B7510F8B13F4675934EA2A0C7023B74@andrusnotebook
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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+ ?

Andrus.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Neil Best 2009-08-07 21:21:27 Re: \copy: unexpected response (4)
Previous Message Ray Stell 2009-08-07 20:07:03 Re: Postgresql Backups