Skip site navigation (1) Skip section navigation (2)

Rewriting existing table tuples on alter type

From: Rikard Pavelic <rikard(dot)pavelic(at)zg(dot)htnet(dot)hr>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Rewriting existing table tuples on alter type
Date: 2012-06-30 12:59:07
Message-ID: 4FEEF81B.8010106@zg.htnet.hr (view raw or flat)
Thread:
Lists: pgsql-hackers
How hard would it be to rewrite table content on composite attribute type change?

For simple use cases:
create type complex as (i int, j int);
create table numbers (c complex);
insert into numbers values(row(1,2));

I  can work around
alter complex from int to bigint
"fairly" easy with
alter type complex add attribute _tmp_i bigint;
--loop for each table
update numbers set c._tmp_i = (c).i;
--end loop
alter type complex drop attribute i;
alter type complex rename attribute _tmp_i to i;

I can "easily" work with deep nested types, but things starts to get PITA when there is
array involved because I need to unroll it and pack it again.

Regards,
Rikard

Responses

pgsql-hackers by date

Next:From: Edson RichterDate: 2012-06-30 14:10:25
Subject: Re: Can someone help me to get ODBC fdw running on windows?
Previous:From: C├ędric VillemainDate: 2012-06-30 11:06:10
Subject: Re: Pruning the TODO list

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group