There might come a time where you’ll want to shrink the size of a VARCHAR field in a table.
Example:
yugabyte=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
c1 | integer | | not null |
c2 | character varying(10000) | | |
Indexes:
"t_pkey" PRIMARY KEY, lsm (c1 HASH)
yugabyte=# SELECT * FROM t ORDER BY c1;
c1 | c2
----+-----
1 | ABC
2 | DEF
3 | GHI
(3 rows)
In the above table named T, the C2 column was declared as a VARCHAR(10000). But as part of my application business logic, data in this column should never be greater than 3 characters. I want to enforce that business logic in my database, so I need to reduce the size of column C2 to VARCHAR(3).
yugabyte=# ALTER TABLE t ALTER COLUMN c2 TYPE VARCHAR(3);
ERROR: This ALTER TABLE command is not yet supported.
Uh oh. It’s not going to that easy.
To shrink the column size, we’re going to have to perform the following steps:
yugabyte=# ALTER TABLE t ADD COLUMN c2_temp VARCHAR(3);
ALTER TABLE
yugabyte=# UPDATE t SET c2_temp = c2;
UPDATE 3
yugabyte=# ALTER TABLE t DROP COLUMN c2;
ALTER TABLE
yugabyte=# ALTER TABLE t RENAME COLUMN c2_temp TO c2;
ALTER TABLE
Now when we describe the table and query its data, we see that the data type of the C2 column is now the desired size of VARCHAR(3).
yugabyte=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+----------------------+-----------+----------+---------
c1 | integer | | not null |
c2 | character varying(3) | | |
Indexes:
"t_pkey" PRIMARY KEY, lsm (c1 HASH)
yugabyte=# SELECT * FROM t;
c1 | c2
----+-----
1 | ABC
2 | DEF
3 | GHI
(3 rows)
Note: Take care when you perform the above steps. That is, try to run them during off hours when the table is not in use.
Have Fun!