[INFO] django-redshift-backend cannot provide change column for: PRIMARY, UNIQUE, FK, NOT NULL #96

shimizukawa · 2022-02-18T21:34:13Z

Redshift backend cannot provide the following features

Add, modify, or delete PRIMARY KEY columns
Add, modify, or delete UNIQUE columns
Add, modify, or delete REFERENCES (foreign key) columns
Add NOT NULL columns without DEFAULT
Modify column for: SET DEFAULT, DROP DEFAULT, SET NOT NULL, DROP NOT NULL
RENAME and TYPE change in a transaction.

In Redshift, these are only possible when creating a new table.
The alternative is to recreate the table and insert data into the new table from old table. However, this process cannot be provided as a feature of django-redshift-backend because it is too difficult to perform this process with automatic migration and the impact of failure would be significant.

For more information, please refer to the following page.
https://docs.aws.amazon.com/en_us/redshift/latest/dg/r_ALTER_TABLE.html

NOTE:

Amazon Redshift Maintenance (February 20th - March 21st 2019): You can now use the ALTER TABLE command to increase the size of VARCHAR columns.

shimizukawa · 2022-02-19T02:09:02Z

How to ADD COLUMN NOT NULL without DEFAULT?

You can't.

Alternative:

ALTER TABLE "pony" ADD COLUMN "name" VARCHAR(20) DEFAULT '' NOT NULL; (no way to DROP DEFAULT)
ALTER TABLE "pony" ADD COLUMN "name" VARCHAR(20) NULL; (no way to SET NOT NULL)

dev=# ALTER TABLE "pony" ALTER COLUMN "name" SET NOT NULL;
ERROR:  ALTER COLUMN SET NOT NULL is not supported
dev=# ALTER TABLE "pony" ALTER COLUMN "name" DROP NOT NULL;
ERROR:  ALTER COLUMN DROP NOT NULL is not supported
dev=# ALTER TABLE "pony" ALTER COLUMN "name" SET DEFAULT '';
ERROR:  ALTER COLUMN SET/DROP DEFAULT is not supported
dev=# ALTER TABLE "pony" ALTER COLUMN "name" DROP DEFAULT;
ERROR:  ALTER COLUMN SET/DROP DEFAULT is not supported

shimizukawa · 2022-02-19T20:38:17Z

