From: | Axel Rau <Axel(dot)Rau(at)Chaos1(dot)DE> |
---|---|
To: | daryl(at)brandywine(dot)com |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Updating cidr column with network operator |
Date: | 2005-09-22 17:58:34 |
Message-ID: | 72c14f47f1dca0ae99e559cef56416a3@Chaos1.DE |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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):
>
> 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?
The fk requires a corresponding row in network. But my update tries to
reference the right network, that one where the ip address belongs to.
>
>> 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" ...
I'm using 8.0.3 and there are some new operators related to inet and
cidr data types.
On page 157, I found "<<" as address/network "is contained in" network.
Finding the net where an address belongs to works as:
SELECT id FROM network WHERE inet '$p_ipSource' << id;
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitri Bichko | 2005-09-22 18:03:40 | Re: Help with a view |
Previous Message | Leif B. Kristensen | 2005-09-22 17:29:35 | Help with a view |