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

[shimizukawa]

Redshift backend cannot provide the following features

1. Add, modify, or delete PRIMARY KEY columns
2. Add, modify, or delete UNIQUE columns
3. Add, modify, or delete REFERENCES (foreign key) columns
4. Add NOT NULL columns without DEFAULT
5. Modify column for: SET DEFAULT, DROP DEFAULT, SET NOT NULL, DROP NOT NULL
6. 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]

How to ADD COLUMN NOT NULL without DEFAULT?

You can't.

Alternative:

1. ALTER TABLE "pony" ADD COLUMN "name" VARCHAR(20) DEFAULT '' NOT NULL; (no way to DROP DEFAULT)
2. 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]

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]

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]

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]

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]

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]

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]

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]

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