> -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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 > and DISTINCT > > On Thu, Jun 02, 2005 at 20:22:07 +0100, > 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 ); > > > > 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 > [EMAIL PROTECTED] > > ... John ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
