adding an auto increment column to an existing table

[Chris Withers]

So, I screwed up and realised I really want an auto-incrementing integer
as the primary key for a bunch of tables.

I've changed my models, got all the tests passing and now I need to get
the migrations done, I have:


op.add_column('observation',
sa.Column('id', sa.Integer(), nullable=False,
autoincrement=True))
op.drop_constraint('observation_pkey', table_name='observation')
op.create_primary_key('observation_pkey', 'observation', ['id'])


According to this answer, this should work:

http://stackoverflow.com/a/19262262/216229

...but when running the migration, I get:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) column "id"
contains null values
[SQL: 'ALTER TABLE observation ADD COLUMN id INTEGER NOT NULL']

...so what am I doing wrong?

cheers,

Chris

[Chris Withers]

Okay, so this worked:

op.execute(CreateSequence(Sequence("observation_id_seq")))

op.add_column('observation', sa.Column(
'id', sa.Integer(), nullable=False,

server_default=sa.text("nextval('observation_id_seq'::regclass)")

))
op.drop_constraint('observation_pkey', table_name='observation')
op.create_primary_key('observation_pkey', 'observation', ['id'])

...but how come my original attempt didn't?

cheers,

Chris

[mike bayer]

On 01/10/2017 03:33 AM, Chris Withers wrote:
> Okay, so this worked:
>
> op.execute(CreateSequence(Sequence("observation_id_seq")))
> op.add_column('observation', sa.Column(
> 'id', sa.Integer(), nullable=False,
> server_default=sa.text("nextval('observation_id_seq'::regclass)")
> ))
> op.drop_constraint('observation_pkey', table_name='observation')
> op.create_primary_key('observation_pkey', 'observation', ['id'])
>
> ...but how come my original attempt didn't?

when you add a NOT NULL column it needs to be able to create a value for
all the existing rows, so yeah giving it a server default so it can do
that is the right way to go. you can look in your column and see 1, 2,
3, 4, 5, 6, 7, ... that it got from that.

[Chris Withers]

On 10/01/2017 16:05, mike bayer wrote:
>
>
> On 01/10/2017 03:33 AM, Chris Withers wrote:
>> Okay, so this worked:
>>
>> op.execute(CreateSequence(Sequence("observation_id_seq")))
>> op.add_column('observation', sa.Column(
>> 'id', sa.Integer(), nullable=False,
>> server_default=sa.text("nextval('observation_id_seq'::regclass)")
>> ))
>> op.drop_constraint('observation_pkey', table_name='observation')
>> op.create_primary_key('observation_pkey', 'observation', ['id'])
>>
>> ...but how come my original attempt didn't?
>
> when you add a NOT NULL column it needs to be able to create a value for
> all the existing rows, so yeah giving it a server default so it can do
> that is the right way to go. you can look in your column and see 1, 2,
> 3, 4, 5, 6, 7, ... that it got from that.

Sure, but if I was creating a table from scratch, all I'd have to do was
add primary_key=True or autoincrement=True and this would be done for me.

http://stackoverflow.com/a/19262262/216229 implies the same works for
Alembic, but it didn't work at all for me. So I was wondering if that
answer was wrong, or if I was doing something wrong.

cheers,

Chris

[Mike Bayer]

On Jan 16, 2017 1:31 AM, "Chris Withers" <ch...@withers.org> wrote:

On 10/01/2017 16:05, mike bayer wrote:

On 01/10/2017 03:33 AM, Chris Withers wrote:

Okay, so this worked:

op.execute(CreateSequence(Sequence("observation_id_seq")))
op.add_column('observation', sa.Column(
    'id', sa.Integer(), nullable=False,
    server_default=sa.text("nextval('observation_id_seq'::regclass)")
))
op.drop_constraint('observation_pkey', table_name='observation')
op.create_primary_key('observation_pkey', 'observation', ['id'])

...but how come my original attempt didn't?

when you add a NOT NULL column it needs to be able to create a value for
all the existing rows, so yeah giving it a server default so it can do
that is the right way to go.  you can look in your column and see 1, 2,
3, 4, 5, 6, 7, ... that it got from that.

Sure, but if I was creating a table from scratch, all I'd have to do was add primary_key=True or autoincrement=True and this would be done for me.

Because there's no data in a brand new table, the server default isn't needed to create the not-null column. 

http://stackoverflow.com/a/19262262/216229 implies the same works for Alembic, but it didn't work at all for me. So I was wondering if that answer was wrong, or if I was doing something wrong.

cheers,

Chris

--
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-alembic+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Chris Withers]