How to modify NOT NULL to NULL ? (#63)

You can't DROP NOT NULL as:

dev=# ALTER TABLE "pony" ALTER COLUMN "name" DROP NOT NULL;
ERROR:  ALTER COLUMN DROP NOT NULL is not supported

Alternative flow:

dev=# ALTER TABLE "pony" ADD COLUMN "name_tmp" VARCHAR(20) NULL;
ALTER TABLE
dev=# UPDATE "pony" SET "name_tmp"="name";
UPDATE 202
dev=# ALTER TABLE "pony" DROP COLUMN "name";
ALTER TABLE
dev=# ALTER TABLE "pony" RENAME COLUMN "name_tmp" TO "name";
ALTER TABLE

Note that, redshift in transaction can't rename column:

dev=# BEGIN;
BEGIN
dev=# ALTER TABLE "pony" RENAME COLUMN "name_tmp" TO "name";
ERROR:  current transaction is aborted, commands ignored until end of transaction block

shimizukawa · 2022-02-19T22:18:04Z

How to change default?

You can't DROP DEFAULT / SET DEFAULT as:

dev=# ALTER TABLE "pony" ALTER COLUMN "name" SET DEFAULT '';
ERROR:  ALTER COLUMN SET/DROP DEFAULT is not supported
dev=# ALTER TABLE "pony" ALTER COLUMN "name" DROP DEFAULT;
ERROR:  ALTER COLUMN SET/DROP DEFAULT is not supported

Alternative flow:

dev=# ALTER TABLE "pony" ADD COLUMN "name_tmp" VARCHAR(20) DEFAULT 'blink' NOT NULL;
ALTER TABLE
dev=# UPDATE "pony" SET "name_tmp"="name";
UPDATE 202
dev=# ALTER TABLE "pony" DROP COLUMN "name";
ALTER TABLE
dev=# ALTER TABLE "pony" RENAME COLUMN "name_tmp" TO "name";
ALTER TABLE

Note that, redshift in transaction can't rename column:

dev=# BEGIN;
BEGIN
dev=# ALTER TABLE "pony" RENAME COLUMN "name_tmp" TO "name";
ERROR:  current transaction is aborted, commands ignored until end of transaction block

shimizukawa · 2022-02-23T12:39:45Z

How to change type (varchar size) with UNIQUE constraint?

You can't change type for UNIQUE.

dev=# ALTER TABLE pony ADD CONSTRAINT pony_name_unique UNIQUE (name);
ALTER TABLE
dev=# ALTER TABLE pony ALTER COLUMN name TYPE varchar(30);
ERROR:  cannot alter type of a column which is primary or foreign key or unique

However, once drop constraints of the column, you can.

dev=# ALTER TABLE pony DROP CONSTRAINT pony_name_unique;
ALTER TABLE
dev=# ALTER TABLE pony ALTER COLUMN name TYPE varchar(30);
ALTER TABLE
dev=# ALTER TABLE pony ADD CONSTRAINT pony_name_unique UNIQUE (name);
ALTER TABLE

shimizukawa · 2022-02-23T12:57:31Z

How to change type (varchar size) with PRIMARY KEY constraint?

You can't change type for PRIMARY KEY.

dev=# CREATE TABLE pony (name varchar(20) PRIMARY KEY);
CREATE TABLE
dev=# \d pony;
                       Table "public.pony"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 name   | character varying(20) |           | not null | 
Indexes:
    "pony_pkey" PRIMARY KEY, btree (name)

dev=# ALTER TABLE pony ALTER COLUMN name TYPE varchar(30);
ERROR:  cannot alter type of a column which is primary or foreign key or unique

However, once drop constraints of the column, you can.

dev=# ALTER TABLE pony DROP CONSTRAINT pony_pkey;
ALTER TABLE
dev=# ALTER TABLE pony ALTER COLUMN name TYPE varchar(30);
ALTER TABLE
dev=# ALTER TABLE pony ADD PRIMARY KEY (name);
ALTER TABLE
dev=# \d pony
                       Table "public.pony"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 name   | character varying(30) |           | not null | 
Indexes:
    "pony_pkey" PRIMARY KEY, btree (name)

shimizukawa · 2022-02-23T13:06:04Z

How to change type (varchar size) with FOREIGN KEY constraint?
You can change size once drop FOREIGN KEY.

dev=# ALTER TABLE pony ADD COLUMN name_ref varchar(30);
ALTER TABLE
dev=# ALTER TABLE pony ADD FOREIGN KEY (name_ref) REFERENCES pony (name);
ALTER TABLE
dev=# \d pony
                        Table "public.pony"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 name     | character varying(30) |           | not null | 
 name_ref | character varying(30) |           |          | 
Indexes:
    "pony_pkey" PRIMARY KEY, btree (name)
Foreign-key constraints:
    "pony_name_ref_fkey" FOREIGN KEY (name_ref) REFERENCES pony(name)
Referenced by:
    TABLE "pony" CONSTRAINT "pony_name_ref_fkey" FOREIGN KEY (name_ref) REFERENCES pony(name)

dev=# ALTER TABLE pony ALTER COLUMN name_ref TYPE varchar(40);
ERROR:  cannot alter type of a column which is primary or foreign key or unique

shimizukawa · 2022-02-25T21:31:09Z

RENAME and TYPE change in a transaction is not allowed.

dev=# alter table pony add column ba  varchar(10) NULL;
ALTER TABLE

dev=# begin;
BEGIN
dev=# alter table pony alter column ba type varchar(20);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
dev=# alter table pony rename column ba to bar;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
dev=# end;
ROLLBACK

dev=# alter table pony alter column ba type varchar(20);
ALTER TABLE
dev=# alter table pony rename column ba to bar;
ALTER TABLE

shimizukawa · 2024-07-16T08:13:34Z

How to change type (varchar size) with default value?

Redshift says:

=# CREATE TABLE foo (id INTEGER);
=# ALTER TABLE "foo" ADD COLUMN "name" varchar(10) DEFAULT "";
=# ALTER TABLE "foo" ALTER COLUMN "name" TYPE varchar(20);
=# ALTER TABLE "foo" ALTER COLUMN "name" TYPE varchar(10);
cannot decrease a VARCHAR column size with default value
=# ALTER TABLE "foo" ALTER COLUMN "name" DROP DEFAULT;
ALTER COLUMN SET/DROP DEFAULT is not supported

However, we can migrate into new column.

=# CREATE TABLE foo (id INTEGER);
=# ALTER TABLE "foo" ADD COLUMN "name" varchar(20) DEFAULT '';
=# ALTER TABLE "foo" ADD COLUMN "name_new" varchar(10) DEFAULT '';
=# UPDATE "foo" SET name_new = name;
=# ALTER TABLE "foo" DROP COLUMN name;
=# ALTER TABLE "foo" RENAME COLUMN name_new TO name;

shimizukawa · 2024-07-16T23:24:10Z

How to change type (varchar size) with unique constraint?

Redshift says:

=# CREATE TABLE foo (id INTEGER);
=# ALTER TABLE "foo" ADD COLUMN "name" varchar(20) UNIQUE;
ERROR: ALTER TABLE ADD COLUMN does not support columns defined with UNIQUE or PRIMARY KEY constraints
=# ALTER TABLE "foo" ADD COLUMN "name" varchar(20);
=# ALTER TABLE "foo" ADD CONSTRAINT "constraint1" UNIQUE("name");
=# ALTER TABLE "foo" ALTER COLUMN "name" TYPE varchar(10);
cannot alter type of a column which is primary or foreign key or unique

However, we can migrate into new column.

=# CREATE TABLE foo (id INTEGER);
=# ALTER TABLE "foo" ADD COLUMN "name" varchar(20);
=# ALTER TABLE "foo" ADD CONSTRAINT "constraint1" UNIQUE("name");
=# ALTER TABLE "foo" DROP CONSTRAINT "constraint1";
=# ALTER TABLE "foo" ALTER COLUMN "name" TYPE varchar(10);
=# ALTER TABLE "foo" ADD CONSTRAINT "constraint1" UNIQUE("name");

shimizukawa added the question label Feb 18, 2022

shimizukawa self-assigned this Feb 18, 2022

shimizukawa mentioned this issue Feb 26, 2022

bug fixes and support several features for django migrations #97

Merged

shimizukawa mentioned this issue Jul 16, 2024

fix bugs for testing with Redshift (include a new feature; add columun with UNIQUE) #134

Merged

3 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[INFO] django-redshift-backend cannot provide change column for: PRIMARY, UNIQUE, FK, NOT NULL #96

[INFO] django-redshift-backend cannot provide change column for: PRIMARY, UNIQUE, FK, NOT NULL #96

shimizukawa commented Feb 18, 2022 •

edited

Loading

shimizukawa commented Feb 19, 2022 •

edited

Loading

shimizukawa commented Feb 19, 2022 •

edited

Loading

shimizukawa commented Feb 19, 2022

shimizukawa commented Feb 23, 2022 •

edited

Loading

shimizukawa commented Feb 23, 2022 •

edited

Loading

shimizukawa commented Feb 23, 2022

shimizukawa commented Feb 25, 2022

shimizukawa commented Jul 16, 2024

shimizukawa commented Jul 16, 2024 •

edited

Loading

[INFO] django-redshift-backend cannot provide change column for: PRIMARY, UNIQUE, FK, NOT NULL #96

[INFO] django-redshift-backend cannot provide change column for: PRIMARY, UNIQUE, FK, NOT NULL #96

Comments

shimizukawa commented Feb 18, 2022 • edited Loading

shimizukawa commented Feb 19, 2022 • edited Loading

shimizukawa commented Feb 19, 2022 • edited Loading

shimizukawa commented Feb 19, 2022

shimizukawa commented Feb 23, 2022 • edited Loading

shimizukawa commented Feb 23, 2022 • edited Loading

shimizukawa commented Feb 23, 2022

shimizukawa commented Feb 25, 2022

shimizukawa commented Jul 16, 2024

shimizukawa commented Jul 16, 2024 • edited Loading

shimizukawa commented Feb 18, 2022 •

edited

Loading

shimizukawa commented Feb 19, 2022 •

edited

Loading

shimizukawa commented Feb 19, 2022 •

edited

Loading

shimizukawa commented Feb 23, 2022 •

edited

Loading

shimizukawa commented Feb 23, 2022 •

edited

Loading

shimizukawa commented Jul 16, 2024 •

edited

Loading