Question 89435 in PostgreSQL Question posted in
The official documentation can be found here.

How to add a new column to an existing database table with unique and not null constraint – Postgresql

I have a users table, with the primary key being an id field, which is an autoincrementing integer. I don’t have a username field right now, but I would like to add one. Now, the username should be UNIQUE and NOT NULL so that I can query the database with the username and so I can’t have a default value. How can I add the username column in my case, what happens to the existing tuples? If I were to use an ORM and a migration tool (like sqlalchemy and alembic), how can I go about this migration?

Tags:

2

Answers


  1. 0

    If you add a column to a table with existing rows, it will either be filled with NULL, or else a DEFAULT value if you define one.

    But a DEFAULT value obviously doesn’t work if the column also has a UNIQUE constraint. Assuming you have more than one row in the table, you can’t fill the new column with the same default value on all rows, because then it would violate the UNIQUE property.

    How to resolve this? You can’t do all these changes in a single step.

    You must choose one of these strategies:

    • Add the column but do not define it as NOT NULL and don’t give it a DEFAULT. Let the new column be filled with NULLs. You can still define a UNIQUE constraint at the same time, because UNIQUE ignores NULLs. Then you would UPDATE all the rows to assign each a different value. After that, if you want, you can ALTER TABLE again to make it NOT NULL.

    • Add the column, optionally with NOT NULL and a DEFAULT, but do not add the UNIQUE constraint yet. Let the column be filled with the DEFAULT value. Then UPDATE all the rows to assign each a different value. After that, you can ALTER TABLE a second time to add the UNIQUE constraint.

    Sorry, not all kinds of changes can be done in a single step.

    Login or Signup to reply.
  2. 0

    You can do in steps:

    alter table t add column username varchar(20);       -- add the new column
update t set username = 'user' || id;                -- set the values of it
alter table t alter column username set not null;    -- set it as NOT NULL
alter table t add constraint uq1 unique (username);  -- set is as UNIQUE

    See running example at db<>fiddle.

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

Back To Top
Search