sql icon indicating copy to clipboard operation
sql copied to clipboard

Casting to some of the integer types generates invalid queries

Open Bogdanp opened this issue 6 years ago • 0 comments

I use a bunch of custom domains in my application for validation and, because those domains get assigned custom type ids which this library doesn't (I think) support, I end up having to cast various columns. I've noticed that casting to int4 and int8 and text works just fine

> (sql-ast->string (scalar-expr-qq (select (cast 1 int4))))
"(SELECT CAST(1 AS int4))"

> (sql-ast->string (scalar-expr-qq (select (cast 1 int8))))
"(SELECT CAST(1 AS int8))"

> (sql-ast->string (scalar-expr-qq (select (cast 1 text))))
"(SELECT CAST(1 AS text))"

but casting to integer produces invalid sql:

(sql-ast->string (scalar-expr-qq (select (cast 1 integer))))
"(SELECT CAST(1 AS \"INTEGER\"))"

Note the quotes around INTEGER. In Postgres, executing this query produces an error:

ERROR:  42704: type "integer" does not exist
LINE 1: select cast(1 as "integer");
                         ^
LOCATION:  typenameType, parse_type.c:257
Time: 0.454 ms

Casting to smallint, bigint, decimal or numeric exhibits the same behavior.

Bogdanp avatar Apr 25 '19 13:04 Bogdanp