Thread: changing the size of a column without losing data

hi,

How would i change a field which is currently char100 to char50?

have tried:
ALTER TABLE outlets MODIFY description varchar(50);

but get error:
parser: parse error at or near "modify"

what is the correct command.

Thx

Mark

From:               "Mark Seftel" <mark@trustemail.com>
To:                 <pgsql-novice@postgresql.org>
Subject:            [NOVICE] changing the size of a column without losing data
Date sent:          Thu, 22 Aug 2002 19:43:57 +0200

Mark

Alter colummn isn't implemented beyond renaming as far as I know,

I do it by brute force, eg:

Begin;
ALTER TABLE table RENAME TO table2;

DROP INDEX table_pkey;

CREATE TEMPORARY TABLE tabletemp() INHERITS (table2);

INSERT INTO tabletemp SELECT * FROM table2;

CREATE TABLE table(
field varchar(150) PRIMARY KEY);


INSERT INTO table SELECT * FROM tabletemp;

DROP table2;

DROP tabletemp;

End;

I always do these things in a transaction (wonderful things) as I
often make typo's

Hope this helps

Paul Butler


> hi,
>
> How would i change a field which is currently char100 to char50?
>
> have tried:
> ALTER TABLE outlets MODIFY description varchar(50);
>
> but get error:
> parser: parse error at or near "modify"
>
> what is the correct command.
>
> Thx
>
> Mark
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

From:               "Mark Seftel" <mark@trustemail.com>
To:                 <pgsql-novice@postgresql.org>
Subject:            [NOVICE] changing the size of a column without losing data
Date sent:          Thu, 22 Aug 2002 19:43:57 +0200

Like I said I make a lot of typo's

What I meant to write was:

begin;

ALTER table table0 RENAME TO table1;

DROP INDEX table0_pkey;

 CREATE TEMPORARY table table1temp() INHERITS (table1);

INSERT INTO table1temp SELECT * FROM table1;

CREATE table table0(
field varchar(50) PRIMARY KEY);

INSERT INTO table0 SELECT * FROM table1temp;

DROP table table1temp;

DROP table table1;
end;

Though looking at it again I'm not sure why I used the temporary
table, this seems to work as well:

begin;

ALTER table table0 RENAME TO table1;

DROP INDEX table0_pkey;

CREATE table table0(
field varchar(50) PRIMARY KEY);

INSERT INTO table0 SELECT * FROM table1;

DROP table table1;
end;

Watch out for the data in the old column(100) being too big for the
new one (50),
Hope this actually helps

Paul Butler




> hi,
>
> How would i change a field which is currently char100 to char50?
>
> have tried:
> ALTER TABLE outlets MODIFY description varchar(50);
>
> but get error:
> parser: parse error at or near "modify"
>
> what is the correct command.
>
> Thx
>
> Mark
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Hi !

One quick way to do this is to take a dump from your x_db. Edit the dump file
and change your field's defition from character varying(100) to character
varying (50)

Make sure the data is valid for this shorter field. Drop your x_db. Create a
new empty x_db. Read the dump in the new x_db.

BR,

aarni



On Thursday 22 August 2002 08:43 pm, you wrote:
> hi,
>
> How would i change a field which is currently char100 to char50?
>
> have tried:
> ALTER TABLE outlets MODIFY description varchar(50);
>
> but get error:
> parser: parse error at or near "modify"
>
> what is the correct command.
>
> Thx
>
> Mark
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)