Postgres missing support for "ALTER TABLE ... ALTER COLUMN ... TYPE ..."

[fydo23]

I was looking at https://github.com/db-migrate/node-db-migrate/blob/master/lib/driver/pg.js#L246 and was sad to see that it's impossible to alter an existing column's types in postgres.

I'm thinking, check if type is in columnSpec to trigger:

 var sql = util.format('ALTER TABLE "%s" ALTER COLUMN "%s" TYPE %s', tableName, columnName, columnType);

Fy.

[fydo23]

I built off of my last comment and modified the function setNotNull() to the following:

function setNotNull() {
    var using = columnSpec.using != undefined ? 
      columnSpec.using : util.format("USING %s::%s", columnName, this.mapDataType(columnSpec.type))
    var alter_column = columnSpec.type != undefined ? 
      util.format('ALTER COLUMN "%s" TYPE %s %s, ', columnName, this.mapDataType(columnSpec.type), using) : ""

    var setOrDrop = columnSpec.notNull === true ? 'SET' : 'DROP';
    alter_column = util.format('%sALTER COLUMN "%s" %s NOT NULL', alter_column, columnName, setOrDrop);

    var sql = util.format('ALTER TABLE %s %s', tableName, alter_column);

    this.runSql(sql, setUnique.bind(this));
  }

This seems to work for me. However, this adds the extra complexity of supporting an optional using param in the columnSpec for custom data conversion (ie. bytea to int).

[wzrdtales]

Actually there was pull request about this
#209

Unfortunately an incomplete one, not only it is lacking tests, it also failed with the existing ones. Currently this is not in scope of v0.10.x and earliest would be in v0.11.x. But you're free to open a merge request handling tests, this is always appreciated :)

[Dynom]

I would greatly appreciate a more thorough overview of what's supported and what isn't supported per database.

I was scratching my head about why the following

exports.up = function(db, callback) {
  db.changeColumn(
    'foo', 
    'bar',
    {
      type: 'int'
    },
    callback
  );
};

Produced the following ridiculous SQL statements:

[INFO] dry run
BEGIN;
ALTER TABLE "foo" ALTER COLUMN "bar" DROP NOT NULL
ALTER TABLE "foo" ALTER COLUMN "bar" DROP DEFAULT
..

Until I found this issue.

[wzrdtales]

@Dynom Yes you're right, I'm working on the documentation and informations like this will also be documented there. Thats part of the goals which are set for the v0.10.x release.

Btw. the newest docs are always on readthedocs now, I will clean up the readme soon.

https://readthedocs.org/projects/db-migrate/?badge=latest

[Dynom]

Thanks for the update @wzrdtales I'll make sure to readthedocs more often (-;

[wzrdtales]

closed as it was already merged