Type safe coercion
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`
Best Answers
-
Bryan_H
Vertica Employee Administrator
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.
0 -
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 !:: INTis 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.17326919018790 - SELECT 3.14 ::! INT;