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

Re: Alter Table Column Datatype

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Rod Taylor <rbt(at)rbt(dot)ca>
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Alter Table Column Datatype
Date: 2003-09-29 15:21:35
Message-ID: 24575.1064848895@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Rod Taylor <rbt(at)rbt(dot)ca> writes:
> Questions:
>         Is this syntax ok?
>                 ALTER TABLE <table> ALTER COLUMN <column> TYPE <new
>                 type>

It should be the same as whatever All Them Other Databases use.

>         COERCE_ASSIGNMENT is the appropriate coercion technique, I
>         assume? I don't like EXPLICIT as it would allow data to be
>         munged without telling the user about it.

I think there needs to be a way to allow explicit coercions; perhaps
even supply a conversion expression if the user wants a transformation
that doesn't correspond to the default coercion.  When you are doing
this by hand, you do
	UPDATE tab SET newcol = oldcol
which can easily accommodate any arbitrary expression on oldcol.

Perhaps the ALTER command could include an optional clause "TRANSFORM
expr-on-oldcol" to do this.  In that case, defaulting to assignment
coercion would be fine with me.

>         In order to correct the Var nodes in Check constraints, views,
>         etc. I  need to change the varattno and type information, then
>         wrap it in a cast to bring it back to the old data type?

I think a more likely way of making the conversion is to deparse the
constraint expression to text, then reparse using the new column
definition.  For example, if you were promoting an integer column to
numeric, and you had a constraint "x > 0", you'd probably be pretty
surprised if the constraint became "x::int > 0".  I think you'd want it
interpreted as numeric "x > 0".

Not sure to what extent we can apply that theory to views, though.
If it would result in a change in a view's output column datatype,
do we want to allow that (which would mean recursively propagating
that column type change to yet other places)?

>         Is there any way of expiring a function cache or, for that
>         matter, telling which functions use the column in question
>         internally?

Not at the moment.  Sooner or later we will have to address that issue...

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2003-09-29 15:30:06
Subject: Re: pg_dump no longer honors --no-reconnect
Previous:From: Tom LaneDate: 2003-09-29 15:04:55
Subject: Re: pg_dump bug in 7.4

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