Postgresql - change the size of a varchar column to lower length

Verify before altering

Prior to executing the ALTER TABLE command:

• Inspect your data with a query to detect entries surpassing the new limit.
• Opt for the substr function to trim down prolix entries.

SELECT my_column, LENGTH(my_column) FROM my_table WHERE LENGTH(my_column) > 50; /*Kid, you're too tall for this ride.*/


Minimal downtime

In high-availability settings, you can suppress downtime via:

• Transactions encapsulating the ALTER TABLE statement.
• Implementing batches on ample tables to regulate memory usage.
• Append a new column, replicate the data gradually in smaller batches, then interchange columns.

Start a transaction:

BEGIN;
ALTER TABLE my_table ALTER COLUMN my_column TYPE varchar(50); /*Hang on tight! VARCHAR's going on a rollercoaster ride. 🎢*/
COMMIT;


Applying constraints

In PostgreSQL, VARCHAR and TEXT are handled similarly, optimizing storage dynamically. TEXT with tangible length constraint future-proofs the column:

ALTER TABLE my_table ALTER COLUMN my_column TYPE text CHECK (char_length(my_column) <= 50); /*I know TEXT looks big but it’s just fluffy. Constraint makes it VARCHAR-like! 🐻*/


Safety first

Before trimming a column, make sure to:

• Preserve data integrity: All pre-existing rows should fit within the renewed column constraint.
• Eliminating excess characters prior to the operation can prevent unintentional data loss.
• Test any schema changes on your development database before injecting them into production.

This way, you lower the risk of unplanned setbacks during your ALTER TABLE operations.

Future-proofing and adaptation

When it comes to large tables or critical systems, any schema changes need careful planning and execution:

• Anticipate future trends and changes. Will your requirement for column size differ in future?
• Application layer validation. Revalidate your client-facing code to adapt it to new column size.
• Preparing a migration script ensures smooth data transition when altering the schema.