Need to execute migration with : in the expression

[candiduslynx]

I have a functional index I need to generate that includes : in the expression:

(split_part(category, ':', 1) || ':___' || SUBSTRING(split_part(category, ':', 2) FROM 4))

When generating it via the following expression Alembic generates NULL instead of ':___':

(
    func.split_part(category, literal(":"), literal_sql(1))
        + type_coerce(text(":___"), type_=TEXT)
        + func.substring(func.split_part(category, literal(":"), literal(2)), literal(4))
)

Initially I tried with literal(":___") instead of type_coerce(text(":___"), type_=TEXT) but it starts treating the string as param and shows '%(___)s' in the query text.

Could you please advice on the Alembic usage and how to force it not to treat : as parameter start?

Update: workaround of literal(":") + literal("___") seems to work.

[CaselIT]

Hi,

Please refer to the text documentation on how to escape the colon https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.text

[candiduslynx]

When I do that it generates '\\\\:___'

[CaselIT]

Can you provide an example of what you are doing?

from sqlalchemy import *

s = sa.select(func.split_part('category', literal(":"), literal(1)) + type_coerce(text("\\:___"), type_=TEXT) + func.substring(func.split_part('category', literal(":"), literal(2)), literal(4)))
print(s.compile(compile_kwargs={"literal_binds": True}).string)

this prints:

SELECT split_part('category', ':', 1) || :___ || substring(split_part('category', ':', 2), 4) AS anon_1

that seems correct

[zzzeek]

when you have a thing you want evaluated as a column expression, literal_column() tends to be best:

from sqlalchemy import func
from sqlalchemy import literal
from sqlalchemy import literal_column
from sqlalchemy import TEXT
from sqlalchemy import type_coerce


expr = (func.split_part("category", literal(":"), 1)
        + type_coerce(literal_column(":___"), type_=TEXT)
        + func.substring(func.split_part("category", literal(":"), literal(2)), literal(4)))
print(expr)

for text() it looks like this:

expr = (func.split_part("category", literal(":"), 1)
        + type_coerce(text(r"\:___"), type_=TEXT)
        + func.substring(func.split_part("category", literal(":"), literal(2)), literal(4)))

im not able to reproduce your \\\\ issue

[candiduslynx]

I still see %(___)s when trying type_coerce(literal_column(":___"), type_=TEXT).
This might be an issue with how I create view text:

str(
                view.get_view().compile(
                    compile_kwargs={"literal_binds": True},
                    dialect=postgresql.dialect(),
                ),
            )

where get_view returns a Select that also has this (I use it to manage materialized views).