Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Post History
Context: I have a varchar column in a segment event table that contains version numbers. Most of these are formatted nicely as X.Y.Z (major.minor.patch) where X, Y, and Z are integers. However, the...
#1: Initial revision
Redshift int casting from varchar - how to force early evaluation order?
Context: I have a varchar column in a segment event table that contains version numbers. Most of these are formatted nicely as X.Y.Z (major.minor.patch) where X, Y, and Z are integers. However, there are a few bad values, e.g. X.Y.Z-a, X.Y.Z-testing, etc. that I want to exclude entirely. I've confirmed that the results of the following query are what I expect (no bad version vales): ``` select distinct app_version from table_name where app_version ~ '^[0-9.]+$' ``` However, when I go to use this later on, I run into the following error. ``` SQL Error [XX000]: ERROR: Invalid digit, Value 'f', Pos 0, Type: Integer Detail: ----------------------------------------------- error: Invalid digit, Value 'f', Pos 0, Type: Integer code: 1207 context: false query: 249112189 location: :0 process: query0_279_249112189 [pid=0] ----------------------------------------------- ``` For example, the following gives the example above: ``` with valid_versions as ( select distinct app_version from table_name where app_version ~ '^[0-9.]+$' ) select some_col from valid_versions inner join table_name on valid_versions.app_version = table_name.app_version where split_part(valid_versions.app_version, '.', 1)::INT = 5 ``` I think this is due to order of operations - that Redshift is trying to do the int conversion on values containing bad values and then erroring. I tried putting the "good" values into a temp table and everything worked fine, but I'd like to avoid temp tables if possible. The query also works fine if I don't cast to int at all, but I need to return a numeric for my query. Is there a way to force Redshift to evaluate the CTE first so it doesn't error out when casting the values? Alternatively, is there a way to "try cast" the value that will e.g. return a null rather than erroring?