CODEWITHSUNDEEP
postgresqlnetwork-programmingcidr
uralbash
uralbash

Reputation: 3618

Postgres CIDR. How to select only network w/o subnet?

I have a table Net where is the list of networks

-- Table: net

CREATE TABLE net
(
  id serial NOT NULL,
  cidr cidr,
  description text,
  CONSTRAINT net_pkey PRIMARY KEY (id )
)

I need to select all the networks that do not. belong to other networks. ie only network without subnets. How to build a query?

cidr:
10.0.0.0/8
10.1.0.0/16
10.2.0.0/16
10.3.0.0/16
10.3.1.0/24
10.3.2.0/24
10.3.3.0/24
10.15.1.0/24
10.15.2.0/24
10.15.3.0/24
172.20.0.0/16
172.21.0.0/16
172.0.0.0/8
11.11.11.0/24
Top rated net is 10.0.0.0/8, 172.0.0.0/8, 11.11.11.0/24

Upvotes: 1

Views: 2776

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658997

I understand your question like this: You want all network specifications that are not contained in any other network specification, i.e. are not part of the subnet of another.

Try this:

SELECT *
FROM   net
WHERE  NOT EXISTS (SELECT cidr FROM net n WHERE n.cidr >> net.cidr);

Produces your expected result.
Have a look at the chapter Network Address Functions and Operators in the manual for more on the >> operator.
Semi-join with NOT EXISTS is probably the fastest way to do this.

Upvotes: 3

Related Questions