Re: CAST doesn't work :-(

On Sun, 22 Apr 2001, Antonio Gennarini - Geotronix wrote:

> Hi.
>
> I read in Momjian's book that to change the CHAR length of a column in a table (from 30 -> 40) lets say, i'm to use the CAST command (pg 93). The fact is that the Posgres User's manual has nothing about cast and psql doesn't understand this command :-(((((((
>
> Anyone can tell me how to ajust a CHAR length in a table column? I found out that some email exceed 30 digits now i can't insert them and don't want to start from scratch.
>
> Thanks.

CAST doesn't change table attributes, it just changes the datatype of an
expression.

For example

SELECT CAST '2001-01-01' AS DATE;

turns the string '2001-01-01' into a date.

(Most PG users instead write the above as

SELECT date('2001-01-01')

or

SELECT '2001-01-01'::date

tho' they're PostgreSQL-isms)

You can't change the datatype of an existing column. Instead, create a new
table, insert the data into that, drop the existing table, and rename the
new one.

For example, if you have the table:

CREATE TABLE Pers (
id int not null primary key,
email varchar(30)
);

with some data in it:

insert into pers values (1, 'antigayweenie(at)whitehouse(dot)gov');

and you want to change email to varchar(50):

CREATE TABLE pers_new (
id int not null primary key,
email varchar(50)
);

[pg_dump can give you the CREATE statement for your table so you don't
have to re-create it by hand]

insert into pers_new select * from pers;

drop table pers;

alter table pers_new rename to pers;

If you have SERIAL datatypes (which use sequences behind the scenes,
you'll have to create the new table to use the existing sequence, and not
create a new one. In our example, that would be:

CREATE TABLE pers_new (
id int not null default nextval('pers_id_seq'),
...
);

rather than just "id serial not null".

HTH,
--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington