index help for uuid datatype
I am testing the uuid datatype with unique indexing.
I have the following script to generate a table with uuid types:
create table guid(
pk uuid primary key default new_guid(),
f1 varchar(38)
);
insert into guid(f1) values('bla bla');
insert into guid(f1) values('bla bla');
insert into guid(f1) select f1 from guid;
-- I repeat the insert above 10 times to get may records.
-- then save the generated guid into the varchar field
update guid set f1=pk;
-- check for duplicates in the varchar field
select f1,count(*) from guid group by f1 having(count(f1) > 1);
-- no duplicates there:
f1 | count
----+-------
(0 rows)
-- and here it comes.........
dev=# create unique index idx1 on guid using btree (pk);
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
I must be doing something very wrong.....
Does anyone ever seen such a thing?
Regards,
Gevik.
Gevik Babakhani <pgdev@xs4all.nl> writes:
I must be doing something very wrong.....
Does anyone ever seen such a thing?
Your comparison functions for uuid are inconsistent.
regards, tom lane
Thank you Tom :)
I was doing strncmp at some point but it did not work because
of the '\0'. I have created a custom comparison function and it seems to
work. I am now inserting 6 million records to see if it will break again
and start the other tests from scratch.
Thank you for your help.
Show quoted text
On Sat, 2006-09-16 at 12:43 -0400, Tom Lane wrote:
Gevik Babakhani <pgdev@xs4all.nl> writes:
I must be doing something very wrong.....
Does anyone ever seen such a thing?Your comparison functions for uuid are inconsistent.
regards, tom lane
Gevik Babakhani <pgdev@xs4all.nl> writes:
I was doing strncmp at some point but it did not work because
of the '\0'. I have created a custom comparison function and it seems to
work.
Perhaps you just need memcmp instead of strncmp?
regards, tom lane
I followed your advice.... 6 million records are inserted without any
problems :)
Thank you.
Show quoted text
On Sat, 2006-09-16 at 14:03 -0400, Tom Lane wrote:
Gevik Babakhani <pgdev@xs4all.nl> writes:
I was doing strncmp at some point but it did not work because
of the '\0'. I have created a custom comparison function and it seems to
work.Perhaps you just need memcmp instead of strncmp?
regards, tom lane