Message-ID: <4332C38E.5040807@brandywine.com>
Date: Thu, 22 Sep 2005 10:45:34 -0400
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Updating cidr column with network operator
References: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
In-Reply-To: <ffaabb4676ab075beb4b6d041ec280c4@Chaos1.DE>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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