Re: selecting for type cast failures - Mailing list pgsql-general

From Adrian Klaver
Subject Re: selecting for type cast failures
Date
Msg-id 51395DBF.2050706@gmail.com
Whole thread Raw
In response to selecting for type cast failures  (Natalie Wenz <nataliewenz@ebureau.com>)
Responses Re: selecting for type cast failures
List pgsql-general
On 03/07/2013 05:08 PM, Natalie Wenz wrote:
> Hi!
>
> I am working on updating some of our tables to use appropriate native data types;  they were all defined as text when
theywere created years ago. 
>
> What I am running into, though, is there are some records that have bad data in them, where they can't be
successfullyconverted to int, or float, or boolean, for example. 
>
> Is there a straightforward way to identify offending records?
>
> I've been able to identify some with things like "...not similar to '(0|1)'..." for the boolean fields, and "...not
similarto '[0-9]{1,}'..." for int. 
> Are regular expressions the best approach here or is there a better way?
>
> Thoughts?

My opinion, it would take more time to concoct regexes that cover all
the corner cases than to write a script that walks the through the data
, finds the problem data and flags them.

>
> I've poked around on the internet and have found some people suggesting user-defined functions. I'd prefer to just
usea query, since it's a one-time clean-up. 

Again, most 'one time' things I have done turned out not to be:)

>
> (I'm using postgres 9.2)
>
>
> Thanks!
> Natalie
>


--
Adrian Klaver
adrian.klaver@gmail.com


pgsql-general by date:

Previous
From: Guy Rouillier
Date:
Subject: Re: selecting for type cast failures
Next
From: Craig Ringer
Date:
Subject: Re: EDB installer should check for valid %COMSPEC%