Thread: changing field length

I am running 7.3.3. Can I change a field that is varying character(128) to varying character(250)?

Thanks
Jodi

"Jodi Kanter" <jkanter@virginia.edu> wrote:
> I am running 7.3.3. Can I change a field that is varying character(128)
> to varying character(250)?

nope.

You can
    a) create a new column named tmp of character(250).
    b) copy the content from old column to new column
    c) drop the old column
    d) rename the tmp column to the correct name


Regards
Gaeatano Mendola

PS: Be carefull with the constraint....

On Tue, 19 Aug 2003, Jodi Kanter wrote:

> I am running 7.3.3. Can I change a field that is varying character(128)
> to varying character(250)?

You can either add a column, copy the data across and drop the old column
and use rename to give the new column the old name or if you're willing
to hack at system tables, you can change atttypmod in pg_attribute for the
column from 132 (maxsize + 4 to hold the real size) to 254.

Jodi-

Here's an example of the "hack" approach, which I've used without causing
any problems for some time:

update pg_attribute set atttypmod = 104
where attrelid = ( select oid from pg_class where relname = 'actor'
and attname = 'actor_full_name' );

In your case, you'd substitute 254 for 104, your table name for "actor" and
your field name for "actor_full_name";

-Nick

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Jodi Kanter
Sent: Tuesday, August 19, 2003 1:34 PM
To: Postgres Admin List
Subject: [ADMIN] changing field length


I am running 7.3.3. Can I change a field that is varying character(128) to
varying character(250)?

Thanks
Jodi

--

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
<!--[if !supportEmptyParas]-->  <!--[endif]-->
<!--[if !supportEmptyParas]-->  <!--[endif]-->
<!--[if !supportEmptyParas]-->  <!--[endif]-->

how do I copy from one field to another?

Gaetano Mendola wrote:

"Jodi Kanter" <jkanter@virginia.edu> wrote:

I am running 7.3.3. Can I change a field that is varying character(128) 
to varying character(250)?

nope.

You can 
    a) create a new column named tmp of character(250).
    b) copy the content from old column to new column
    c) drop the old column
    d) rename the tmp column to the correct name


Regards
Gaeatano Mendola

PS: Be carefull with the constraint....
    


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

On Wed, Aug 20, 2003 at 10:16:38 -0400,
  Jodi Kanter <jkanter@virginia.edu> wrote:
> how do I copy from one field to another?

update tablename set newcol = oldcol;

ExecUpdate: Fail to add null value in not null attribute study_name

Is there any way around this error. Col1 is a not null field but col2 is not obviously since it is currently empty!
Thanks for the help.
Jodi

Bruno Wolff III wrote:

On Wed, Aug 20, 2003 at 10:16:38 -0400,
  Jodi Kanter <jkanter@virginia.edu> wrote:

how do I copy from one field to another?

update tablename set newcol = oldcol;

please ignore last message. I had my columns switched.
sorry about that!
Thanks for the quick response.
jodi

Bruno Wolff III wrote:

On Wed, Aug 20, 2003 at 10:16:38 -0400,
  Jodi Kanter <jkanter@virginia.edu> wrote:

how do I copy from one field to another?

update tablename set newcol = oldcol;

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

can I now make this field not null?

is it possible to move a field up in a table? now that I have done this move the new field is at the bottom of the list of fields. From time to time we have had to make changes to our schema so I will rerun our script and do a pg_dump of the data only and then restore the data from within psql. This new field in the dump is in a different location then it is when the script runs. I expect this will cause an error. I am doing pg_dumps with the following: -Rau
I realize I can move it down in the script but was wondering if there were another solution. Logically it makes sense to list it toward the top of the table.

Thanks
Jodi

Bruno Wolff III wrote:

On Wed, Aug 20, 2003 at 10:16:38 -0400,
  Jodi Kanter <jkanter@virginia.edu> wrote:

how do I copy from one field to another?

update tablename set newcol = oldcol;

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

On Wed, Aug 20, 2003 at 10:49:08 -0400,
  Jodi Kanter <jkanter@virginia.edu> wrote:
> can I now make this field not null?
>
> is it possible to move a field up in a table? now that I have done this

Currently, not without recreating the table. There was some discussion
about that a few months ago, but nothing is happening in 7.4 on that
front. Maybe in 7.5 there will be a way to change the column order,
but don't count on it.

Ok. so for now I want to return to where I started. so I renamed the table and regenerated my original table with the fields in the order that I like. I cannot just do a
insert into table select * from other_table;
because the fields are in a different order.
Can I do this by listing the fields in my insert in the order in which I want them placed?
Thanks
Jodi

Bruno Wolff III wrote:

On Wed, Aug 20, 2003 at 10:49:08 -0400,
  Jodi Kanter <jkanter@virginia.edu> wrote:

can I now make this field not null?

is it possible to move a field up in a table? now that I have done this 

Currently, not without recreating the table. There was some discussion
about that a few months ago, but nothing is happening in 7.4 on that
front. Maybe in 7.5 there will be a way to change the column order,
but don't count on it.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

On Wed, Aug 20, 2003 at 11:16:18 -0400,
  Jodi Kanter <jkanter@virginia.edu> wrote:
> Ok. so for now I want to return to where I started. so I renamed the
> table and regenerated my original table with the fields in the order
> that I like. I cannot just do a
> insert into table select * from other_table;
> because the fields are in a different order.
> Can I do this by listing the fields in my insert in the order in which I
> want them placed?

Yes. Instead of using '*' list the columns from the table that currently
has the data in the order that they appear in the table that is currently
empty.

As always, there's more than one way to skin a cat...

Create a new table and insert into it:

create newtable (field newdef, field2 newdef);
insert into
  newtable (
    select oldfield,
    oldfield2 from oldtable
  );

Create it on the fly with Postgresql casting shortcuts:
select
  field1::newtype,
  field2::newtype,
  field3,
  field4
into
  newtable
from
  oldtable;

Create it on the fly with ANSI style casting:
select
  cast(field1 as newtype),
  cast(field2 as newtype),
  field3,
  field4
into
  newtable
from
  oldtable;


On Wed, 20 Aug 2003, Jodi Kanter wrote:

> Ok. so for now I want to return to where I started. so I renamed the
> table and regenerated my original table with the fields in the order
> that I like. I cannot just do a
> insert into table select * from other_table;
> because the fields are in a different order.
> Can I do this by listing the fields in my insert in the order in which I
> want them placed?
> Thanks
> Jodi
>
> Bruno Wolff III wrote:
>
> >On Wed, Aug 20, 2003 at 10:49:08 -0400,
> >  Jodi Kanter <jkanter@virginia.edu> wrote:
> >
> >>can I now make this field not null?
> >>
> >>is it possible to move a field up in a table? now that I have done this
> >>
> >
> >Currently, not without recreating the table. There was some discussion
> >about that a few months ago, but nothing is happening in 7.4 on that
> >front. Maybe in 7.5 there will be a way to change the column order,
> >but don't count on it.
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: Have you searched our list archives?
> >
> >               http://archives.postgresql.org
> >
>
>