From: | "John Hansen" <john(at)geeknet(dot)com(dot)au> |
---|---|
To: | "Bruno Wolff III" <bruno(at)wolff(dot)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 14:10:51 |
Message-ID: | 5066E5A966339E42AA04BA10BA706AE50A9330@rodrick.geeknet.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
> -----Original Message-----
> From: pgsql-bugs-owner(at)postgresql(dot)org
> [mailto:pgsql-bugs-owner(at)postgresql(dot)org] On Behalf Of Bruno Wolff III
> Sent: Friday, June 03, 2005 11:58 PM
> To: Mauro Delfino
> Cc: pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] BUG #1698: Different behavior in UNIQUE
> and DISTINCT
>
> 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?
>
And, are these the _actual_ tables?
If not, try SELECT DISTINCT ON (field1) field1 FROM table_one; instead.
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>
>
... John
From | Date | Subject | |
---|---|---|---|
Next Message | Haluk GÜNÇER | 2005-06-03 14:21:38 | Re: BUG #1678: pw_shadow BUS ERROR |
Previous Message | Michael Fuhr | 2005-06-03 14:03:39 | Re: BUG #1700: Error on Subselect with date in where condition |