Migration. Alter table, set as NULL, PostgreSQL.

[talovicnedim]

Hello,

In user table I have two columns: first_name and last_name and currently NOT NULL is set to Yes - I'm not allowed to create a new user without first name and last name.

This is how the migration looks:

$this->alterColumn('user', 'first_name', $this->string()->null());
$this->alterColumn('user', 'last_name', $this->string()->null()); 

and this is the error:

  > alter column first_name in table user to string NULL DEFAULT NULL ...Error: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "NULL"
LINE 1: ...user" ALTER COLUMN "first_name" TYPE varchar(255) NULL DEFAU...
                                                             ^
The SQL being executed was: ALTER TABLE "user" ALTER COLUMN "first_name" TYPE varchar(255) NULL DEFAULT NULL

Of course, I can change the column manually:

ALTER TABLE "user" ALTER COLUMN "first_name" SET DEFAULT NULL;

but I'm interested why this doesn't work.

Nedim

[lalviarez]

Hi.
I get the same error on similar migration, i want alter some columns from NOT NULL to NULL
The DBMS is PostgreSQL 9.5

Yii Migration Tool (based on Yii v2.0.10)

Total 1 new migration to be applied:
    m161028_032950_cambiando_a_null_campos_de_padre_y_madre_en_estudio_socio_economico

Apply the above migration? (yes|no) [no]:yes
*** applying m161028_032950_cambiando_a_null_campos_de_padre_y_madre_en_estudio_socio_economico
    > alter column telefono_fijo_solicitante in table {{%estudio_socio_economico}} to string(11) NULL DEFAULT NULL ...Exception: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "NULL"
LINE 1: ...LUMN "telefono_fijo_solicitante" TYPE varchar(11) NULL DEFAU...
                                                             ^
The SQL being executed was: ALTER TABLE "estudio_socio_economico" ALTER COLUMN "telefono_fijo_solicitante" TYPE varchar(11) NULL DEFAULT NULL (/home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/db/Schema.php:631)
#0 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/db/Command.php(854): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE "es...')
#1 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/db/Migration.php(365): yii\db\Command->execute()
#2 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/console/migrations/m161028_032950_cambiando_a_null_campos_de_padre_y_madre_en_estudio_socio_economico.php(9): yii\db\Migration->alterColumn('{{%estudio_soci...', 'telefono_fijo_s...', Object(yii\db\ColumnSchemaBuilder))
#3 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/db/Migration.php(94): m161028_032950_cambiando_a_null_campos_de_padre_y_madre_en_estudio_socio_economico->safeUp()
#4 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(651): yii\db\Migration->up()
#5 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(163): yii\console\controllers\BaseMigrateController->migrateUp('m161028_032950_...')
#6 [internal function]: yii\console\controllers\BaseMigrateController->actionUp(0)
#7 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)
#8 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/base/Controller.php(154): yii\base\InlineAction->runWithParams(Array)
#9 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/console/Controller.php(128): yii\base\Controller->runAction('', Array)
#10 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/base/Module.php(454): yii\console\Controller->runAction('', Array)
#11 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate', Array)
#12 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/console/Application.php(147): yii\console\Application->runAction('migrate', Array)
#13 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/vendor/yiisoft/yii2/base/Application.php(375): yii\console\Application->handleRequest(Object(yii\console\Request))
#14 /home/ubuntu/Proyectos/Fundacite/Fortalento-Inscripciones/yii(27): yii\base\Application->run()
#15 {main}
*** failed to apply m161028_032950_cambiando_a_null_campos_de_padre_y_madre_en_estudio_socio_economico (time: 0.025s)


0 from 1 migrations were applied.

Migration failed. The rest of the migrations are canceled.

Saludos.

[lalviarez]

Hi.
This worked for me:

$this->alterColumn('{{%estudio_socio_economico}}', 'telefono_fijo_solicitante', 'DROP NOT NULL'); //for drop not null
$this->alterColumn('{{%estudio_socio_economico}}', 'telefono_fijo_solicitante', 'SET DEFAULT NULL'); //for set default null value

The problem is that Yii build the same code for alter column and create table SQL commands.
For example, this method generate correct SQL on CREATE TABLE:

$this->boolean()->defaultValue(true) // Generate: boolean DEFAULT true

The same SQL is generated for ALTER COLUMN, this is wrong. The correct code for ALTER COLUMN is SET DEFAULT true.

Saludos.

[SilverFire]

Duplicates #9903

[thattejada]

@lalviarez solution worked for me, thanks!