Pgadmin - [1.4rc1] Can't change column type

Author

[1.4rc1] Can't change column type

Chris

2005-11-08, 4:13 pm

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)?

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

Joost Kraaijeveld

2005-11-08, 4:13 pm

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Chris

2005-11-08, 4:13 pm

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
.org so that your
message can get through to the mailing list cleanly

Joost Kraaijeveld

2005-11-08, 4:13 pm

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Chris Velevitch

2005-11-08, 4:13 pm

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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Dave Page

2005-11-08, 4:13 pm

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sim Zacks

2005-11-08, 4:13 pm

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
.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Andreas Pflug

2005-11-09, 7:24 am

Dave Page wrote:
>
>
> On 6/11/05 9:11 am, "Chris Velevitch" <chris.velevitch@gmail.com> wrote:
>
>
>
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links