patternsqlMinor
Postgres varchar(100)[] -> text[] column conversion
Viewed 0 times
conversionpostgrescolumntextvarchar100
Problem
I need to change a column type from
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[]. 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:
However, if converting to
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 TABLECode 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 TABLEContext
StackExchange Database Administrators Q#293904, answer score: 6
Revisions (0)
No revisions yet.