Convert postgres varchar[] to text[] without downtime

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Method 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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply