Re: Updating cidr column with network operator

Axel Rau wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Am 22.09.2005 um 22:26 schrieb Daryl Richter:
>
>> Axel Rau wrote:
>>
>>> Thank you for responding, Daryl,
>>> Am 22.09.2005 um 16:45 schrieb Daryl Richter:
>>>
>>>> Axel Rau wrote:
>>>>
>>>>> Hi SQLers,
>>>>> I have a fk from address to network and try to update the foreign
>>>>> key column to point at the network, "it belongs to":
>>>>> CREATE TABLE network (
>>>>> id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address'
>>>>> )
>>>>> CREATE TABLE address (
>>>>> id inet PRIMARY KEY , -- 'PK of IPv4/6 host
>>>>> address'
>>>>> network cidr NOT NULL -- 'FK to Network table'
>>>>> REFERENCES network ON DELETE CASCADE ON
>>>>> UPDATE CASCADE
>>>>> )
>>>>> I tried (using the WHERE clause to eliminate the addresses were no
>>>>> corresponding net exists):
>>>>

[snip]

>
> Networks change during time, being diveded or aggregated or you just
> enter wrong data during insert.
> With the UPDATE below, I want to correct the addresses to again point at
> the right net. While writing this,
> I learn that because of the pk in network, UPDATEs will be difficult to
> accomplish (you may need a temporary net
> to park all addresses of a network to be divided, make the change in
> network and use the UPDATE below to
> adjust A.network. I use net '0.0.0.0/32' as 'UNKNOWN' net or for parking.
>
>>
>> I think if you provide some sample data we can figure this out.
>
> Yes, this a goof idea. Playing with small tables let you find quickly
> the right query. Lets start over with a slightly bigger
> collection of data:
>
> insert into network( id ) values( '10.1/16' );
> insert into network( id ) values( '10.2/16' );
> insert into network( id ) values( '10.3/16' );
>
> insert into address( id, network ) values( '10.1.0.1', '10.1/16' );
> insert into address( id, network ) values( '10.1.0.2', '10.1/16' );
> insert into address( id, network ) values( '10.1.0.3', '10.1/16' );
> insert into address( id, network ) values( '10.1.0.4', '10.2/16' );
> insert into address( id, network ) values( '10.1.0.5', '10.2/16' );
> insert into address( id, network ) values( '10.1.0.6', '10.3/16' );
>
> insert into address( id, network ) values( '10.200.0.6', '10.3/16'
> ); -- address not in network
> insert into address( id, network ) values( '10.200.0.7', '10.3/16'
> ); -- address not in network
>

But those are bad inserts, right?

I think that I now see what the problem is --> Why do you have a network
table at all? It's redundant. If you just insert the ids into your
address table, don't the new PostgreSQL operators give you all the
information you need?

So, for example, if I inserted the data above and then want to answer
the question, "What are all my 16-bit subnets?"

select distinct network( set_masklen( id, 16 ) ) from address;

network
-------------
10.1.0.0/16
10.2.0.0/16
10.200.0.0/16

[rest snipped]

--
Daryl