Re: Updating cidr column with network operator

On Fri, Sep 23, 2005 at 06:31:17PM +0200, Axel Rau wrote:
> Networks change during time, being diveded or aggregated or you just
> enter wrong data during insert.

Have you considered using a CHECK constraint and/or a trigger to
ensure that the network in the network column contains the address
in the id column? If you have and rejected the idea, what were the
reasons?

> With the UPDATE below, I want to correct the addresses to again point
> at the right net.

Does the following statement do what you want? It shouldn't touch
the records with no matching network -- what do you want to happen
in those cases? This update also might not give the results you
want if more than one network matches.

UPDATE address SET network = n.id FROM network n WHERE address.id << n.id;

> 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'm not sure I understand what you're saying, but if you're concerned
about foreign key violations then making the foreign key constraints
deferrable and deferring them during certain updates might remove
the need for a temporary "parking" network.

--
Michael Fuhr