Re: Updating cidr column with network operator
От | Daryl Richter |
---|---|
Тема | Re: Updating cidr column with network operator |
Дата | |
Msg-id | 43331359.1020703@brandywine.com обсуждение исходный текст |
Ответ на | Re: Updating cidr column with network operator (Axel Rau <Axel.Rau@Chaos1.DE>) |
Ответы |
Re: Updating cidr column with network operator
(Axel Rau <Axel.Rau@Chaos1.DE>)
|
Список | 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
В списке pgsql-sql по дате отправления: