Resize varchar column

[John Sidney-Woollett]

Is it possible to alter a table to resize a varchar column? The PG docs
indicate lots of uses for "alter table T alter column C..." but not one
that allows the changing of the type (or same type, new size).

This is possible in Oracle (provided you are increasing the column size).

Is there a way to resize the column without having to drop or recreate the
table or column?

Thanks

John Sidney-Woollett

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[John Sidney-Woollett]

Re: Resize varchar column

Bruno Wolff III said:[color=blue]
> In general (in 7.4.x) you can change data types using add, drop, rename
> and a query to copy/translate the data. This will have the side effect of
> making the updated column the last column when using * to refer to the
> table's columns.[/color]

I'll take a look at the system catalogs - thanks for the tip.
[color=blue]
> It is possible to change the size of varchar data by updating the system
> catalogs. I don't know the exact details of what to do, but the
> instructions should be in the archives (multiple times).[/color]

I was hoping to avoid a column drop and recreate.

Thanks for the info.

John Sidney-Woollett



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Dirk Försterling]

Re: Resize varchar column

Bruno Wolff III wrote:[color=blue]
> catalogs. I don't know the exact details of what to do, but the instructions
> should be in the archives (multiple times).[/color]

Maybe. But how to find this? Any query on archives.postgr esql.org
returns just

"Sorry, but search returned no results.

Try to produce less restrictive search query"

for me (with varying search time).

-dirk

--
D i r k F "o r s t e r l i n g
r@zorbla.de http://r@zorbla.de/
-------------
"Diese Rekursionen sind nicht mit den Haaren herbeigezaubert " - R.K.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[scott.marlowe]

Re: Resize varchar column

On Wed, 4 Feb 2004, John Sidney-Woollett wrote:
[color=blue]
> Bruno Wolff III said:[color=green]
> > In general (in 7.4.x) you can change data types using add, drop, rename
> > and a query to copy/translate the data. This will have the side effect of
> > making the updated column the last column when using * to refer to the
> > table's columns.[/color]
>
> I'll take a look at the system catalogs - thanks for the tip.
>[color=green]
> > It is possible to change the size of varchar data by updating the system
> > catalogs. I don't know the exact details of what to do, but the
> > instructions should be in the archives (multiple times).[/color]
>
> I was hoping to avoid a column drop and recreate.
>
> Thanks for the info.[/color]

There is a way to do it. I don't recall it off my head, it has to do with
tweaking the system catalogs directly. That said, I would practice on a
test database, backup the live database, and cross my fingers and use a
chicken foot to make sure you don't scram your database, as playing with
system catalogs by hand is not something that's guaranteed to be a nice
happy safe thing. But as messing with the system catalogs go, this is one
of the easier things to do.

It should be in the list archives somewhere, and should probably be an
admin faq by now.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Richard Huxton]

Re: Resize varchar column

On Wednesday 04 February 2004 16:00, John Sidney-Woollett wrote:[color=blue]
> Bruno Wolff III said:[color=green]
> > In general (in 7.4.x) you can change data types using add, drop, rename
> > and a query to copy/translate the data. This will have the side effect of
> > making the updated column the last column when using * to refer to the
> > table's columns.[/color]
>
> I'll take a look at the system catalogs - thanks for the tip.
>[color=green]
> > It is possible to change the size of varchar data by updating the system
> > catalogs. I don't know the exact details of what to do, but the
> > instructions should be in the archives (multiple times).[/color]
>
> I was hoping to avoid a column drop and recreate.[/color]

The message (one of them anyway) you want is:

http://archives.postgresql.org/pgsql-general/2001-10/msg01208.php

Try and find something more recent though, in case anything important has
changed. Oh, and do take Tom's advice about the backup.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[John Sidney-Woollett]

Re: Resize varchar column

Richard Huxton said:[color=blue]
> The message (one of them anyway) you want is:
>
> http://archives.postgresql.org/pgsql...0/msg01208.php
>
> Try and find something more recent though, in case anything important has
> changed. Oh, and do take Tom's advice about the backup.[/color]

Thanks, that's great.

It would be nice if there was a command available that would do simple
column resizes (upwards), or even data type changes (assuming no implied
data truncation), ie

alter table T alter column X varchar(x);

where x is the new size, and x > old size;

In fact, looking at your URL above (and using the technique explained), it
may be possible to create a function that does this.

I wonder if it works, though, if there is an index on the column...

Anyway, this time, I dropped and recreated the table as this is a test
system. But reinstating all the objects that referenced that table was a
pain!

Thanks again.

John Sidney-Woollett

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings