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

=20

> -----Original Message-----
> From: pgsql-bugs-owner@postgresql.org=20
> [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Bruno Wolff III
> Sent: Friday, June 03, 2005 11:58 PM
> To: Mauro Delfino
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] BUG #1698: Different behavior in UNIQUE=20
> and DISTINCT
>=20
> On Thu, Jun 02, 2005 at 20:22:07 +0100,
>   Mauro Delfino <maurodelfino@gmail.com> wrote:
> >=20
> > I have the these two tables:
> > CREATE TABLE table_one
> > ( field1 VARCHAR(255) );
> >=20
> > CREATE TABLE table_two
> > ( field1 VARCHAR(255) UNIQUE );
> >=20
> > table_one has 500k records. I certify that all strings are distinct=20
> > with this query:
> > SELECT DISTINCT field1 FROM table_one; The query results 500k rows.
> >=20
> > But if I try to insert the records of table_one into table_two with=20
> > the following command:
> > INSERT INTO table_two (field1) (SELECT field1 FROM table_one); This=20
> > error occurs:
> > ERROR:  duplicate key violates unique constraint=20
> "table_two_field1_key"
> >=20
> > What happened? DISTINC and UNIQUE have different algorithms to=20
> > determine when two strings are equal?
>=20
> Are you sure table_two is empty when you do this?
>=20

And, are these the _actual_ tables?
If not, try SELECT DISTINCT ON (field1) field1 FROM table_one; instead.

> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to=20
> majordomo@postgresql.org
>=20
>=20

... John