Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D58726326E3 for ; Wed, 28 Jan 2009 13:49:17 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 29186-04 for ; Wed, 28 Jan 2009 13:49:13 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227]) by mail.postgresql.org (Postfix) with ESMTP id 419426326B5 for ; Wed, 28 Jan 2009 13:49:13 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id 20D117E498B for ; Wed, 28 Jan 2009 11:48:59 -0600 (CST) X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 2fksos1dTMr3 for ; Wed, 28 Jan 2009 11:48:25 -0600 (CST) Received: from whitneys (dhcp-100-188.int.journyx.com [192.168.100.188]) by mail.int.journyx.com (Postfix) with ESMTP id B4C457E4165 for ; Wed, 28 Jan 2009 11:48:24 -0600 (CST) From: "Scott Whitney" To: Subject: Strange query problem... Date: Wed, 28 Jan 2009 11:49:43 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350 Thread-Index: AcmBcMyVrDWDBl/oQ1S3GFGCB3680g== Message-Id: <20090128174824.B4C457E4165@mail.int.journyx.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/148 X-Sequence-Number: 31195 Um. How is this possible? Am I doing something very, very stupid, here? mydb=# select * from time_recs where id_time_rec not in (select id_time_rec from punch_time_recs); id_time_rec | id_user | record_date | id_code_task | id_code_pay_type | id_project | time_amount | comment | commit_state | id_domain | id_code_bill_type | id_group -------------+---------+-------------+--------------+------------------+---- --------+-------------+---------+--------------+-----------+---------------- ---+---------- (0 rows) mydb=# select count(*) from time_recs; count ------- 73725 (1 row) mydb=# select count(*) from punch_time_recs; count ------- 5369 (1 row) There are many occurences where this is true...Roughly 68,356, if my math is right. :) Table definitions: mydb=# \d time_recs Table "public.time_recs" Column | Type | Modifiers -------------------+------------------------+------------------------------- id_time_rec | character varying(38) | not null id_user | character varying(38) | not null record_date | character varying(38) | not null id_code_task | character varying(38) | not null id_code_pay_type | character varying(38) | not null id_project | character varying(38) | not null time_amount | double precision | not null comment | character varying(252) | default ''::character varying commit_state | integer | not null id_domain | character varying(38) | not null id_code_bill_type | character varying(38) | not null id_group | character varying(38) | not null Indexes: "cpk_123" PRIMARY KEY, btree (id_time_rec) "ix123_10" btree (id_code_bill_type) "ix123_2" btree (record_date) "ix123_3" btree (id_code_task) "ix123_4" btree (id_code_pay_type) "ix123_5" btree (id_project) "ixc123_1_2" btree (id_user, record_date) Foreign-key constraints: "f123_10_112" FOREIGN KEY (id_code_bill_type) REFERENCES codes_bill_types(id_code) "f123_1_104" FOREIGN KEY (id_user) REFERENCES users(id_user) "f123_3_109" FOREIGN KEY (id_code_task) REFERENCES codes_tasks(id_code) "f123_4_111" FOREIGN KEY (id_code_pay_type) REFERENCES codes_pay_types(id_code) "f123_5_108" FOREIGN KEY (id_project) REFERENCES projects(id_project) "f123_9_103" FOREIGN KEY (id_domain) REFERENCES domains(id_domain) mydb=# \d punch_time_recs Table "public.punch_time_recs" Column | Type | Modifiers -------------------+------------------------+------------------------------- id_punch | character varying(38) | not null id_user | character varying(38) | not null record_date | character varying(38) | not null id_code_task | character varying(38) | not null id_code_pay_type | character varying(38) | not null id_project | character varying(38) | not null punch_datetime | double precision | not null comment | character varying(252) | default ''::character varying id_time_rec | character varying(38) | when_exported | double precision | id_code_bill_type | character varying(38) | not null pre_or_post | double precision | id_group | character varying(38) | not null Indexes: "cpk_173" PRIMARY KEY, btree (id_punch) "ix173_10" btree (id_code_bill_type) "ix173_3" btree (id_code_task) "ix173_4" btree (id_code_pay_type) "ix173_5" btree (id_project) "ix173_6" btree (punch_datetime) "ix173_8" btree (id_time_rec) "ixc173_1_2" btree (id_user, record_date) Foreign-key constraints: "f173_10_112" FOREIGN KEY (id_code_bill_type) REFERENCES codes_bill_types(id_code) "f173_1_104" FOREIGN KEY (id_user) REFERENCES users(id_user) "f173_3_109" FOREIGN KEY (id_code_task) REFERENCES codes_tasks(id_code) "f173_4_111" FOREIGN KEY (id_code_pay_type) REFERENCES codes_pay_types(id_code) "f173_5_108" FOREIGN KEY (id_project) REFERENCES projects(id_project) "f173_8_123" FOREIGN KEY (id_time_rec) REFERENCES time_recs(id_time_rec) ON DELETE CASCADE