On 16/01/2017 13:49, Mike Bayer wrote:
>
> On 01/10/2017 03:33 AM, Chris Withers wrote:
>
> Okay, so this worked:
>
> op.execute(CreateSequence(Sequence("observation_id_seq")))
> op.add_column('observation', sa.Column(
> 'id', sa.Integer(), nullable=False,
>
> server_default=sa.text("nextval('observation_id_seq'::regclass)")
> ))
> op.drop_constraint('observation_pkey', table_name='observation')
> op.create_primary_key('observation_pkey', 'observation', ['id'])
>
> ...but how come my original attempt didn't?
>
>
> when you add a NOT NULL column it needs to be able to create a
> value for
> all the existing rows, so yeah giving it a server default so it
> can do
> that is the right way to go. you can look in your column and
> see 1, 2,
> 3, 4, 5, 6, 7, ... that it got from that.
>
>
> Sure, but if I was creating a table from scratch, all I'd have to do
> was add primary_key=True or autoincrement=True and this would be
> done for me.
>
> Because there's no data in a brand new table, the server default isn't
> needed to create the not-null column.

No needed, but it is created, isn't that the point of autoincrement=True?

> http://stackoverflow.com/a/19262262/216229 implies the same works
> for Alembic, but it didn't work at all for me. So I was wondering if
> that answer was wrong, or if I was doing something wrong.

Still wondering if this answer is wrong or if I'm doing something wrong...

Chris

[mike bayer]

for postgresql, autoincrement=True means that if the column is marked
primary_key=True it will use the datatype SERIAL for that column, which
in PG does mean the sequence is generated and added as the server side
default. I'm not sure offhand if Alembic does this with op.add_column().

if the column is not an integer primary key column then autoincrement
doesn't do anything.

>
>> http://stackoverflow.com/a/19262262/216229 implies the same works
>> for Alembic, but it didn't work at all for me. So I was wondering if
>> that answer was wrong, or if I was doing something wrong.
>
> Still wondering if this answer is wrong or if I'm doing something wrong...

that SO answer is showing op.create_table(). your example was just for
op.add_column(). super different.




>
> Chris
>

[Chris Withers]

On 17/01/2017 15:07, mike bayer wrote:
>
>>> Because there's no data in a brand new table, the server default isn't
>>> needed to create the not-null column.
>>
>> No needed, but it is created, isn't that the point of autoincrement=True?
>
> for postgresql, autoincrement=True means that if the column is marked
> primary_key=True it will use the datatype SERIAL for that column, which
> in PG does mean the sequence is generated and added as the server side
> default.

Would you expect a SERIAL on Postgres to end up being an inteeger with a
default of a sequence when viewed in psql?

What happens with autoincrement on a non-primary-key column? My
experiences suggests it does nothing...

>>> http://stackoverflow.com/a/19262262/216229 implies the same works
>>> for Alembic, but it didn't work at all for me. So I was wondering if
>>> that answer was wrong, or if I was doing something wrong.
>>
>> Still wondering if this answer is wrong or if I'm doing something
>> wrong...
>
> that SO answer is showing op.create_table(). your example was just for
> op.add_column(). super different.

I have to admit that, at the column level, that's surprising to me.
Where can I see the differences between a column created as part of
create_table() verus as part of an add_column()?

cheers,

Chris

[mike bayer]

On 01/18/2017 05:20 AM, Chris Withers wrote:
> On 17/01/2017 15:07, mike bayer wrote:
>>
>>>> Because there's no data in a brand new table, the server default isn't
>>>> needed to create the not-null column.
>>>
>>> No needed, but it is created, isn't that the point of
>>> autoincrement=True?
>>
>> for postgresql, autoincrement=True means that if the column is marked
>> primary_key=True it will use the datatype SERIAL for that column, which
>> in PG does mean the sequence is generated and added as the server side
>> default.
>
> Would you expect a SERIAL on Postgres to end up being an inteeger with a
> default of a sequence when viewed in psql?
>
> What happens with autoincrement on a non-primary-key column? My
> experiences suggests it does nothing...
>
>>>> http://stackoverflow.com/a/19262262/216229 implies the same works
>>>> for Alembic, but it didn't work at all for me. So I was
>>>> wondering if
>>>> that answer was wrong, or if I was doing something wrong.
>>>
>>> Still wondering if this answer is wrong or if I'm doing something
>>> wrong...

Well in fact now that i've read the original SO question, the answer you
refer to is completely wrong. The correct answer to the question as
asked is:

