[SQL] Sum up network events by type, interval and network
Hi all,
For each event of each type in each interval I want to compute the sum
of events referencing the same relayNet (via host) and insert/update 1
row in SumOfEvents.
All intervals start at a time, minimum 5 minutes in the past, rounded
down modulo 5 minutes like:
SELECT DATE_TRUNC('MINUTES', NOW () - ('0:' || (SELECT
(EXTRACT('MINUTE' FROM NOW())::INT % 5) + 5 ) || ':0')::INTERVAL);
CREATE TABLE host (
idSERIAL PRIMARY KEY,
relayNetFKINT
REFERENCES relayNet
ON DELETE CASCADE
)
CREATE TABLE event (
idSERIAL PRIMARY KEY,
type CHAR NOT NULL
CHECK ( type IN ('C', 'A', 'D', 'S', 'R') ),
timeOfEvent timestamp NOT NULL
DEFAULT NOW(),
hostfkINT NOT NULL
REFERENCES host
ON DELETE CASCADE
)
CREATE TABLE relayNet (
idSERIAL PRIMARY KEY,
name TEXTNOT NULL UNIQUE
)
CREATE TYPE eventIntervals AS ENUM ('5m', '30m', '3h', '24h', '30d');
CREATE TABLE SumOfEvents (
idSERIAL PRIMARY KEY,
type CHAR NOT NULL
CHECK ( type IN ('C', 'A', 'D', 'S', 'R') ),
startTime timestamp NOT NULL,
interval eventIntervals NOT NULL,
value INT,-- sum
relayNetFKINT
REFERENCES relayNet
ON DELETE CASCADE,
UNIQUE (type, interval, relayNetFK)
)
Can this be done w/o procedural code?
Any hints?
Thanks, Axel
---
axel@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @
chaos claudius
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause
Good morning, I have a function argument blah of type text containing something like 33,44,55,66 . Can I cast it in some way to use it in an IN clause as integers like UPDATE foo SET x = y WHERE id IN ( blah ); or need I revert to dynamic SQL (EXECUTE...) ? Thanks, Axel --- axel@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause [re-post]
Good morning, I have a function argument blah of type text containing something like 33,44,55,66 . Can I cast it in some way to use it in an IN clause as integers like UPDATE foo SET x = y WHERE id IN ( blah ); or need I revert to dynamic SQL (EXECUTE...) ? Thanks, Axel --- axel@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause [re-post]
Thanks Richard and Sergey, your solution works perfect, even if blah contains only one member. Am 25.10.2010 um 18:17 schrieb Richard Broersma: Here is what I think should work: UPDATE foo Set x = y WHERE id = ANY( CAST( string_to_array( '1,2,3,4', ',' ) AS INTEGER[] )); Axel --- axel@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Poor performance in inet cidr join (Resolved)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 03.11.2005 um 00:22 schrieb Tom Lane: Axel Rau [EMAIL PROTECTED] writes: Question: Can rtree_inet be included in the core? No, because rtree is going away in 8.2. Feel like converting that code to be a GIST opclass, instead? Perhaps. It would be a big step for me, since I started with pg 3 month ago. As starting points, I found - - chapter 48 in the 8.0 manual - - rtree_gist in 8.0 contrib - - btree_gist in 8.1 contrib Any more? Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (Darwin) iQEVAwUBQ2oIlMFz9+6bacTRAQIwHQgAgOKK5rxY4aTrEStJeljORZwUWQre66ZD ZoD6HYcVxJepRC9lEbakxLmdokHtaMp1rqWziiv7idlDqApc6deVlo7ESozFC2jZ Yb/hVBIxmaBuHFj2n/AbYwGPR18g1SLODhyOj6/QlciKLw0apAVLUb0iHCfS7ie0 qSaCh/oARM8066SpMtdBX5oKLRgcgXYYtK9UNmX0njLqyDmCd9WZRrOwnLRRzptZ k5R1iMDrksV/Hifx9RcaGNRkQ4JZ15rf3OCLHgwMMwrpfLB0jMcBN8o1YEpZimJc Ffc5ChGlTfb4ADcCMGl3mBgBFNRoUZqMKrFakvEHJY65jz8ng3bl3w== =Hco5 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Poor performance in inet cidr join (Resolved)
Am 31.10.2005 um 19:53 schrieb Axel Rau:
The planner does not use the pk-indices. Poking around,
I could not find an operator class, which supports the containment ('') operator.
Is my conclusion correct?
How can the functionality be extended?
Is there any implementation available?
rtree_inet from John Hansen does the trick:
http://archives.postgresql.org/pgsql-hackers/2005-01/msg01038.php
First test shows query time of 85 instead of 2745 seconds:
QUERY PLAN
-
Aggregate (cost=1768557.75..1768557.75 rows=1 width=0)
-> Seq Scan on event e (cost=0.00..1768040.83 rows=206769 width=0)
Filter: (NOT (subplan))
SubPlan
-> Index Scan using network_id_rtree on network n (cost=0.00..21.28 rows=5 width=0)
Index Cond: ($0 (id)::inet)
Question: Can rtree_inet be included in the core?
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
[SQL] Poor performance in inet cidr join
Hi SQLers,
in 8.0, joining 2 tables:
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'
...
)
as:
SELECT COUNT(*) FROM address A
WHERE NOT EXISTS (
SELECT A.id FROM network N WHERE A.id N.id );
shows this query plan:
Aggregate (cost=2264.51..2264.51 rows=1 width=0)
-> Seq Scan on address a (cost=0.00..2225.86 rows=15459 width=0)
Filter: (NOT (subplan))
SubPlan
-> Seq Scan on network n (cost=0.00..107.95 rows=2038 width=0)
Filter: ($0 (id)::inet)
The planner does not use the pk-indices. Poking around,
I could not find an operator class, which supports the containment ('') operator.
Is my conclusion correct?
How can the functionality be extended?
Is there any implementation available?
Thanks, Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
Re: [SQL] Selecting count of details along with details columns
Am 29.09.2005 um 10:30 schrieb Richard Huxton: Axel Rau wrote: SELECT T2.T2_name, COUNT(T1.id) AS xx FROM T2, T1 WHERE T2.id = T1.fk_t2 GROUP BY T2.T2_name HAVING COUNT(T1.id) > 1 ORDER BY xx DESC; t2_name | xx -+ T2-N2 | 3 T2-N3 | 2 (2 rows) Adding column t1_name to the result set breaks COUNT(T1.id): SELECT T2.T2_name, T1.T1_name, COUNT(T1.id) AS xx FROM T2, T1 WHERE T2.id = T1.fk_t2 GROUP BY T2.T2_name, T1.T1_name HAVING COUNT(T1.id) > 1 ORDER BY xx DESC; t2_name | t1_name | xx -+-+ (0 rows) How can I do this with pg ? Do what? You don't say what results you are expecting. Do you want: 1. ALL values of T1_name (in which case what count do you want)? 2. The FIRST value of T1_name (in which case what do you mean by first)? #1.: t2_name | t1_name | count -+-+--- T2-N2 | T1-CCC | 3 T2-N3 | T1-FFF | 2 T2-N2 | T1-BBB | 3 T2-N2 | T1-DDD | 3 T2-N3 | T1-EEE | 2 (5 rows) Sorry, for not making this clear. Thank you for taking the time, Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 PGP.sig Description: Signierter Teil der Nachricht
Re: [SQL] Selecting count of details along with details columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 29.09.2005 um 12:03 schrieb Richard Huxton: Axel Rau wrote: Am 29.09.2005 um 10:30 schrieb Richard Huxton: Axel Rau wrote: ... Ah - this is two questions: 1. What are the unique (t2_name,t1_name) pairings? 2. How many different (t1.id) values are there for each t2. So - something like: SELECT names.T2_name, names.T1_name, counts.num_t2 FROM ( SELECT DISTINCT T2.T2_name, T1.T1_name FROM T2,T1 WHERE T2.id = T1.fk_t2 ) AS names, ( SELECT T2.T2_name, COUNT(T1.id) AS num_t2 FROM T2, T1 WHERE T2.id = T1.fk_t2 GROUP BY T2.T2_name HAVING COUNT(T1.id) 1 ) AS counts WHERE names.T2_name = counts.T2_name ; You could write the names sub-query with a GROUP BY if you wanted of course. Exactly, that query works as I expected. Thank you. Can you answer this question as well: Looking for a workaround, I learned that aggregate functions are not allowed in WHERE clauses. Question: Is this conform with the standard? Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (Darwin) iQEVAwUBQzw7n8Fz9+6bacTRAQIqnAf9EW7TS7K+cCf95fosagOcNhgQFuUvlyUr yJpkXrv83+oKJ6kw6OcJxaEAkuiyRIiGQAlsVfc86itgKUQLfq6qpXEjeMD459kb wIO01LV37akn9y3420h4Pmi1SDaZ63oUWJn48DhlUuuh5B7LHNyiOSMUKLU8ptLd ZQ875uPo235bdqb15ibmZtwAuMGdsf3PPySBYMzvHzk7uZ+68b50QTmTPSU7VuPd XtbZWdTK8q6+R3mhgz6k7DFaqTlTqzMimQevmwb1ADZZGVOOC0i77M1axYsCHarB i2RT1CAcnNCX8MYc2nt8HS4j5KXpq7POFk3vdyAmVMwZ8WHNWJP2/w== =CJSg -END PGP SIGNATURE- ---(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
[SQL] Selecting count of details along with details columns
Dear pgsql-admin members:
Having 2 tables:
CREATE TABLE T2 (
idserial PRIMARY KEY,
T2_name text
);
CREATE TABLE T1 (
idserial PRIMARY KEY,
T1_name text,
fk_t2 int4REFERENCES t2
);
And some rows:
INSERT INTO T2 (T2_name) VALUES('T2-N1');
INSERT INTO T2 (T2_name) VALUES('T2-N2');
INSERT INTO T2 (T2_name) VALUES('T2-N3');
INSERT INTO T2 (T2_name) VALUES('T2-N4');
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-AAA', 1);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-BBB', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-CCC', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-DDD', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-EEE', 3);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-FFF', 3);
It is possible to show how many details exist and to limit result with HAVING:
SELECT T2.T2_name, COUNT(T1.id) AS xx
FROM T2, T1
WHERE T2.id = T1.fk_t2
GROUP BY T2.T2_name
HAVING COUNT(T1.id) > 1
ORDER BY xx DESC;
t2_name | xx
-+
T2-N2 | 3
T2-N3 | 2
(2 rows)
Adding column t1_name to the result set breaks COUNT(T1.id):
SELECT T2.T2_name, T1.T1_name, COUNT(T1.id) AS xx
FROM T2, T1
WHERE T2.id = T1.fk_t2
GROUP BY T2.T2_name, T1.T1_name
HAVING COUNT(T1.id) > 1
ORDER BY xx DESC;
t2_name | t1_name | xx
-+-+
(0 rows)
How can I do this with pg ?
Looking for a workaround, I learned that aggregate functions are not allowed in WHERE clauses.
Question: Is this conform with the standard?
Sorry, if this has been discussed earlier.
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
Re: [SQL] Updating cidr column with network operator
Am 27.09.2005 um 16:02 schrieb Daryl Richter: > An attribute is redundant if it repeats a fact that can be learned > without it. If one table contains IP addresses and another contains > networks, then you can associate IP addresses and networks with a > join of the two tables; indeed, this is how the fix the network > column update works. Having a network column in the address table > simply repeats what could be learned through the join. > > I agree with Michael here. I think the fundamental problem with your schema is that it is possible to have contradictory data between the network and address table, always a bad situation. I would replace network.id with a serial type value and make the cidr a separate column, for example: CREATE TABLE network ( id int not null PRIMARY KEY, address cidrnot null, attr1 varchar(10) null ); CREATE TABLE address ( id inetPRIMARY KEY, networkint NOT NULL REFERENCES network ); I agree with Michael too, but I understand him differently: What he says is: Get rid of the redundancy, which means to me: remove the fk from address to network completly. The attribute network is not realy needed because we can always join address.id network.id This reduces the necessary logic to keep things consistent. I still can have my cascaded delete in network, have to do it with a trigger. I'm currently looking at performance issues. Introducing a synthetic pk in network does not really make things easier. Instead I introduced an insert/update trigger which prevents from overlaps in network (which is not as a matter of course for cidr columns, I have learnt): CREATE OR REPLACE FUNCTION dd.ids_network_update_insert_check() RETURNS TRIGGER AS $$ BEGIN -- check if new net overlapps with existing one PERFORM N.id FROM network N WHERE NEW.id N.id OR NEW.id >> N.id; IF FOUND THEN RAISE EXCEPTION '?Attempt to insert overlapping network %', NEW.id; RETURN NULL; END IF; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; Axel Axel Rau, Frankfurt, Germany +49-69-951418-0
Re: [SQL] Updating cidr column with network operator
Am 27.09.2005 um 17:02 schrieb Daryl Richter: Ok, I guess, but isn't tit true now that you can insert a new address row which doesn't belong to any valid network?? Yes, I can. But in the earlier approach, the fk pointed at a special row in network (UNKNOWN), which maks no big difference. It's the nature of my ids/abuse application, that there are always faked source addresses, which may not belong to any routed net. Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Updating cidr column with network operator
Am 26.09.2005 um 02:05 schrieb Michael Fuhr: On Fri, Sep 23, 2005 at 09:19:25PM +0200, Axel Rau wrote: Am 23.09.2005 um 19:32 schrieb Michael Fuhr: On Fri, Sep 23, 2005 at 06:31:17PM +0200, Axel Rau wrote: Networks change during time, being diveded or aggregated or you just enter wrong data during insert. Have you considered using a CHECK constraint and/or a trigger to ensure that the network in the network column contains the address in the id column? If you have and rejected the idea, what were the reasons? I'm sure this would be the cleanest solution but remember networks change. Yes, which is why it's a good idea to automatically propogate those changes to tables that maintain redundant data. I would not call it redundant but normalized, because network has some attributes, common to all addresses in the net, 1st of all the netmask. If that data isn't reliable then there's little point in maintaining it. Reliability is a big issue in my application, because it's some kind of data mining of internet structures (networks, Autonomous Systems and abuse addresses). Whois data is seldom correct, so I changed recently to use the internet routing table for the most important network data. This constraind would have to update all details (addresses) of a 10/8 being splitted in a 10/9 and a 10.128/9. If this can be done with pg, it is above my current knowledge level. (But feel free to send a suggestion). See the documentation for PL/pgSQL and triggers. You could write a trigger function to automatically update the address table whenever the network table changes. Or, since the foreign keys already cascade on update, you could have a trigger on the address table that checks whether the new network contains the IP address, and if it doesn't then it looks up the correct network. I will try this, but be sure I will come back with questions. (-;). The other point is performance. Inserting new addresses is a realtime job while correcting network changes is a daily maintenance job. Triggers on update shouldn't affect insert performance, and since you already have a foreign key constraint to slow inserts down, adding a CHECK constraint should have negligible impact. The need for regular corrections is a sign that perhaps the design could be improved. This is one reason to avoid maintaining redundant data if possible: you have to take additional steps to ensure that it remains consistent. I agree. This update also might not give the results you want if more than one network matches. This is not possible, because the pk of network is the net cidr. Yes, it is possible, because the update's join condition isn't equality but rather containment. If the network table contains 10.1.0.0/16 and 10.1.0.0/24, then the IP address 10.1.0.1 would match both. You mean, unique does not imply none-overlapping for data-type network? Oh, I didn't know that. Who is responsible for this func spec? This is completly contra- real-world-experience. Can this be re-considered for a future release? I do understand now the background of your arguments. First what I have to do, is to fix that network table to forbid overlapps. I expect that I will find overlapps already in the table, because I have not yet written the maintenance code to deleting/reorganizing nets (-;). Deleting involves scanning a 300 MB flat file and looking which row in network has no longer an entry in the flat file. I did try this in pg in the 1st place but could not keep up updating 9 million rows 3 times in 4 hours on a 2x 900MHz 2GB Powermac G4. I currently have no idea how to solve that (Possibly worth another thread). If your application prevents 10.1.0.0/16 and 10.1.0.0/24 from both being in the network table then *that's* the reason multiple matches aren't possible, but it's not because of the primary key. -- Michael Fuhr Thank you for taking the time to explain this, Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Updating cidr column with network operator
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 23.09.2005 um 19:32 schrieb Michael Fuhr: On Fri, Sep 23, 2005 at 06:31:17PM +0200, Axel Rau wrote: Networks change during time, being diveded or aggregated or you just enter wrong data during insert. Have you considered using a CHECK constraint and/or a trigger to ensure that the network in the network column contains the address in the id column? If you have and rejected the idea, what were the reasons? I'm sure this would be the cleanest solution but remember networks change. This constraind would have to update all details (addresses) of a 10/8 being splitted in a 10/9 and a 10.128/9. If this can be done with pg, it is above my current knowledge level. (But feel free to send a suggestion). The other point is performance. Inserting new addresses is a realtime job while correcting network changes is a daily maintenance job. With the UPDATE below, I want to correct the addresses to again point at the right net. Does the following statement do what you want? Yes. Thank you. This was the 1st answer I'm looking for. Just too simple. It shouldn't touch the records with no matching network -- what do you want to happen in those cases? They will be updated to reference '0.0.0.0/32' (the UNKNOWN net). Will this work: UPDATE address SET network = '0.0.0.0/32' WHERE NOT EXISTS (SELECT address.id network.id); ? This update also might not give the results you want if more than one network matches. This is not possible, because the pk of network is the net cidr. UPDATE address SET network = n.id FROM network n WHERE address.id n.id; While writing this, I learn that because of the pk in network, UPDATEs will be difficult to accomplish (you may need a temporary net to park all addresses of a network to be divided, make the change in network and use the UPDATE below to adjust A.network. I use net '0.0.0.0/32' as 'UNKNOWN' net or for parking. I'm not sure I understand what you're saying, but if you're concerned about foreign key violations then making the foreign key constraints deferrable and deferring them during certain updates might remove the need for a temporary parking network. I see. But I have to sort in the orphaned addresses anyway if I delete a net for splitting or aggregating or even the net may be abandoned so its children will have to be moved to the UNKNOWN net. Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (Darwin) iQEVAwUBQzRVS8Fz9+6bacTRAQKIlwgAn6G8mXkT+vODCW+n9/dUmOB/NYOJVfZL T7/oiYpSVWz1ApcIbcQii+RvhpEZXvgpHif8i5Nd0yeV2347PKwflttGSiWJxVPt mVUrYxjfIjAKmYhbOP25aHK/AGqgjgQRrCOosz3Kbzr5OY4kpNhF67oosGDpIVq+ DcC7nx6+QoHkFByBqL7xTlHDNBS98baVCeGDTIeaJOFEsU1u6t+29ORHloicBo6n 3QZz2qLTMVNzcX/mfS6BqV4POOMSza9zMyRApTwM5lwM+HBAOXvMJ0INiGA0hLE0 o+kVa0I0JTBD4RByxt9c66qFtFN5Y6oZFonm+pBA6nRliBIpt2/8ZA== =fhnD -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Updating cidr column with network operator
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 23.09.2005 um 20:43 schrieb Daryl Richter: [snip] I think that I now see what the problem is -- Why do you have a network table at all? It's redundant. There are more attributes and fks, I didn't mention to simplify. The schema is events - addresses - autnums - organisations. Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (Darwin) iQEVAwUBQzRWjsFz9+6bacTRAQIT4Af/fYbPJtgi9sDcWTm84hbtPl9BUS6Rt0Wo 728oZO7/iEXNSCoaZCqUymK2sKzsE5XV1EZL7xeBCoDZmj+i/3LS0cEAq3Q4corU HEXBvRQ1HcEbvaRioefo5pk6iKvgBxbz9guKawcPwgHJMdvAFSlJpDB1lXOkZgTJ P/8goloEygGFFTEEsquP+4Us0sdBDNe9g0TLapNwI8A9VQ/10tqKF29hEa+tUUun DjkdWAWndAiZvz5zoTfyBTFFKNu9e4dHYNSRMtmz16d5oMYwhIePuHT4lPw03hBW 0FOq4CnkF5YUIrC5XoEta6J1Zxf7FGY1ba+xHBUZjviKCtjtmPS34A== =XBqf -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Updating cidr column with network operator
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): UPDATE address SET network = (SELECT N.id WHERE A.id N.id) FROM address A, network N WHERE A.id N.id; 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
Re: [SQL] Updating cidr column with network operator
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
