Hi all, I don't know if it's a bug or not...but things are quite strange for me.My problem comes from a unique constraint violation whereas tha data I try to insert in my table are different (at least for me). My database is encoded using SQL_ASCII, postgresql 7.4.8 on a Red Hat Advanced Server v3 or 4)
I created a table : CREATE TABLE trace_object ( object_id serial NOT NULL, object_barcode character varying(15) NOT NULL, object_barcode_128 character varying(25), );ALTER TABLE ONLY trace_object ADD CONSTRAINT pk_trace_object PRIMARY KEY (object_id); ALTER TABLE ONLY trace_object ADD CONSTRAINT trace_object_object_barcode_key UNIQUE (object_barcode); ALTER TABLE ONLY trace_object ADD CONSTRAINT unique_barcode128 UNIQUE (object_barcode_128);
The column "object_barcode" contains human readable barcode (e.g. AB28662097) and the column "object_barcode_128" contains the "crypted" barcode readable by LASER scanners once printed with the corresponding font. We can determine the object_barcode_128 content, applying a function on object_barcode (you can find it here : http://grandzebu.net/informatique/codbar/code128_PLpgSQL.asc, sorry the comments are in french). Let's call this function text2code128().
If I do :INSERT INTO trace_object (object_barcode, object_barcode_128) VALUES ('AB28662097', text2code128('AB28662097')); INSERT INTO trace_object (object_barcode, object_barcode_128) VALUES ('AB28662098', text2code128('AB28662098'));
I get the error : ERROR: duplicate key violates unique constraint "unique_barcode128" But the string returned by text2code128('AB28662097') and text2code128('AB28662098') are different!!!, i.e., respectively ÌABÇ<b4ÅÃÎ and ÌABÇ<b4ÆÊÎ.
Why do I get an error here? I really don't understand...I get this error using my cgi interface, phpPgAdmin and command line.
Some other things :If I drop the unique constraint unique_barcode128, I can insert my previous data. Then if the request is : SELECT object_barcode_128 FROM trace_object WHERE object_barcode_128 = (SELECT text2code128('AB28662098'))
the two rows are returned ('ÌABÇ<b4ÅÃÎ' and 'ÌABÇ<b4ÆÊÎ').
if my request is :
SELECT object_barcode_128 FROM trace_object WHERE object_barcode_128 LIKE
(SELECT text2code128('AB28662098'))
I get one row 'ÌABÇ<b4ÆÊÎ' could you please help me understanding what happens....I know that the unique index is created using B-TREE (CREATE UNIQUE INDEX unique_barcode128 ON trace_object USING btree (object_barcode_128)). Is there a way to have a look at the content of this index? Do you know how it works and where I can find more information abour it?
thank you for your help Gérald _________________________________________________________________ Gagnez des pc Windows Vista avec Live.com http://www.image-addict.fr/
