From: | Daryl Richter <daryl(at)brandywine(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Updating cidr column with network operator |
Date: | 2005-09-22 20:26:01 |
Message-ID: | 43331359.1020703@brandywine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Axel Rau wrote:
> 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.
I'm still not understanding what you're trying to do, perhaps its a
language issue. :) Let me try again.
I built your schema and inserted some rows:
insert into network( id ) values( '10.1' );
insert into address( id, network ) values( '10.1.0.1', '10.1' );
insert into address( id, network ) values( '10.1.0.2', '10.1' );
insert into address( id, network ) values( '10.1.0.3', '10.1' );
I then select from network:
id
-----------
10.1.0.0/16
and from address:
id network
-------- -----------
10.1.0.1 10.1.0.0/16
10.1.0.2 10.1.0.0/16
10.1.0.3 10.1.0.0/16
Why do you now want to update address.network? They are already
pointing to the right network, aren't they?
I think if you provide some sample data we can figure this out.
>
>>
>>> 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;
>
Ahh, ok. see above.
> Axel
>
> Axel Rau, Frankfurt, Germany +49-69-951418-0
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
--
Daryl
From | Date | Subject | |
---|---|---|---|
Next Message | Kenneth Hutchinson | 2005-09-22 20:33:51 | Issue with UPDATE statement on v8 |
Previous Message | Dmitri Bichko | 2005-09-22 19:10:42 | Re: Help with a view |