X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 342BF53CBE for ; Mon, 25 Apr 2005 17:24:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04478-09 for ; Mon, 25 Apr 2005 20:24:13 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 3821654E9E for ; Mon, 25 Apr 2005 17:24:12 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j3PKO6RJ039894 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Mon, 25 Apr 2005 14:24:09 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j3PKO6Q6003359; Mon, 25 Apr 2005 14:24:06 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j3PKO6XC003358; Mon, 25 Apr 2005 14:24:06 -0600 (MDT) (envelope-from mfuhr) Date: Mon, 25 Apr 2005 14:24:06 -0600 From: Michael Fuhr To: Rob Casson Cc: pgsql-sql@postgresql.org Subject: Re: searching cidr/inet arrays Message-ID: <20050425202406.GA3289@winnie.fuhr.org> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/375 X-Sequence-Number: 21268 On Mon, Apr 25, 2005 at 02:46:37PM -0400, Rob Casson wrote: > > i'm having trouble figuring out how to search in inet arrays....its > been a long time since i used postgres array support, so i may just be > bone-headed...... > > how can i determine if a given ip address is contained in the subnet > declaration inside an array? > > {134.53.25.0/24,134.53.0.0/16} > {134.53.24.0/24} > > i'd like to see which rows match an ip of, say, 134.53.24.2..... See "Row and Array Comparisons" in the "Functions and Operators" chapter of the documentation. The following works in 7.4 and later: CREATE TABLE foo ( id serial PRIMARY KEY, nets cidr[] NOT NULL ); INSERT INTO foo (nets) VALUES ('{134.53.25.0/24,134.53.0.0/16}'); INSERT INTO foo (nets) VALUES ('{134.53.24.0/24}'); SELECT * FROM foo WHERE '134.53.24.2' << ANY (nets); id | nets ----+-------------------------------- 1 | {134.53.25.0/24,134.53.0.0/16} 2 | {134.53.24.0/24} (2 rows) SELECT * FROM foo WHERE '134.53.100.2' << ANY (nets); id | nets ----+-------------------------------- 1 | {134.53.25.0/24,134.53.0.0/16} (1 row) -- Michael Fuhr http://www.fuhr.org/~mfuhr/