PostgreSQL Bugs - BUG #1860: Insert failed due to unique index

Author

BUG #1860: Insert failed due to unique index

Viswanath Ramineni

2005-09-03, 9:24 am

The following bug has been logged online:

Bug reference: 1860
Logged by: Viswanath Ramineni
Email address: v_ramineni@hotmail.com
PostgreSQL version: 8.1 Beta
Operating system: Windows 2000 Professional
Description: Insert failed due to unique index
Details:

I have a table with a primary key on id (integer) column and a unique index
on varchar(10) column. Insertion fails when the value of the unique column
is substring of an existing value. For example, if I have a row with the
unique column value 'TEST1', it will not allow me insert another row with
'TEST' as the value for this column.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Peter Eisentraut

2005-09-03, 8:24 pm

Viswanath Ramineni wrote:
> I have a table with a primary key on id (integer) column and a unique
> index on varchar(10) column. Insertion fails when the value of the
> unique column is substring of an existing value. For example, if I
> have a row with the unique column value 'TEST1', it will not allow me
> insert another row with 'TEST' as the value for this column.

Please show the exact sequence of commands that leads to this situation.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2005-09-04, 8:23 pm

Peter Eisentraut <peter_e@gmx.net> writes:
> Viswanath Ramineni wrote:
[color=darkred]
> Please show the exact sequence of commands that leads to this situation.

This is probably the bug that was introduced here:

2005-08-24 13:50 tgl

* src/backend/utils/adt/: oracle_compat.c, varlena.c: Add small
hack to support use of Unicode-based locales on WIN32. This is not
adequately tested yet, but let's get it into beta1 so it can be
tested. Magnus Hagander and Tom Lane.

and fixed here:

2005-08-26 13:40 tgl

* src/backend/utils/adt/varlena.c: The idea of using _strncoll() on
Windows doesn't work. Revert to same code as we use on other
platforms when encoding is not UTF8.

Non-unicode comparison on Windows was pretty thoroughly broken during
that interval :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links