# mike@xxxxxxxx / 2005-07-23 06:04:55 -0600: > On Sat, Jul 23, 2005 at 11:31:23AM +0200, Roman Neuhauser wrote: > > > > I have a VARCHAR column containing mostly ip addresses, with an > > occasional piece of junk, and would like to transfer this data to an > > INET column. The UPDATE (SET inet_col = CAST(vc_col AS INET)) aborts > > as soon as it hits an invalid datum. I'm looking for a way to add > > something like WHERE IS_CASTABLE(vc_col, INET) to the update. > > If you're using 8.0, then you could write a PL/pgSQL function that > attempts to make the cast and traps INVALID_TEXT_REPRESENTATION. > > http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING I was (still am) afraid this would be too long in the teeth on my ~3M rows * N tables, and ITMT wrote one in C; it's basically PG_RETURN_BOOL(inet_aton(PG_GETARG_TEXT_P(0))); Not a full "this would make a valid INET / CIDR value" test, but good enough for my needs. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
