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

On Mon, 13 Jun 2005 10:12:38 -0300, 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 );
> > >
> > > SELECT DISTINCT field1 FROM table_one;
> > > The query results 500k rows.
> > >
> > > 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?

Did you forget to put distinct in your insert select? table1.field1 is
not unique so it allows duplicates in table_one.

Try "select field1 from table_one group by field1 having count(*) > 1"
and see if it gives you any results.

If you do get results, then you need to put the distinct into the insert
statement.

INSERT INTO table_two (field1) (SELECT distinct field1 FROM table_one);

klint.

+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg(at)kgb(dot)une(dot)edu(dot)au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+