Thread: About changing type af a column

Hi all,

I need to change a column length.

From varchar(150) to varchar(250).

It's easy to change with an ALTER command? Or I need to create a new column,
copy all data from the old column to the new one, then drop the old and
rename the new.

If only exists the second solutions, the new column it will be created last
one of the columns, then I must change all INSERT and UPDATE commands in my
application.
It's possible to create a column in a given position in the columns
structure of a table?.

Thanks in advance.

--
Marc Soler

* Marc Soler <msoler@el-valles.com> [26.03.2003 11:48]:
> Hi all,
>
> I need to change a column length.
>
> >From varchar(150) to varchar(250).
>
> It's easy to change with an ALTER command? Or I need to create a new column,
> copy all data from the old column to the new one, then drop the old and
> rename the new.
>
> If only exists the second solutions, the new column it will be created last
> one of the columns, then I must change all INSERT and UPDATE commands in my
> application.

How order of fields in the table can affect UPDATE statements?

About INSERTs - it's a good thing to specify all fields being inserted just
to prevent 'inconveniences' like you've mentioned.

> It's possible to create a column in a given position in the columns
> structure of a table?.

Yes. create a new table with same design except for the field you'd like to
change, than copy all data into new table, drop old one and rename new one.

>
> Thanks in advance.

--

Victor Yegorov

> >From varchar(150) to varchar(250).

See pg_attribute.atttypmod. Searching the archive would have answered it.

regards,
bhuvaneswaran

On Wed, 2003-03-26 at 04:56, Victor Yegorov wrote:
> * Marc Soler <msoler@el-valles.com> [26.03.2003 11:48]:
> > Hi all,
> >
> > I need to change a column length.
> >
> > >From varchar(150) to varchar(250).
> >
> > It's easy to change with an ALTER command? Or I need to create a new
> column,
> > copy all data from the old column to the new one, then drop the old
> and
> > rename the new.
> >
> > If only exists the second solutions, the new column it will be created
> last
> > one of the columns, then I must change all INSERT and UPDATE commands
> in my
> > application.
>
> How order of fields in the table can affect UPDATE statements?
>
> About INSERTs - it's a good thing to specify all fields being inserted
> just
> to prevent 'inconveniences' like you've mentioned.
>

And actually relational theory states that column orders in tables is
irrelevant, so it's really a good idea to make sure you keep from
falling into this trap. This means referencing result set array by field
name rather than by number (ie. $arr[2] - which implies field position)

> > It's possible to create a column in a given position in the columns
> > structure of a table?.
>
> Yes. create a new table with same design except for the field you'd like
> to
> change, than copy all data into new table, drop old one and rename new
> one.
>

funny part is I just answered this question on the -sql list yesterday,
so I happen to have the link handy to my archived post from several
months back that also answers this same question; check it out:

http://fts.postgresql.org/db/mw/msg.html?mid=1071582


Robert Treat