IntegrityError while adding a NOT NULL column to postgres

[utkar...@gmail.com]

Hello,

I am trying to run a migration where I added a new "not null" column:
op.add_column('x_user_token', sa.Column('x_receiver_id', sa.Integer(), nullable=False))

Now when I run: alembic upgrade head

I get this error:
  File "/Users/x/.virtualenvs/checkbook/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (IntegrityError) column "x_receiver_id" contains null values
 'ALTER TABLE x_user_token ADD COLUMN x_receiver_id INTEGER NOT NULL' {}

I am assuming this is similar to this problem: http://od-eon.com/blogs/stefan/adding-not-null-column-south/

But setting a default value like this didn't help: op.add_column('checkbook_user_token', sa.Column('checkbook_receiver_id', sa.Integer(), nullable=False, default=1))
Also, I don't want to add a default value to this field.

Any suggestions?

Thanks,
-Utkarsh

[utkar...@gmail.com]

Never mind, found a fix here: https://alembic.readthedocs.org/en/latest/ops.html#operation-reference
The upgrade func should have this:

    op.add_column('x_user_token', sa.Column('x_receiver_id', sa.Integer(), nullable=False, server_default=DefaultClause("0")))

Although, my problem still persists. If I pass empty value to x_user_token (maybe due to a bug), it won't complain and just set its value=0.

Thanks,
-Utkarsh

[Michael Bayer]

well the approach you take here depends on what you want, let’s understand the problem first.

you have a table, which already has rows:

id   data

== ====

1    d1

2    d2

3    d3

now you want to add a column x_receiver_id.  you want it to be NOT NULL.   Here’s that:

id   data   x_receiver_id

== ====  ==========

1    d1           ???

2    d2           ???

3    d3           ???

problem!  you’re adding a column to the table, it has three rows, which means you’re adding three more values to that table (1 column x 3 rows).    The database has no idea what value it should put here, so it puts NULL - which in SQL means, “we don’t know what this value is”.

id   data   x_receiver_id

== ====  ==========

1    d1           NULL

2    d2           NULL

3    d3           NULL

but, problem again!  you want this column to be NOT NULL.  what should go there?   the database has no way to know, and you have to tell it.   So you set your default of “0”, OK.

But you don’t want a default of zero in there, and that makes sense.  so your two options here are 1. remove the server default after the fact, or 2. add the column as NULL, perform an UPDATE, then set to NOT NULL.    I tend to go with #2:

from sqlalchemy.sql import table, column

op.add_column('x_user_token', sa.Column('x_receiver_id', sa.Integer(), nullable=True))

x_user_token = table('x_user_token', column('x_receiver_id'))

op.execute(x_user_token.update().values(x_receiver_id=0))

op.alter_column('x_user_token', 'x_receiver_id', nullable=False)

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Utkarsh Sengar]

Thanks! That worked!

--
Thanks,
-Utkarsh