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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
