Re: adding an auto increment column to an existing table
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
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
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: adding an auto increment column to an existing table
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
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: adding an auto increment column to an existing table
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
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: adding an auto increment column to an existing table
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 -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: adding an auto increment column to an existing table
On 01/17/2017 06:10 AM, Chris Withers wrote:
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?
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
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: adding an auto increment column to an existing table
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
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: adding an auto increment column to an existing table
On Jan 16, 2017 1:31 AM, "Chris Withers" 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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: adding an auto increment column to an existing table
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
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: adding an auto increment column to an existing table
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.
cheers,
Chris
On 10/01/2017 08:03, Chris Withers wrote:
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
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: adding an auto increment column to an existing table
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
On 10/01/2017 08:03, Chris Withers wrote:
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
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
