BUG #1698: Different behavior in UNIQUE and DISTINCT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #1698: Different behavior in UNIQUE and DISTINCT

Mauro Delfino

The following bug has been logged online:

Bug reference:      1698
Logged by:          Mauro Delfino
Email address:      [hidden email]
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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

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

Bruno Wolff III
On Thu, Jun 02, 2005 at 20:22:07 +0100,
  Mauro Delfino <[hidden email]> 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?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

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

John Hansen
In reply to this post by Mauro Delfino
 

> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Bruno Wolff III
> Sent: Friday, June 03, 2005 11:58 PM
> To: Mauro Delfino
> Cc: [hidden email]
> Subject: Re: [BUGS] BUG #1698: Different behavior in UNIQUE
> and DISTINCT
>
> On Thu, Jun 02, 2005 at 20:22:07 +0100,
>   Mauro Delfino <[hidden email]> 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
> [hidden email]
>
>

... John

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

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

Mauro Delfino
In reply to this post by Bruno Wolff III
Yes I'm sure. I was created right before filling it.
Thanks,
Mauro
 
2005/6/3, Bruno Wolff III <[hidden email]>:
On Thu, Jun 02, 2005 at 20:22:07 +0100,
Mauro Delfino <[hidden email]> 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?



--
Mauro Delfino
[hidden email]
(48) 9933-6933
(MCDBA/MCSE/MCP+I/IBM Lotus CLS)
Microsoft SQL Server Official Beta Tester
Reply | Threaded
Open this post in threaded view
|

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

Klint Gore
On Mon, 13 Jun 2005 10:12:38 -0300, Mauro Delfino <[hidden email]> 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   : [hidden email]           :  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