Unique is non unique; no nulls
От | Razvan Costea-Barlutiu |
---|---|
Тема | Unique is non unique; no nulls |
Дата | |
Msg-id | 46F557D1.8030004@laitek.com обсуждение исходный текст |
Ответы |
Re: Unique is non unique; no nulls
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: