3

I need to change a column type from varchar(100)[] to text[]. The table has >100M rows and heavy write load, so I'd like to avoid taking an access exclusive lock and rewriting the index. I saw in this question that non-array varchar to text changes manage this, so I was wondering: does the same apply with array columns?

The docs + the mailing list say that this change will be quick if:

the old type is either binary coercible to the new type or an unconstrained domain over the new type)

Is that the case for varchar(100)[] to text[]?

1
  • If anyone has insight into why this doesn't work, would happily accept that answer!
    – Felipe
    Jun 8 at 2:40
5

Adapting the test code from the mailing list shows that the conversion requires a full table rewrite:

test=> set client_min_messages = debug1;
SET

test=> CREATE TABLE t1 (id serial PRIMARY KEY, name character varying(30)[]);
DEBUG:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
DEBUG:  building index "pg_toast_203729867_index" on table "pg_toast_203729867"
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
DEBUG:  building index "t1_pkey" on table "t1"
CREATE TABLE

test=> INSERT INTO t1 (id) SELECT generate_series(1,1000000) i;
INSERT 0 1000000

test=> CREATE INDEX ON t1 (name);
DEBUG:  building index "t1_name_idx" on table "t1"
CREATE INDEX

test=> ALTER TABLE t1 ALTER COLUMN name TYPE text[];
DEBUG:  building index "pg_toast_203730108_index" on table "pg_toast_203730108"
DEBUG:  rewriting table "t1"
DEBUG:  building index "t1_pkey" on table "t1"
DEBUG:  building index "t1_name_idx" on table "t1"

However, if converting to varchar[] instead, there is no rewrite required. Unconstrained varchar and text are functionally the same (and both are non-standard PG extensions)

test=> set client_min_messages = debug1;
SET
test=> CREATE TABLE t1 (id serial PRIMARY KEY, name character varying(30)[]);
DEBUG:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
DEBUG:  building index "pg_toast_49914_index" on table "pg_toast_49914" serially
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
DEBUG:  building index "t1_pkey" on table "t1" serially
DEBUG:  index "t1_pkey" can safely use deduplication
CREATE TABLE
test=> INSERT INTO t1 (id) SELECT generate_series(1,1000000) i;
INSERT 0 1000000
test=> CREATE INDEX ON t1 (name);
DEBUG:  building index "t1_name_idx" on table "t1" with request for 1 parallel worker
DEBUG:  index "t1_name_idx" cannot use deduplication
CREATE INDEX
test=> ALTER TABLE t1 ALTER COLUMN name TYPE varchar[];
ALTER TABLE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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