Sequence for PostgreSQL

[Max Bourinov]

Hi guys!

I use PostgreSQL. I want to create an auto-increment field started from 1000, increment by 2.

My not working code:

class MyTable(db.Model):

    __tablename__ = 'my_table'

    id_seq = Sequence('my_table_id_seq', start=1000, increment=2)

    id = db.Column(db.Integer, id_seq, server_default=id_seq.next_value(), primary_key=True) 

In my autogenerated migration scrip I see this (which will obviously not work, because there is no definition for my_table_id_seq):

def upgrade():

    # ### commands auto generated by Alembic - please adjust! ###

    op.create_table('my_table',

    sa.Column('id', sa.Integer(), server_default=sa.text("nextval('my_table_id_seq')"), nullable=False),

    sa.PrimaryKeyConstraint('id')

    )

    # ### end Alembic commands ###

If I run it, I get expected error:

  File "/Users/maxb/Projects/ANDR3/Andr3Admin/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "my_table_id_seq" does not exist

 [SQL: "\nCREATE TABLE my_table (\n\tid INTEGER DEFAULT nextval('my_table_id_seq') NOT NULL, \n\tPRIMARY KEY (id)\n)\n\n"] (Background on this error at: http://sqlalche.me/e/f405)

So, how do I code it to have expected behaviour?

Thanks in advance!

[Mike Bayer]

On Wed, Jul 18, 2018 at 4:52 AM, Max Bourinov <bour...@gmail.com> wrote:
> Hi guys!
>
> I use PostgreSQL. I want to create an auto-increment field started from
> 1000, increment by 2.
>
> My not working code:
>
> class MyTable(db.Model):
> __tablename__ = 'my_table'
> id_seq = Sequence('my_table_id_seq', start=1000, increment=2)
> id = db.Column(db.Integer, id_seq, server_default=id_seq.next_value(),
> primary_key=True)

the correct form of Column with Sequence is at:

http://docs.sqlalchemy.org/en/latest/core/defaults.html#defining-sequences

note that the Sequence is placed inline inside the Column itself.

to combine it with nextval() as a server side default is additionally
described at:

http://docs.sqlalchemy.org/en/latest/core/defaults.html#associating-a-sequence-as-the-server-side-default


the second example includes both the Core Table and the declarative form.

>
> In my autogenerated migration scrip I see this (which will obviously not
> work, because there is no definition for my_table_id_seq):
>
> def upgrade():
> # ### commands auto generated by Alembic - please adjust! ###
> op.create_table('my_table',
> sa.Column('id', sa.Integer(),
> server_default=sa.text("nextval('my_table_id_seq')"), nullable=False),
> sa.PrimaryKeyConstraint('id')
> )
> # ### end Alembic commands ###
>
> If I run it, I get expected error:
>
> File
> "/Users/maxb/Projects/ANDR3/Andr3Admin/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py",
> line 507, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation
> "my_table_id_seq" does not exist
> [SQL: "\nCREATE TABLE my_table (\n\tid INTEGER DEFAULT
> nextval('my_table_id_seq') NOT NULL, \n\tPRIMARY KEY (id)\n)\n\n"]
> (Background on this error at: http://sqlalche.me/e/f405)
>
> So, how do I code it to have expected behaviour?
>
> Thanks in advance!
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

Correction, I just noticed you are trying to get Alembic autogenerate
to spit it out. not supported right now, see
https://bitbucket.org/zzzeek/alembic/issues/60/autogenerate-for-sequences-as-well-as
.