On 08/28/2016 02:42 PM, Duke Dougal wrote:
I'm using POstgres 9.5, Python 3 and SqlAlchemy 1.0.14 When a new Issue is created, I want it to have sequence_number equal to the largest existing sequence number + 1 Each thread_id has its own sequence starting at 1. However when I create a new record I get: sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) aggregate function calls cannot contain window function calls class Issues(db.Base): __tablename__ = 'issues' id = Column(String, primary_key=True) thread_id = Column(String, nullable=False) sequence_number = Column(Integer, default=select([func.coalesce(func.max(func.row_number().over(partition_by=thread_id)) + 1,1)])) I've spent all day muttering spells but cannot find the correct incantation. Can anyone suggest what I can do to get this going please?
why not use "SELECT MAX(sequence_number) + 1 FROM table WHERE thread_id=:thread_id" ?
Note that this approach is not safe against concurrent inserts from other threads or processes, you can easily have duplicate sequence_numbers on a thread_id.
-- 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+unsubscr...@googlegroups.com <mailto:sqlalchemy+unsubscr...@googlegroups.com>. To post to this group, send email to sqlalchemy@googlegroups.com <mailto:sqlalchemy@googlegroups.com>. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
-- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.