BETWEEN SYMMETRIC not supported

[malthe]

It's a convenient comparison operation – see PostgreSQL chapter on comparison functions.

[Mike Bayer]

here's an easy recipe for that:

from sqlalchemy.sql import column, and_, literal

def between_symmetric(x, y, z):
    return x.op("BETWEEN SYMMETRIC")(and_(y, z))

print between_symmetric(column('x'), literal('3'), literal('4'))

[malthe]

It's supported in both PostgreSQL and MariaDB – perhaps it could be a flag to the built-in between method? E.g. between(x, y, z, symmetric=True)? The default is asymmetric, obviously.

[Mike Bayer]

only if its in the SQL standard.

[Mike Bayer]

lucky ducks, it's in the SQL standard:

Specify a range comparison. Format <between predicate> ::= <row value predicand> <between predicate part 2> <between predicate part 2> ::= [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] <row value predicand> AND <row value predicand>

[Mike Bayer]

• Added new flag :paramref:.expression.between.symmetric, when set to True renders "BETWEEN SYMMETRIC". Also added a new negation operator "notbetween_op", which now allows an expression like ~col.between(x, y) to render as "col NOT BETWEEN x AND y", rather than a parentheiszed NOT string. fixes #2990

→ <<cset e16ede8cae00>>

[Mike Bayer]

thanks for the suggestion