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
theCAST command (pg 93). The fact is that the Posgres User's manual has nothing about cast and psql doesn't understand
thiscommand :-((((((( 
>
> 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'tinsert 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@whitehouse.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@scw.org>
Director of Information Systems, Support Center of Washington