HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Postgres varchar(100)[] -> text[] column conversion

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
conversionpostgrescolumntextvarchar100

Problem

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[]?

Solution

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

Code Snippets

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"
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

Context

StackExchange Database Administrators Q#293904, answer score: 6

Revisions (0)

No revisions yet.