Strange query problem... - Mailing list pgsql-admin

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