Re: Need clarifications......

From: "A(dot)Bhuvaneswaran" <bhuvan(at)symonds(dot)net>
To: Ravi AVK <ravi_nunet(at)rediffmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Need clarifications......
Date: 2003-07-23 09:38:51
Message-ID: Pine.LNX.4.44.0307231455120.2993-100000@Bhuvan.bksys.co.in
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

> 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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Joepie Platteau 2003-07-23 13:19:45 PSQL locks records in the table...
Previous Message Jason Topaz 2003-07-23 08:57:45 Struggling with set-returning functions, seeking advice