{"action":"created","issue":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/9903","labels_url":"https://api.github.com/repos/yiisoft/yii2/issues/9903/labels{/name}","comments_url":"https://api.github.com/repos/yiisoft/yii2/issues/9903/comments","events_url":"https://api.github.com/repos/yiisoft/yii2/issues/9903/events","html_url":"https://github.com/yiisoft/yii2/issues/9903","id":111151587,"number":9903,"title":"Migration. Alter table, set default and get syntax error. postgresql","user":{"login":"johnluxor","id":3223988,"avatar_url":"https://avatars.githubusercontent.com/u/3223988?v=3","gravatar_id":"","url":"https://api.github.com/users/johnluxor","html_url":"https://github.com/johnluxor","followers_url":"https://api.github.com/users/johnluxor/followers","following_url":"https://api.github.com/users/johnluxor/following{/other_user}","gists_url":"https://api.github.com/users/johnluxor/gists{/gist_id}","starred_url":"https://api.github.com/users/johnluxor/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/johnluxor/subscriptions","organizations_url":"https://api.github.com/users/johnluxor/orgs","repos_url":"https://api.github.com/users/johnluxor/repos","events_url":"https://api.github.com/users/johnluxor/events{/privacy}","received_events_url":"https://api.github.com/users/johnluxor/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/yiisoft/yii2/labels/feature:db","name":"feature:db","color":"bfd4f2"},{"url":"https://api.github.com/repos/yiisoft/yii2/labels/type:bug","name":"type:bug","color":"e102d8"}],"state":"open","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/yiisoft/yii2/milestones/14","html_url":"https://github.com/yiisoft/yii2/milestones/2.0.7","labels_url":"https://api.github.com/repos/yiisoft/yii2/milestones/14/labels","id":1107394,"number":14,"title":"2.0.7","description":"","creator":{"login":"cebe","id":189796,"avatar_url":"https://avatars.githubusercontent.com/u/189796?v=3","gravatar_id":"","url":"https://api.github.com/users/cebe","html_url":"https://github.com/cebe","followers_url":"https://api.github.com/users/cebe/followers","following_url":"https://api.github.com/users/cebe/following{/other_user}","gists_url":"https://api.github.com/users/cebe/gists{/gist_id}","starred_url":"https://api.github.com/users/cebe/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cebe/subscriptions","organizations_url":"https://api.github.com/users/cebe/orgs","repos_url":"https://api.github.com/users/cebe/repos","events_url":"https://api.github.com/users/cebe/events{/privacy}","received_events_url":"https://api.github.com/users/cebe/received_events","type":"User","site_admin":false},"open_issues":102,"closed_issues":82,"state":"open","created_at":"2015-05-12T19:21:21Z","updated_at":"2015-10-13T10:48:16Z","due_on":null,"closed_at":null},"comments":5,"created_at":"2015-10-13T10:25:40Z","updated_at":"2015-10-13T12:20:24Z","closed_at":null,"body":"Hello.\r\nI have migration. Yii 2.0.6. Postgres 9.4\r\n```\r\npublic function up()\r\n{\r\n        $this->alterColumn('client', 'banned', $this->smallInteger(1)->defaultValue(0));\r\n}\r\n```\r\n\r\nAnd got error:\r\n\r\n> alter column banned in table client to smallint(1) DEFAULT 0 ...Exception 'yii\\db\\Exception' with message 'SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near \"DEFAULT\"\r\nLINE 1: ...TABLE \"client\" ALTER COLUMN \"banned\" TYPE smallint DEFAULT 0\r\n                                                              ^\r\nThe SQL being executed was: ALTER TABLE \"client\" ALTER COLUMN \"banned\" TYPE smallint DEFAULT 0'\r\n\r\n```\r\nin /Users/yevgeniy/sites/vendor/yiisoft/yii2/db/Schema.php:628\r\n\r\nError Info:\r\nArray\r\n(\r\n    [0] => 42601\r\n    [1] => 7\r\n    [2] => ERROR:  syntax error at or near \"DEFAULT\"\r\nLINE 1: ...TABLE \"client\" ALTER COLUMN \"banned\" TYPE smallint DEFAULT 0\r\n                                                              ^\r\n)\r\n\r\n```"},"comment":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/comments/147699029","html_url":"https://github.com/yiisoft/yii2/issues/9903#issuecomment-147699029","issue_url":"https://api.github.com/repos/yiisoft/yii2/issues/9903","id":147699029,"user":{"login":"SilverFire","id":4499203,"avatar_url":"https://avatars.githubusercontent.com/u/4499203?v=3","gravatar_id":"","url":"https://api.github.com/users/SilverFire","html_url":"https://github.com/SilverFire","followers_url":"https://api.github.com/users/SilverFire/followers","following_url":"https://api.github.com/users/SilverFire/following{/other_user}","gists_url":"https://api.github.com/users/SilverFire/gists{/gist_id}","starred_url":"https://api.github.com/users/SilverFire/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SilverFire/subscriptions","organizations_url":"https://api.github.com/users/SilverFire/orgs","repos_url":"https://api.github.com/users/SilverFire/repos","events_url":"https://api.github.com/users/SilverFire/events{/privacy}","received_events_url":"https://api.github.com/users/SilverFire/received_events","type":"User","site_admin":false},"created_at":"2015-10-13T12:20:24Z","updated_at":"2015-10-13T12:20:24Z","body":"And we can not just change `DEFAULT` to `SET DEFAULT` in `buildDefaultString`, because for `CREATE TABLE` query we have to use `DEFAULT`, and for `ALTER TABLE` - `SET DEFAULT`.\r\n\r\n```sql\r\npg=> create temporary table test (id integer not null, test text SET DEFAULT 'oh no');\r\nERROR:  syntax error at or near \"SET\"\r\nLINE 1: ...porary table test (id integer not null, test text SET DEFAUL...\r\n                                                             ^\r\nTime: 0.261 ms\r\npg=> create temporary table test (id integer not null, test text DEFAULT 'oh yeah');\r\nCREATE TABLE\r\nTime: 7.313 ms\r\n\r\n```"}}