Thread: Proposal: is_castable
Currently there is no way to check if CAST will succeed.
Therefore I propose adding new function: is_castable
SELECT is_castable('foo' as time) // false
SELECT is_castable('123' as numeric) // true
SELECT is_castable(1.5 as int) // true
SELECT is_castable('1.5' as int) // false
Many users write their own functions:
https://stackoverflow.com/q/10306830/2446102 (11k views, ~25 upvotes)
https://stackoverflow.com/a/16206123/2446102 (72k views, 70 upvotes)
https://stackoverflow.com/q/2082686/2446102 (174k views, ~150 upvotes)
Similar features are implemented in:
- SQL Server (as TRY_CONVERT)
- Oracle (as CONVERT([val] DEFAULT [expr] ON CONVERSION ERROR)
I would love to implement it myself, but my knowledge of C is superficial.
Thanks,
Michał Wadas
Hi
pá 3. 4. 2020 v 13:45 odesílatel Michał Wadas <michalwadas@gmail.com> napsal:
Currently there is no way to check if CAST will succeed.Therefore I propose adding new function: is_castableSELECT is_castable('foo' as time) // falseSELECT is_castable('123' as numeric) // trueSELECT is_castable(1.5 as int) // trueSELECT is_castable('1.5' as int) // falseMany users write their own functions:https://stackoverflow.com/q/10306830/2446102 (11k views, ~25 upvotes)https://stackoverflow.com/a/16206123/2446102 (72k views, 70 upvotes)https://stackoverflow.com/q/2082686/2446102 (174k views, ~150 upvotes)Similar features are implemented in:- SQL Server (as TRY_CONVERT)- Oracle (as CONVERT([val] DEFAULT [expr] ON CONVERSION ERROR)I would love to implement it myself, but my knowledge of C is superficial.
It's is interesting feature - and implementation can be very easy - but without enhancing type API this function can be pretty slow.
So there is a dilemma - simple implementation (few work) but possible very negative performance impact under higher load due work with savepoints, or much larger work (probably easy) without necessity to use safepoints.
Regards
Pavel
Thanks,Michał Wadas
=?UTF-8?Q?Micha=C5=82_Wadas?= <michalwadas@gmail.com> writes: > Currently there is no way to check if CAST will succeed. > Therefore I propose adding new function: is_castable > SELECT is_castable('foo' as time) // false What would you actually do with it? > Similar features are implemented in: > - SQL Server (as TRY_CONVERT) > - Oracle (as CONVERT([val] DEFAULT [expr] ON CONVERSION ERROR) Somehow, I don't think those have the semantics of what you suggest here. I suspect you are imagining that you could write something like CASE WHEN is_castable(x as y) THEN cast(x as y) ELSE ... but that will not work. The THEN condition has to pass parse analysis whether or not execution will ever reach it. regards, tom lane
> What would you actually do with it?
I am one of the users of these do-it-yourself functions, and I use them in my ETL pipelines heavily.
For me, data gets loaded into a staging table, all columns text, and I run a whole bunch of validation queries
on the data prior to it moving to the next stage in the pipeline, a strongly typed staging table, where more
validations are performed. So I currently check each column type with my custom can_convert_sometype(text)
functions, and if the row has any columns that cannot convert, it marks a boolean to ignore moving that row
to the next strongly typed table (thus avoiding the cast for those rows).
For this ETL process, I need to give users feedback about why specific specific rows failed to be processed, so
each of those validations also logs an error message for the user for each row failing a specific validation.
So it's a two step process for me currently because of this, I would love if there was a better way to handle
this type of work though, because my plpgsql functions using exception blocks are not exactly great
for performance.
>> Similar features are implemented in:
>> - SQL Server (as TRY_CONVERT)
>> - Oracle (as CONVERT([val] DEFAULT [expr] ON CONVERSION ERROR)
>
>> - SQL Server (as TRY_CONVERT)
>> - Oracle (as CONVERT([val] DEFAULT [expr] ON CONVERSION ERROR)
>
> Somehow, I don't think those have the semantics of what you suggest here.
Agreed that they aren't the same exact feature, but I would very much love the ability to both
know "will this cast fail?", and also be able to "try and cast, but if it fails just put this value and don't error".
They both have uses IMO, and while having is_castable() functions built in would be great, I just want to
express my desire for something like the above feature in SQL Server or Oracle as well.
Hi
So it's a two step process for me currently because of this, I would love if there was a better way to handlethis type of work though, because my plpgsql functions using exception blocks are not exactly greatfor performance.
Probably we can for some important buildin types write method "is_valid", and this method can be called directly. For custom types or for types without this method, the solution based on exceptions can be used.
This should not be too much code, and can be fast for often used types.