Re: Updating cidr column with network operator - Mailing list pgsql-sql

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