pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSyntax error while altering col-type
8+ messages / 5 participants
[nested] [flat]
* Syntax error while altering col-type
@ 2005-01-12 17:02 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 2 replies; 8+ messages in thread
From: KÖPFERL Robert @ 2005-01-12 17:02 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Syntax error while altering col-type
@ 2005-01-12 17:17 Michael Fuhr <mike@fuhr.org>
parent: KÖPFERL Robert <robert.koepferl@sonorys.at>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Fuhr @ 2005-01-12 17:17 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Syntax error while altering col-type
@ 2005-01-12 17:22 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: KÖPFERL Robert <robert.koepferl@sonorys.at>
1 sibling, 0 replies; 8+ messages in thread
From: Rosser Schwarz @ 2005-01-12 17:22 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Syntax error while altering col-type
@ 2005-01-13 08:48 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 2 replies; 8+ messages in thread
From: KÖPFERL Robert @ 2005-01-13 08:48 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
> -----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/
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Syntax error while altering col-type
@ 2005-01-13 10:19 Sam Mason <sam@samason.me.uk>
parent: KÖPFERL Robert <robert.koepferl@sonorys.at>
1 sibling, 0 replies; 8+ messages in thread
From: Sam Mason @ 2005-01-13 10:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Syntax error while altering col-type
@ 2005-01-13 12:05 Andrew Sullivan <ajs@crankycanuck.ca>
parent: KÖPFERL Robert <robert.koepferl@sonorys.at>
1 sibling, 0 replies; 8+ messages in thread
From: Andrew Sullivan @ 2005-01-13 12:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Syntax error while altering col-type
@ 2005-01-13 12:30 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 1 reply; 8+ messages in thread
From: KÖPFERL Robert @ 2005-01-13 12:30 UTC (permalink / raw)
To: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Syntax error while altering col-type
@ 2005-01-13 13:25 Andrew Sullivan <ajs@crankycanuck.ca>
parent: KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 8+ messages in thread
From: Andrew Sullivan @ 2005-01-13 13:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-01-13 13:25 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-12 17:02 Syntax error while altering col-type KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-01-12 17:17 ` Michael Fuhr <mike@fuhr.org>
2005-01-12 17:22 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2005-01-13 08:48 Re: Syntax error while altering col-type KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-01-13 10:19 ` Sam Mason <sam@samason.me.uk>
2005-01-13 12:05 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-01-13 12:30 Re: Syntax error while altering col-type KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-01-13 13:25 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox