Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group