Thread: BUG #1698: Different behavior in UNIQUE and DISTINCT
The following bug has been logged online: Bug reference: 1698 Logged by: Mauro Delfino Email address: maurodelfino@gmail.com PostgreSQL version: 7.4.6 and 8.0.3 Operating system: Debian and Windows 2003 Server Description: Different behavior in UNIQUE and DISTINCT Details: 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? PS: I've tested this situation on PG 7.4.4 on Debian and 8.0.3 on Windows 2003 Server. PS2: Database encoding is LATIN1 Thanks in advance.
On Thu, Jun 02, 2005 at 20:22:07 +0100, 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 ); > > 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?
=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
Yes I'm sure. I was created right before filling it. Thanks, Mauro 2005/6/3, Bruno Wolff III <bruno@wolff.to>:=20 >=20 > On Thu, Jun 02, 2005 at 20:22:07 +0100, > 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 ); > > > > 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? >=20 > Are you sure table_two is empty when you do this? >=20 --=20 Mauro Delfino=20 maurodelfino@gmail.com=20 (48) 9933-6933 (MCDBA/MCSE/MCP+I/IBM Lotus CLS)=20 Microsoft SQL Server Official Beta Tester
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 : : +---------------------------------------+-----------------+