Обсуждение: Syntax error while altering col-type

Hi, I am perplexed.

I tried to change the type of a column using the syntax I found in the
[ALTER TABLE] section:
ALTER TABLE "Mailboxes" ALTER COLUMN "Status" TYPE int4;

This shuld be no problem since the current type acutally is int4 and the
names are copy'n'pasted. The server responds as follows:

ERROR:  syntax error at or near "TYPE" at character 47

It seems like it doesn't like the "type". My compiler-experience tells me
that the column identifier is somehow broken. But it exists!

What went wrong?

On Wed, Jan 12, 2005 at 06:02:10PM +0100, KÖPFERL Robert wrote:

> ALTER TABLE "Mailboxes" ALTER COLUMN "Status" TYPE int4;
> 
> This shuld be no problem since the current type acutally is int4 and the
> names are copy'n'pasted. The server responds as follows:
> 
> ERROR:  syntax error at or near "TYPE" at character 47

That's exactly the error you'd get on a pre-8.0 system that doesn't
support altering a column's type.  Are you looking at 8.0 documentation
but running a 7.x server?  What does "SELECT version();" show?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

while you weren't looking, KÖPFERL Robert wrote:

> ERROR:  syntax error at or near "TYPE" at character 47

What version are you running?  To my knowledge, altering the type of a
column is a new feature to 8.0.

/rls

--
:wq

> -----Original Message-----

> 
> That's exactly the error you'd get on a pre-8.0 system that doesn't
> support altering a column's type.  Are you looking at 8.0 
> documentation
> but running a 7.x server?  What does "SELECT version();" show?

Yes, that's it. I am looking into an 8.0 doc while running 7.4.
I'm a little bit perplexed now... is it really the case that pre 8.0 systems
aren't able to change col-types?
thx

> 
> -- 
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
> 

KÖPFERL Robert wrote:
>I'm a little bit perplexed now... is it really the case that pre 8.0 systems
>aren't able to change col-types?

I would guess that the column type altering code is just short hand
for creating a new column of the correct type, copying the old column
into the new one, deleting the old column, and renaming the new column
to be the same as the old one.

That is just a guess though!  But if it's all bundled inside a
transaction it should have about the same semantics, shouldn't it?

Cheers, Sam

On Thu, Jan 13, 2005 at 09:48:02AM +0100, KÖPFERL Robert wrote:

> I'm a little bit perplexed now... is it really the case that pre 8.0 systems
> aren't able to change col-types?

It really is.  In fact, the feature was (IIRC) somewhat
controversial, because there are all sorts of decisions that need to
be made about what to do with incompatible types.  What if you change
from int8 to int4?  What about varchar(4) to char(4)?  Just to name
two simple-minded examples.  See the -general and -hackers archives
for plenty of previous discussion of this stuff.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?    --attr. John Maynard Keynes

> 
> It really is.  In fact, the feature was (IIRC) somewhat
> controversial, because there are all sorts of decisions that need to
> be made about what to do with incompatible types.  What if you change
> from int8 to int4?  What about varchar(4) to char(4)?  Just to name
> two simple-minded examples.  See the -general and -hackers archives
> for plenty of previous discussion of this stuff.
> 

but that's the reason, the USING clause exists. It however still remains a
problem if triggers, constraints or referential integrity or vews are
involved.

so far

On Thu, Jan 13, 2005 at 01:30:21PM +0100, KÖPFERL Robert wrote:

> but that's the reason, the USING clause exists. It however still remains a

Right.  Please see the archives about how this was hammered out.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.    --Alexander Hamilton