2

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

2 Answers 2

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.

1
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.