postgresqlcastinghugsql

Casting Multiple Values in HugSQL or YesQL with Postgres

I'm trying to cast a list of ip addresses to ::inet but only the last element in the list gets converted.

I've tried the following but nothing seems to work.

select * from ip_addresses where address in (:addresses::inet)

select * from ip_addresses where address in (:addresses)::inet

select * from ip_addresses where address in CAST (:addresses AS inet)

I can't simply cast the address::text since matches can fail if the input addresses don't have a subnet specified.

Solution

  • Sounds like HugSQL is replacing :addresses with a comma delimited list so your (:addresses::inet) ends up looking like (addr, addr, addr, ..., addr::inet). If that's the case then you could replace in (list) with = any(array), use the array constructor syntax, and apply a cast to that array as a whole:

    select * from ip_addresses where address = any(array[:addresses]::inet[])