On Mon, 13 Jun 2005 10:12:38 -0300, Mauro Delfino <[EMAIL PROTECTED]> 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 : [EMAIL PROTECTED] : 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 : :
+---------------------------------------+-----------------+
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
