Hello.
Facts:
1. System: Win2003
2. Postgres 8.2
3. Use pgAdmin 1.6.2, rev.5837 to administer the database
3. Table:
CREATE TABLE mt
(
suid character varying(70) NOT NULL DEFAULT 'suid'::character varying,
ris_match_row_idx integer DEFAULT -1,
ris_match_rule smallint DEFAULT 999,
stulev_match_row_idx integer DEFAULT -1,
sdate character varying(8)
)
4. Constraint:
ALTER TABLE mt
ADD CONSTRAINT suid_uniq UNIQUE(suid);
5. Action : using plpgsql - populate the table with *what I think are*
unique SUID values. No conflicts reported.
Now, here's where the confusion starts
7. Count the number of rows in the table, using pgADmin's "count"
functionality, from the table's context menu. Result: 2,768,862 rows
8. Count the number of rows, by counting the number of SUIDs:
select count (suid) from migratek.mt - returns 2,768,862
so far so good.
but
9. Count the number of distinct values. This should be the same with the
number of rows, since there is a unique constraint there right?
select count (distinct suid) from migratek.mt - returns 2,766,333
10. pull hair off my head
11. try to figure for 10 hours what's going on in joins involving this
table. Then realize that the unique entries in the mt table are not unique.
Does anybody have a clue on how a table with an "unique" constraint can
foster duplications? OR is is it a bug in the *distinct* algorithm
postgres uses?
Does the usage of a plpgsql function (1 large transaction) to populate a
table that has constraints has anything to do with the checking of
constraints in that table?
This has way too many ramifications for me to follow so I do appreciate
some guidance.
Razvan