From: Michael Fuhr <mike@fuhr.org>
To: Axel Rau <Axel.Rau@Chaos1.DE>
Cc: daryl@brandywine.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Updating cidr column with network operator
Date: Fri, 23 Sep 2005 11:32:04 -0600
Message-ID: <20050923173204.GA80995@winnie.fuhr.org> (raw)
In-Reply-To: <bbbf1059080c058d91cb869bf4cf750a@Chaos1.DE>
References: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
<4332C38E.5040807@brandywine.com>
<72c14f47f1dca0ae99e559cef56416a3@Chaos1.DE>
<43331359.1020703@brandywine.com>
<bbbf1059080c058d91cb869bf4cf750a@Chaos1.DE>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, Axel.Rau@Chaos1.DE, daryl@brandywine.com
Subject: Re: Updating cidr column with network operator
In-Reply-To: <20050923173204.GA80995@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox