Debian Question posted in
The oficial documentation can be found here.

Knex and PostgreSQL: How to drop an unique index? – Debian

I have an app using knex for migration. Earlier someone made an unique index on one column using knex.raw like this:

const createProgrammeTable = knex => knex.raw(`
  CREATE TABLE programme (
    id             serial PRIMARY KEY,
    date           date NOT NULL,
    published      date,
    description    text
  );
  CREATE UNIQUE INDEX programme_date_index ON programme(date);
`)

When printing out the db using psql I get this info:

server=# d programme;
                              Table "public.programme"
  Column   |  Type   | Collation | Nullable |                Default
-----------+---------+-----------+----------+---------------------------------------
 id        | integer |           | not null | nextval('programme_id_seq'::regclass)
 date      | date    |           | not null |
 published | boolean |           | not null |
Indexes:
    "programme_pkey" PRIMARY KEY, btree (id)
    "programme_date_index" UNIQUE, btree (date)

Now I want to remove the unique constraint but I’m not sure how. I’ve read that I can do this:

ALTER TABLE programme DROP INDEX programme_date_index;

But doing that using knex.raw gives this error:

migration failed with error: ALTER TABLE programme DROP INDEX programme_date_index - syntax error at or near "programme_date_index"
error: ALTER TABLE programme DROP INDEX programme_date_index - syntax error at or near "programme_date_index"
    at Connection.parseE (/Users/my-path/node_modules/pg/lib/connection.js:555:11)
    at Connection.parseMessage (/Users/my-path/node_modules/pg/lib/connection.js:380:19)
    at Socket.<anonymous> (/Users/my-path/node_modules/pg/lib/connection.js:120:22)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
error Command failed with exit code 1.

I’ve also tried using dropUnique like this:

exports.up = function(knex, Promise) {
  return knex.schema.alterTable("programme", function(t) {
    t.dropUnique("date", "programme_date_index")
  })
}

which gives the following error:

migration failed with error: alter table "programme" drop constraint "programme_date_index" - constraint "programme_date_index" of relation "programme" does not exist

Versions

Knex CLI version: 0.20.15 /
Knex Local version: 0.20.15

server=# select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Can someone please help me/push me in the right direction?

Tags:

3

Answers


  1. 0

    using Knex with Posgresql, I’ve just resolved a similar problem this way:

    import * as Knex from "knex";

export async function up(knex: Knex): Promise<void> {
    return await knex.schema
        .withSchema('public')
        .table('elements', (table) => {
            table.dropUnique(null, 'elements_name_unique');
        });
}

export async function down(knex: Knex): Promise<void> {
    return knex.schema.table('elements', (table) => {
        table.unique(['name']);
    });
}

    Typescript constraint made it weird and I had to cheat a little with parameters.

    I use Knex CLI version: 0.21.17 / Knex Local version: 0.21.18

    Login or Signup to reply.
  2. 0 
    export async function down(db: Knex): Promise<void> {
    db.schema.alterTable('tablename', function (t) {
        t.dropUnique([], 'indexname')
    })
}

    Incase you’re using typescript to remove index.

    Login or Signup to reply.
  3. 0

    Package

    "knex": "^0.95.8",

    This is what I normally do:

    exports.up = function (knex) {
  return knex.schema.alterTable('users', function (table) {
    table.dropUnique('user_id');
  });
};
exports.down = function (knex) {
  return knex.schema.alterTable('users', function (table) {
    table.unique('user_id');
  });
};

    But we ran into this interesting issue where we altered the column name before and renamed the column. For some reason the database still had a constraint with old column name as unique. So we had to remove the old constraint first. Something to keep in mind!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search