Thanks Steve, that works nicely in the testing I've done so far. I'll keep in mind about the pgfoundry project. I don't see this growing overly large, but you never know. I didn't realize the CIDR type couldn't be indexed. Scot Kreienkamp skreien@xxxxxxxxxxxx -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Steve Atkins Sent: Wednesday, May 19, 2010 2:18 PM To: pgsql-general General Subject: Re: CIDR data type query help On May 19, 2010, at 10:32 AM, Scot Kreienkamp wrote: > Hi everyone, > > I have a column of type CIDR in a table that I am querying that contains > the values of 10/8, 10.1/16,10.1.28/24, and 10.1.28.95. I am trying to > return only the one record that's most specific compared to the IP > address I'm currently on as this is being done in a CGI script. If > there's no specific match for the IP of the originating workstation then > it should return the /24 if it's there, then the /16 if it's there, etc. > I have never worked with the CIDR type, and a novice when it comes to > SQL query language, so I have no idea how to approach this. Something like this (untested): select foo from table where foo >>= '10.1.28.14' order by masklen(foo) desc limit 1; You likely want to look at http://pgfoundry.org/projects/ip4r/ as an alternative, if the table is likely to grow beyond a few dozen rows. It's usefully indexable for "contains" queries, unlike the native cidr type, Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
