While I can
SELECT elem[1], elem[2]
FROM ( VALUES ('1,2'::TEXT) ) AS q(arr),
LATERAL CAST(String_To_Array(q.arr, ',') AS INT[]) AS elem
;
using an explicit call to CAST
, I can't
SELECT elem[1], elem[2]
FROM ( VALUES ('1,2'::TEXT) ) AS q(arr),
LATERAL String_To_Array(q.arr, ',')::INT[] AS elem
;
using the implicitly calling ::
operator:
ERROR: syntax error at or near "::"
One other location at which an explicit CAST
is required:
CREATE INDEX ON ... ( CAST(<straw> AS <gold>) );
I doubt there is a syntactical reason, e.g. using extra enclosing parenthesis – which is incorrect here.
Is the explicit function call simply needed at this point as part of the low level implementation? Or does it follow any language rules?
Best Answer
Excellent corner case examples. Both of these syntax variants are "explicit type casts", doing exactly the same. It just so happens that some special locations in SQL code only allow functional notation to avoid ambiguities.
As for your second observation:
Shorthand syntax can actually be used here - with an additional set of parentheses to make it unambiguous:
db<>fiddle here
And either syntax variant matches the other expression in queries, too. See:
(There are more efficient ways to do what your first example does, but that's probably beside the point.)