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, '[EMAIL PROTECTED]');
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 <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
