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

On Mon, 13 Jun 2005 10:12:38 -0300, Mauro Delfino <maurodelfino@gmail.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@kgb.une.edu.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             :                 :
+---------------------------------------+-----------------+