From: | "Scot Kreienkamp" <SKreien(at)la-z-boy(dot)com> |
---|---|
To: | "Steve Atkins" <steve(at)blighty(dot)com>, "pgsql-general General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: CIDR data type query help |
Date: | 2010-05-20 12:39:47 |
Message-ID: | 59E62072D4EFFF4BB912EC607F8FA60BDBFF91@lzbs6304.na.lzb.hq |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)la-z-boy(dot)com
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Steve Atkins
Sent: Wednesday, May 19, 2010 2:18 PM
To: pgsql-general General
Subject: Re: [GENERAL] 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
From | Date | Subject | |
---|---|---|---|
Next Message | Hernan Danielan | 2010-05-20 12:49:23 | Re: Connection lost |
Previous Message | Richard Walker | 2010-05-20 06:19:17 | How to lose transaction history (xmin values, WAL, etc.)? |