Postgres Pro
Downloads
Home   >   mailing lists

Thread: [1.4rc1] Can't change column type

[1.4rc1] Can't change column type

From
Chris
Date:
I'm trying to change a char(30) to varchar(64), but I can't find a way to do it.
I select the table, select properties, select the column and click change, but
the data type and lengths are grayed out.

How do I change this column from char(30) to varchar(64)?

Re: [1.4rc1] Can't change column type

From
Joost Kraaijeveld
Date:
On Sun, 2005-11-06 at 01:12 +0000, Chris wrote:
> I'm trying to change a char(30) to varchar(64), but I can't find a way to do it.
> I select the table, select properties, select the column and click change, but
> the data type and lengths are grayed out.
> 
> How do I change this column from char(30) to varchar(64)?

Does this work (replace the names according to your schema)?

BEGIN;
ALTER TABLE table_name ADD COLUMN new_column varchar(64)?;
UPDATE table_name SET new_column = column;
ALTER TABLE customer RENAME column TO old_column;
ALTER TABLE customer RENAME new_column TO columns;
COMMIT;

-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

Re: [1.4rc1] Can't change column type

From
Chris
Date:
Joost Kraaijeveld <J.Kraaijeveld <at> Askesis.nl> writes:
> Does this work (replace the names according to your schema)?
> 
> BEGIN;
> ALTER TABLE table_name ADD COLUMN new_column varchar(64)?;
> UPDATE table_name SET new_column = column;
> ALTER TABLE customer RENAME column TO old_column;
> ALTER TABLE customer RENAME new_column TO columns;
> COMMIT;

In the end, I did this plu I dropped the old column, but what's the point of
having a tool like pgAdmin if common place everyday activities like this can't
be done?

Re: [1.4rc1] Can't change column type

From
Joost Kraaijeveld
Date:
On Sun, 2005-11-06 at 08:36 +0000, Chris wrote:

> In the end, I did this plu I dropped the old column, but what's the point of
> having a tool like pgAdmin if common place everyday activities like this can't
> be done?
Because data conversion is a major task. See
http://www.pgadmin.org/development.php#todo last entries.

Feel free ;-)

-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

Re: [1.4rc1] Can't change column type

From
Chris Velevitch
Date:
On 11/6/05, Sim Zacks <sim@compulab.co.il> wrote:
> However, if your main database activity is changing the data type of a
> column in ways that are disallowed by PostGreSQL, then you are
> correct. PGAdmin is pointless.

My main database activity is refactoring and version control of a
database. This activity should be supported by a tool like pgAdmin.

Re: [1.4rc1] Can't change column type

From
Dave Page
Date:


On 6/11/05 9:11 am, "Chris Velevitch" <chris.velevitch@gmail.com> wrote:

> On 11/6/05, Sim Zacks <sim@compulab.co.il> wrote:
>> However, if your main database activity is changing the data type of a
>> column in ways that are disallowed by PostGreSQL, then you are
>> correct. PGAdmin is pointless.
> 
> My main database activity is refactoring and version control of a
> database. This activity should be supported by a tool like pgAdmin.

It is, or at least it was. It looks like you just found the first bug in
1.4.0 :-(

Andreas, can you can a look please? I'm too tired atm :-(

Regards, Dave

Re: [1.4rc1] Can't change column type

From
Sim Zacks
Date:
I get a lot of benefit out of PGAdmin without that functionality.
It's actually a database limitation, not a PGAdmin limitation.

However, if your main database activity is changing the data type of a
column in ways that are disallowed by PostGreSQL, then you are
correct. PGAdmin is pointless.

Sim Zacks
CIO
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

________________________________________________________________________________

Joost Kraaijeveld <J.Kraaijeveld <at> Askesis.nl> writes:
> Does this work (replace the names according to your schema)?
> 
> BEGIN;
> ALTER TABLE table_name ADD COLUMN new_column varchar(64)?;
> UPDATE table_name SET new_column = column;
> ALTER TABLE customer RENAME column TO old_column;
> ALTER TABLE customer RENAME new_column TO columns;
> COMMIT;

In the end, I did this plu I dropped the old column, but what's the point of
having a tool like pgAdmin if common place everyday activities like this can't
be done?




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate      subscribe-nomail command to
majordomo@postgresql.orgso that your      message can get through to the mailing list cleanly

Re: [1.4rc1] Can't change column type

From
Andreas Pflug
Date:
Dave Page wrote:
> 
> 
> On 6/11/05 9:11 am, "Chris Velevitch" <chris.velevitch@gmail.com> wrote:
> 
> 
>>On 11/6/05, Sim Zacks <sim@compulab.co.il> wrote:
>>
>>>However, if your main database activity is changing the data type of a
>>>column in ways that are disallowed by PostGreSQL, then you are
>>>correct. PGAdmin is pointless.
>>
>>My main database activity is refactoring and version control of a
>>database. This activity should be supported by a tool like pgAdmin.
> 
> 
> It is, or at least it was. It looks like you just found the first bug in
> 1.4.0 :-(

Well, this is not a really bug but an incompletely supported pgsql8.x 
feature; 1.4 works the same way as 1.2.

We're allowing only binary compatible type changes (from pre 8.0 ages 
where we did type changes by direct catalog access), while with 8.x 
castable changes are allowed. We must have missed that in the 7.5 cycle.

Changed in svn for 1.4.1 and HEAD now, thanks for reporting.

Regards,
Andreas