Change Index in Postgres

Question

I have been facing a problem on my django application while trying to add an value to a model. It seems that there is a constraint in the DB that should not be there, according to models.py. The error I get is:

IntegrityError: null value in column "column_x_ptr_id" violates not-null constraint

Doing a \dt in psql, I get:

Indexes:
    "mytable_model_pkey" PRIMARY KEY, btree (column_x_ptr_id)
    "mytable_model_p_key" UNIQUE CONSTRAINT, btree (column_y_ptr_id)

So, my question is how can I modify this index like so?

"mytable_model_pkey" PRIMARY KEY, btree (column_y_ptr_id)

I'm not sure that would solve the problem though..

Answer 1

Ok this will give you a "more or less" of what you need to do. Your table looks something like this:

CREATE TABLE mytable_model
(
  column_x_ptr_id integer NOT NULL,
  column_y_ptr_id integer,
  CONSTRAINT mytable_model_pkey PRIMARY KEY (column_x_ptr_id),
  CONSTRAINT mytable_model_p_key UNIQUE (column_y_ptr_id)
)

You need to drop both indexes, create a new PK on the second column, and remove the NOT NULL constraint:

ALTER TABLE mytable_model DROP CONSTRAINT mytable_model_pkey;
ALTER TABLE mytable_model DROP CONSTRAINT mytable_model_p_key;
ALTER TABLE mytable_model ADD CONSTRAINT mytable_model_pkey PRIMARY KEY (column_y_ptr_id);
ALTER TABLE mytable_model ALTER COLUMN column_x_ptr_id DROP NOT NULL;

Bear in mind that adding a primary key to column_y_ptr_id will change the column to NOT NULL. If any records have NULL in that field, this will fail. Then as I mentioned, you will probably want to put another index on column_x_ptr_id for performance reasons. What type you use is up to you.