Thread: Need clarifications......

Hello All,

I am having the following points to get clarified. I am new to
this open source database.

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
so. I am using the PGManager, the GUI tool for PostgreSQL. The
particular column datatype is being disabled. But when i check for
some other tables, it is enabled over there. I can't understand
why it is happening for some tables only.

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 will be happy to get some information regarding these two
points, so that it will help me to understand and study indepth
this open source database.

Thanks in advance.

Ravi
___________________________________________________
Download the hottest & happening ringtones here!
OR SMS: Top tone to 7333
Click here now:
http://sms.rediff.com/cgi-bin/ringtone/ringhome.pl

Hi all,

this question is, surely, very annoying, but still, i couldn't find
anything on my own.
Does anyone know where can i get Lithuanian charset (or is it locale?
i'm really new to PG...) for postgresql 7.3.3 (running on FreeBSD 5.0)?

Thanks,

Saulius

Novice,

I was looking at some sample code on a download of psql 7.3.3 today:

src/test/examples

I found they would not link.  Missing all psql API calls.  It was only
after I added to the following line to the local Makefile that they
compiled and worked:

LDLIBS += -L/usr/local/pgsql/lib -lpq

There is obviously something very wrong for me to need to add this line,
however, I am not familiar with the distribution to know where the error
is.  But this is a new clean instalation, so I dought it's anything I
have done.

This is an installation onto a new SuSE 8.2, after removing the native
SuSE PostgreSQL server.

Hope this is of some use,

Ben

> 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

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