Can I change a String column's size and preserve the data?

["Riesland, Zack" <Za...@sensus.com>]

I have a column on a table that is set to varchar(40).

I need to increase that 40, but I don't want to lose any of the data in the table.

The only suggestions I've seen online involve dropping the column and re-creating it, or creating a new table. But I would like to preserve the name of this table.

If I make a copy table, can I rename it after I drop the original?

What is the best way to accomplish this?

Thanks!

Zack Riesland | Data Analytics
639 Davis Drive | Morrisville, NC 27560 USA
Zack.Riesland@sensus.com<ma...@sensus.com> | www.sensus.com<http://www.sensus.com/>
Skype: zack_riesland

[Sensus]<http://www.sensus.com/>

Re: Can I change a String column's size and preserve the data?

[James Taylor <ja...@apache.org>]

Hi Zack,
Phoenix doesn't support an ALTER TABLE statement that allows this, but you
can modify the SYSTEM.CATALOG directly with an UPSERT VALUES call if you're
careful. Make sure to:
- Create a snapshot of the SYSTEM.CATALOG just in case something goes wrong
- Ensure that other modifications aren't occurring to the SYSTEM.CATALOG
table at the same time.
- Bounce your cluster afterwards as Phoenix caches metadata on the region
server hosting the SYSTEM.CATALOG table.
- Restart your client as Phoenix caches metadata on the client as well.

The statement you'd want to run would be something like this (followed by a
commit):

UPSERT INTO SYSTEM.CATALOG (
    TENANT_ID,
    TABLE_SCHEM,
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_FAMILY,
    COLUMN_SIZE
VALUES (
    null,
    "YOUR_SCHEMA_NAME",
    "YOUR_TABLE_NAME",
    "YOUR_COLUMN_NAME",
    "YOUR_COLUMN_FAMILY_NAME", // or "0" if you didn't specify one
    200); // Or whatever you want to increase the max size to be

Thanks,
James

On Thu, Oct 6, 2016 at 8:14 AM, Riesland, Zack <Za...@sensus.com>
wrote:

> I have a column on a table that is set to varchar(40).
>
>
>
> I need to increase that 40, but I don’t want to lose any of the data in
> the table.
>
>
>
> The only suggestions I’ve seen online involve dropping the column and
> re-creating it, or creating a new table. But I would like to preserve the
> name of this table.
>
>
>
> If I make a copy table, can I rename it after I drop the original?
>
>
>
> What is the best way to accomplish this?
>
>
>
> Thanks!
>
>
>
> *Zack Riesland | Data Analytics*
>
> 639 Davis Drive | Morrisville, NC 27560 USA
>
> *Zack.Riesland@sensus.com <Za...@sensus.com> | **www.sensus.com
> <http://www.sensus.com/>*
>
> Skype: zack_riesland
>
>
> [image: Sensus] <http://www.sensus.com/>
>
>
>