Postgres migration error - error: type already exists

Question

exports.up = async (knex) => {
  await knex.raw(`
    ALTER TABLE accounts.login RENAME COLUMN type TO old_type;
    CREATE TYPE newest_login_type AS ENUM('hidden', 'github', 'twitter', 'google');
    ALTER TABLE accounts.login ADD COLUMN type newest_login_type;
  `);

  const types = await knex('accounts.login').select('old_type', 'user_id');

  await Promise.all(types.map(async ({ user_id, old_type }) => {
    return knex('accounts.login').where('user_id', user_id).update({ type: old_type });
  }));

  await knex.raw(`
    ALTER TABLE accounts.login DROP COLUMN old_type;
  `);
};

exports.down = async (knex) => {
  await knex.raw(`
    ALTER TYPE newest_login_type RENAME TO old_login_type;
    CREATE TYPE newest_login_type AS ENUM('hidden', 'github', 'twitter');
    ALTER TABLE accounts.login ALTER COLUMN type TYPE newest_login_type USING type::text::newest_login_type;
    DROP TYPE old_login_type;
  `);
};

When I migrate it works, then rollback works, then migrate again throws the following error:

error: type "newest_login_type" already exists

What am I doing wrong here? Thanks

Mikael Lepistö · Accepted Answer · 2019-06-06 17:03:05Z

1

I'm surprised how that code even can work, but your problem is that up script expects that newest_login_type doesn't exist and your down script does not remove that type, but only renames and recreate it.

So:

running up, newest_login_type is created
running down newest_login_type is modified
running up again fails, because newest_login_type already exist

ps. That shouldn't work at all because you should not pass multiple SQL statements to single knex.raw call. That is just not supported by many DB drivers including pg driver that is used with knex's postgresql dialect.

edited Jun 6, 2019 at 17:03

answered Jun 6, 2019 at 16:58

Mikael Lepistö

19.8k4 gold badges73 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zack Shapiro Over a year ago

Multiple statements in a single knex.raw call are all over our migrations code, so it definitely works :)

Mikael Lepistö Over a year ago

Interesting. Which knex / pg versions are you using? have you set some special connection parameters for it? (I've been knex maintainer for many years and im sure that has not been always possible)

Zack Shapiro Over a year ago

I can dig into this later to find out. I don't know off the top of my head. I fixed my problem, my down needed to create a type for the enum equivalent to the status quo type. Up could create the new one. Thanks Mikael

Mikael Lepistö Over a year ago

Found it :) github.com/brianc/node-postgres/issues/1190 (works as long as parameter bindings are not used... like with mysql it can be done too)

Collectives™ on Stack Overflow

Postgres migration error - error: type already exists

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related