Database Administration
postgresql type-conversion cast operator
Updated Tue, 21 Jun 2022 06:59:33 GMT

PostgreSQL - CAST vs :: operator on LATERAL table function


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?

Solution

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

Comments (5)

  • +0 – 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? — Nov 27, 2020 at 10:40  
  • +0 – 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! — Nov 27, 2020 at 10:44  
  • +0 – Nevermind on the link, I just realized @Lennart may have provided it in their comment. — Nov 27, 2020 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. — Nov 27, 2020 at 18:55  
  • +0 – 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! — Nov 27, 2020 at 19:56  

External Links

External links referenced by this document:

Linked Articles

Local articles referenced by this article:

References