ansaurus

Question

Answer 1

+2 A:

A double is inexact, that's why this might happen. Use an exact datatype and you won't have problems like this.

Frank Heikens 2010-08-17 06:21:20

Although this doesn't solve my problem, it's a really good point. I will consider changing the database to use NUMERIC. Thanks.

ionut bizau 2010-08-17 07:54:59

Wouldn't for example 45.454545454 always result in the same inexact value therefor it would result in the same inexact number causing two rows columns to be the same? Or are you saying 45.454545454 will result in 45.455 and sometimes 45.454? Of course the numbers I'm using is just for discussion purposes.

StarShip3000 2010-08-18 16:56:51

Answer 2

+2 A:

because two NULLs don't compare as equal, they play funny games with UNIQUE constraints.

See last paragraph of UNIQUE constraints in the PostgreSQL documentation:

In general, a unique constraint is violated when there are two or more rows in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns.

Damien_The_Unbeliever 2010-08-17 06:31:32

Damn, this sucks. I knew NULL is not equal to NULL, but for some reason I was expecting it to be different in the index. :)

ionut bizau 2010-08-17 07:56:00

@ionut bizau - of course, the real fun is that, for GROUPing purposes, NULLs are grouped together. And as the documentation states, some other servers (e.g. SQL Server) do implement UNIQUE constraints only allowing a single NULL (although this violates the ANSI SQL spec)

Damien_The_Unbeliever 2010-08-17 07:58:28

ansaurus

tags:

views:

answers:

PostgreSQL UNIQUE index not unique?!

related questions