sql
sql copied to clipboard
Casting to some of the integer types generates invalid queries
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.