Hey thanks for the tip bhuvaneswaran. I don't know how many times we've
had to change the length of a column after a table is in product because of
a management decision. In the past, we've always had to drop the table and
all its related views and recreate them.
Patrick Hatcher
"A.Bhuvaneswaran"
<bhuvan@symonds.net> To: Ravi AVK <ravi_nunet@rediffmail.com>
Sent by: cc: pgsql-novice@postgresql.org
pgsql-novice-owner@post Subject: Re: [NOVICE] Need clarifications......
gresql.org
07/23/2003 02:38 AM
> 1. I have created a table by giving varchar(100).
>
> Now if i want to increase or decrease the value, i am unable to do
IMO, there are two methods.
First method:
Update pg_attribute system table and set atttypmod appropriately. Let us
assume your table is 'foo' and column is 'bar' and new length is 100. The
sql is:
=# update pg_attribute set atttymod = 100 + 4 where attname = 'bar' and
attrelid = pg_class.oid and pg_class.relname = 'foo';
Second method:
1) Create new column, bar1 with desired length.
2) Set bar1 = bar.
3) Drop column bar.
4) Rename bar1 to bar.
> 2. Is there any way to change the datatype ie., i want to change
> from varchar(255) to text datatype. Data is present, can delete
> the data but don't want to drop and recreate the same as it will
> make me to recreate the references.
I am not sure, but it works for me. Let us assume your table is 'foo' and
column is 'bar' and old type is varchar and new type is text. The sql is:
=# update pg_attribute set atttypid = pg_type.oid, atttypmod = -1 where
attname = 'bar' and attrelid = pg_class.oid and pg_class.relname = 'foo'
and pg_type.typname = 'text';
The above second method is also applicable here.
regards,
bhuvaneswaran
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster