Обсуждение: 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.







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.

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
>