Skip site navigation (1) Skip section navigation (2)

Re: BUG #1698: Different behavior in UNIQUE and DISTINCT

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Mauro Delfino <maurodelfino(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1698: Different behavior in UNIQUE and DISTINCT
Date: 2005-06-03 13:58:28
Message-ID: 20050603135828.GA8358@wolff.to (view raw or flat)
Thread:
Lists: pgsql-bugs
On Thu, Jun 02, 2005 at 20:22:07 +0100,
  Mauro Delfino <maurodelfino(at)gmail(dot)com> wrote:
> 
> I have the these two tables:
> CREATE TABLE table_one
> ( field1 VARCHAR(255) );
> 
> CREATE TABLE table_two
> ( field1 VARCHAR(255) UNIQUE );
> 
> table_one has 500k records. I certify that all strings are distinct with
> this query:
> SELECT DISTINCT field1 FROM table_one;
> The query results 500k rows.
> 
> But if I try to insert the records of table_one into table_two with the
> following command: 
> INSERT INTO table_two (field1) (SELECT field1 FROM table_one);
> This error occurs:
> ERROR:  duplicate key violates unique constraint "table_two_field1_key"
> 
> What happened? DISTINC and UNIQUE have different algorithms to determine
> when two strings are equal?

Are you sure table_two is empty when you do this?

In response to

Responses

pgsql-bugs by date

Next:From: Michael FuhrDate: 2005-06-03 14:03:39
Subject: Re: BUG #1700: Error on Subselect with date in where condition
Previous:From: MichaelDate: 2005-06-03 12:47:46
Subject: BUG #1700: Error on Subselect with date in where condition

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group