op.execute("CREATE SEQUENCE group_id_seq")
op.create_table(
'groups',
Column('id', Integer, Sequence('group_id_seq'), primary_key=True),
Column('name', Unicode(50)),
Column('description', Unicode(250)),
)

the poster also seems to have figured out this syntax:

op.execute(CreateSequence(Sequence(name)))

which is fine, just more work (because Sequence is pretty much only a
Postgresql / Oracle thing and you say CREATE SEQUENCE on either, will
fail on all other DBs except firebird)


this however has nothing to do with your issue of the NOT NULL
constraint failing, because in your case, you have data in your table
and you need a server side default. The SO question as asked does not
create a server side default.

>>
>> that SO answer is showing op.create_table(). your example was just for
>> op.add_column(). super different.
>
> I have to admit that, at the column level, that's surprising to me.
> Where can I see the differences between a column created as part of
> create_table() verus as part of an add_column()?

SQL wise the syntaxes are:

https://www.postgresql.org/docs/9.1/static/sql-createtable.html

https://www.postgresql.org/docs/9.1/static/sql-altertable.html

Alembic op.add_column() will emit the SERIAL if you send a Column with
primary_key=True and Integer datatype on a Postgresql backend. I just
added this to confirm:

+ def test_col_w_pk_is_serial(self):
+ context = op_fixture("postgresql")
+ op.add_column("some_table", Column('q', Integer, primary_key=True))
+ context.assert_(
+ 'ALTER TABLE some_table ADD COLUMN q SERIAL NOT NULL'
+ )


SERIAL would be all you need here.


>
> cheers,
>
> Chris
>

[Chris Withers]

>>> for postgresql, autoincrement=True means that if the column is marked
>>> primary_key=True it will use the datatype SERIAL for that column, which
>>> in PG does mean the sequence is generated and added as the server side
>>> default.
>>
>> Would you expect a SERIAL on Postgres to end up being an inteeger with a
>> default of a sequence when viewed in psql?
>>
>> What happens with autoincrement on a non-primary-key column? My
>> experiences suggests it does nothing...

'ALTER TABLE some_table ADD COLUMN q SERIAL NOT NULL' does, indeed, add
a column of type 'integer' with a server-side default set to be the next
value from a sequence that is automagically created.

...but how can I specify I want a non-primary-key SERIAL column on a model?

>>> that SO answer is showing op.create_table(). your example was just for
>>> op.add_column(). super different.
>>
>> I have to admit that, at the column level, that's surprising to me.
>> Where can I see the differences between a column created as part of
>> create_table() verus as part of an add_column()?
>
>
> SQL wise the syntaxes are:

Okay, but from the docs:

> https://www.postgresql.org/docs/9.1/static/sql-createtable.html

{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

> https://www.postgresql.org/docs/9.1/static/sql-altertable.html

ADD [ COLUMN ] column data_type [ COLLATE collation ] [
column_constraint [ ... ] ]

...bar the initial verbage seem the same.

> Alembic op.add_column() will emit the SERIAL if you send a Column with
> primary_key=True and Integer datatype on a Postgresql backend. I just
> added this to confirm:
>
> + def test_col_w_pk_is_serial(self):
> + context = op_fixture("postgresql")
> + op.add_column("some_table", Column('q', Integer,
> primary_key=True))
> + context.assert_(
> + 'ALTER TABLE some_table ADD COLUMN q SERIAL NOT NULL'
> + )
>
> SERIAL would be all you need here.

Cool, so back to my question above: how can I specify I want a
non-primary-key SERIAL column on a model? I hope I'm just being blind,
but I couldn't find SERIAL importable anywhere.

cheers,

Chris

[mike bayer]

On 01/31/2017 02:00 AM, Chris Withers wrote:
>>>> for postgresql, autoincrement=True means that if the column is marked
>>>> primary_key=True it will use the datatype SERIAL for that column, which
>>>> in PG does mean the sequence is generated and added as the server side
>>>> default.
>>>
>>> Would you expect a SERIAL on Postgres to end up being an inteeger with a
>>> default of a sequence when viewed in psql?
>>>
>>> What happens with autoincrement on a non-primary-key column? My
>>> experiences suggests it does nothing...
>
> 'ALTER TABLE some_table ADD COLUMN q SERIAL NOT NULL' does, indeed, add
> a column of type 'integer' with a server-side default set to be the next
> value from a sequence that is automagically created.
>
> ...but how can I specify I want a non-primary-key SERIAL column on a model?

a recipe for the SERIAL datatype is here:
https://bitbucket.org/zzzeek/sqlalchemy/issues/3438