Thread: 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 >