Skip to main content
Question

Type safe coercion

  • December 18, 2025
  • 2 replies
  • 5 views
Pieter_Sheth-Vo
Forum|alt.badge.img+2

What error-tolerant type coercion operators / functions exist?
I thought ::! would yield either 3 or null but it throws an error.
Use case is loading real world data into legacy tables for many columns with a consistent simple operator, not wanting to think too specifically about each particular column type, target type, and conversion path.

SELECT '3.14' ;  `'3.14'`
SELECT '3.14' :: FLOAT ;  `3.14`
SELECT '3.14' :: INT  ;  ` Invalid input syntax for integer: "3.14" `
SELECT '3.14' ::! INT  ;  `Invalid input syntax for integer: "3.14" `
SELECT '3.14' !:: INT  ;  `7` (this was a fun one to figure out)
SELECT CAST('3.14' AS INT);  `Invalid input syntax for integer: "3.14"`
SELECT '3.14' :: FLOAT :: INT ; `3`
SELECT ROUND( '3.14') ; `3`

    2 replies

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Participating Frequently
    • December 30, 2025

    Did you test table data also? There is a note on cast failures: "Note that this functionality only applies to table data. It does not work on inline constant casts and in expressions automatically reduced to such." at the bottom of page https://docs.vertica.com/25.4.x/en/sql-reference/language-elements/operators/data-type-coercion-operators-cast/cast-failures/

    As an aside, why does SELECT '3.14' !:: INT return 7? PostgreSQL errors on this.

    Pieter_Sheth-Vo
    Forum|alt.badge.img+2
    • Pieter_Sheth-Vo
    • Author
    • Participating Frequently
    • December 30, 2025

    I wish there was a "O-Negative" to which any data type could be coerced reversibly. Im thinking VARCHAR is the closest even though casting to varchar and back isn't always lossless.

    Fun fact:

    • SELECT 3.14 ::! INT; 3
    • SELECT 3.14 !:: INT; 7
    • SELECT 3.14 !; 7.1732691901879

    Whats actually happening is the 3.14 !:: INT is intpreted as "take the factorial of 3.14 and convert it to INT". Factorials as we learned them in high school operate on integers, e.g. 3! = 3 * 2 * 1 = 6. But there's a more general function that takes real numbers as input, but returns results that match the factorials we recognize when given integers, and GAMMA(3.14) = 7.1732691901879

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement