Convert postgres varchar[] to text[] without downtime

Question

As I discovered in researching this question, converting a column from varchar(100)[] to text[] requires a table rewrite and rebuilding indexes.

Is there a way to manage this conversion without the locking and rewriting through some catalog trickery like that used in this question? Particularly interested in version 10 if possible

Answer 1

This should work with a simple catalog modification, since text and varchar are the same:

UPDATE pg_attribute
SET atttypid = 'text[]'::regtype, atttypmod = -1
WHERE attrelid = 'mytable'::regclass AND attname = 'mycolumn';

But, as you know, catalog modifications are unsupported, and if I forgot something and it breaks, you might be in trouble.

Answer 2

RDS does not support catalog modifications, so have to do this manually after all :( Here are the steps to follow, adapted from this post. To change orig_column from varchar(100)[] not null to text[] not null:

1. Upgrade to version 12 at least
2. Create a text[] column called new_column, which allows nulls
3. Create BEFORE UPDATE and BEFORE INSERT triggers which copy orig_column into new_column and orig_column into old_column (see code at bottom)
4. Update existing to rows to fill in new_column with orig_column
5. Create copies of any index using orig_column, but using new_column instead, concurrently
6. Add a CHECK constraint (new_column IS NOT NULL), deferring validation
7. Validate constraint
8. Update new_column to be NOT NULL
9. Drop the CHECK constraint
10. In a trx with ACCESS EXCLUSIVE lock on the table, rename the columns to old_column and orig_column
11. Set old_column to be nullable
12. Drop the triggers
13. Drop indexes referencing old_column concurrently
14. Drop old_column
15. (optional) Rename indexes to match previous naming convention

---

CREATE OR REPLACE FUNCTION some_function_name() RETURNS trigger AS $$
BEGIN
  IF (to_jsonb(NEW) ? 'new_column' AND NEW.new_column IS NULL) THEN
    NEW.new_column = NEW.orig_column;
  END IF;
  IF (to_jsonb(NEW) ? 'old_column' AND NEW.old_column IS NULL) THEN
    NEW.old_column = NEW.orig_column;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;