From: Daryl Richter <daryl@brandywine.com>
To: pgsql-sql@postgresql.org
Subject: Re: Updating cidr column with network operator
Date: Thu, 22 Sep 2005 10:45:34 -0400
Message-ID: <4332C38E.5040807@brandywine.com> (raw)
In-Reply-To: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
References: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
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):
But you can't insert a row in address w/o a valid network.id? That's
what the fk ensures.
Perhaps you could elaborate more? Are you trying to *put* on the fk and
you currently have bad data?
> UPDATE address
> SET network = (SELECT N.id WHERE A.id << N.id)
> FROM address A, network N
> WHERE A.id << N.id;
>
This also makes no sense. For starters, << is "bitwise shift left" ...
> But this ended up with all network columns pointing at the same net (-:).
> Any help would be appreciated.
>
> Axel
> Axel Rau, Frankfurt, Germany +49-69-951418-0
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
Daryl
"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776
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: daryl@brandywine.com
Subject: Re: Updating cidr column with network operator
In-Reply-To: <4332C38E.5040807@brandywine.com>
* 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