Search code examples
sqlpostgresqldatabase-designsqlalchemyalembic

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

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?

Solution

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