Type safe coercion

Pieter_Sheth-Vo

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`

Bryan_H

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

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