Thread: 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