Change column type VARCHAR to TEXT in PostgreSQL without lock table

Viewed 6313

I have a "Parent Table" and partition table by year with a lot column and now I need change a column VARCHAR(32) to TEXT because we need more length flexibility.

So I will alter the parent them will also change all partition.

But the table have 2 unique index with this column and also 1 index.

This query lock the table:

ALTER TABLE my_schema.my_table
ALTER COLUMN column_need_change TYPE VARCHAR(64) USING 
column_need_change :: VARCHAR(64);

Also this one :

ALTER TABLE my_schema.my_table
ALTER COLUMN column_need_change TYPE TEXT USING column_need_change :: TEXT;

I see this solution :

UPDATE pg_attribute SET atttypmod = 64+4
WHERE attrelid = 'my_schema.my_table'::regclass
AND attname = 'column_need_change ';

But I dislike this solution.

How can change VARCHAR(32) type to TEXT without lock table, I need continue to push some data in table between the update.

My Postgresql version : 9.6

EDIT :

This is the solution I ended up taking:

ALTER TABLE my_schema.my_table
ALTER COLUMN column_need_change TYPE TEXT USING column_need_change :: TEXT;

The query lock my table between : 1m 52s 548ms for 2.6 millions rows but it's fine.

1 Answers
Related