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..

Are you saying you want to drop the current primary key, drop the unique index, and create a new primary key on column_y? — 404
– 404, Commented May 9, 2018 at 10:51
If this is safe, I would not mind. The fact is that we faced the problem on the prod db, so I'm a bit reluctant to do so. Good thing is that this specific table is still empty... — unicorn
– unicorn, Commented May 9, 2018 at 10:53
Sorry, that was a mistake... The error is "IntegrityError: null value in column "column_x_ptr_id" violates not-null constraint" — unicorn
– unicorn, Commented May 9, 2018 at 10:56
If column_x should allow NULL values, it cannot be a primary key. So you will have to remove the PK, and change the column to allow NULL (PK columns are defined as NOT NULL, you will have to change that). Presumably you will want to put another index on this column otherwise performance may suffer. — 404
– 404, Commented May 9, 2018 at 11:05
Dropping / Adding index does not modify table data. Make sure while adding a Unique / primary index, the column satisfies its constraints. — Lohit Gupta
– Lohit Gupta, Commented May 9, 2018 at 11:07

404 · Accepted Answer · 2018-05-09 11:51:56Z

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.

answered May 9, 2018 at 11:51

404

8,7622 gold badges34 silver badges53 bronze badges

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

2 Comments

unicorn Over a year ago

unicorn Over a year ago

Yep that was the drill... Thank you very much!

Collectives™ on Stack Overflow

Change Index in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related