NamedQuery error when use PostgreSQL type cast ::int

[merlindeep]

When I use NamedQuery for PostgreSQL and use type cast ::int in query:

SELECT (d.categories->>0)::int AS category_id
FROM some_table d
WHERE id =:id

I get error:

error="pq: syntax error at or near \":\""

if I use CAST

SELECT CAST(d.categories->>0 AS integer) AS category_id
FROM some_table d
WHERE id =:id

query works fine

[kisamoto]

Just spotted this but I'm guessing it's because you're using a NamedStmt which requires you to double escape all :. Your generated query will only be providing one : to PostgreSQL and then failing.

Try changing to:

SELECT (d.categories->>0)::::int AS category_id

[powerman]

@kisamoto Thanks. Is this way to double-escape : documented?

[kisamoto]

@powerman I didn't find it in the docs, instead I noticed it searching for a similar problem that led me to #91

[quenbyako]

@kisamoto
Try changing to:

SELECT (d.categories->>0)::::int AS category_id