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?

  • 2
    Interesting observation. I have no idea of what the reason might be, but the same phenomena are discussed in:https://www.postgresql.org/message-id/10477.1499970459%40sss.pgh.pa.us – Lennart Nov 26 '20 at 23:15
  • @Lennart thanks for the link, that seems to be the hiding list topic referred to in one of the linked answers in Erwin's answer! – geozelot Nov 27 '20 at 11:03

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:

One other location at which an explicit CAST is required:

CREATE INDEX ON ... ( CAST(<straw> AS <gold>) );

Shorthand syntax can actually be used here - with an additional set of parentheses to make it unambiguous:

CREATE INDEX ON ... ((<straw>::<gold>));

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.)

  • Thanks for clarifying my explicit vs. implicit misconception. Indeed I wasn't aware one could simply add parenthesis around an operator cast in my second observation; a shame that this PG list topic mentioned in a comment to your first linked answer hasn't turned up - do you happen to have a link handy to explain the need here in detail? – geozelot Nov 27 '20 at 10:40
  • Oh, and, I did use this very example as a suggested, 'maybe more idiomatic' way to retrieve coordinates from a string column, to then get directly passed into a function in the SELECT - see here; if you find that to be a suitable place for an improvement, I'd be happy to stand corrected! – geozelot Nov 27 '20 at 10:44
  • Nevermind on the link, I just realized @Lennart may have provided it in their comment. – geozelot Nov 27 '20 at 11:04
  • 1
    @geozelot: I was about to suggest split_part(). See stackoverflow.com/a/8612456/939860. Over there, Daniel's answer already suggests as much. – Erwin Brandstetter Nov 27 '20 at 18:55
  • And after benchmarking that, it seems that is absolutely correct. I do find that rather counter-intuitive, though, but that needs investigation elsewhere. Thanks again! – geozelot Nov 27 '20 at 19:56

It is a bit weird, yes, but the grammar will only accept something syntactically similar to a function call in a function-in-FROM expression.

So that is indeed a trick you can use if you want an arbitrary expression in a FROM clause: surround it with an unnecessary CAST expression.

PostgreSQL will happily treat anything that looks like a function as a table function in such a context.

  • I thought I was sure that this is no syntax issue, and it seems, at this specific location at least, that is true. And I always appreciate to learn some dark trickery; I will explore that some more, just because. Thanks a lot! – geozelot Nov 27 '20 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.