PostgreSQL Bugs - BUG #1698: Different behavior in UNIQUE and DISTINCT

Author

BUG #1698: Different behavior in UNIQUE and DISTINCT

Mauro Delfino

2005-06-03, 9:24 am

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.

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

Bruno Wolff III

2005-06-03, 9:24 am

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?

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

John Hansen

2005-06-03, 9:24 am

> -----Original Message-----
> From: pgsql-bugs-owner@postgresql.org
> [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
> and DISTINCT
>
> On Thu, Jun 02, 2005 at 20:22:07 +0100,
> Mauro Delfino <maurodelfino@gmail.com> wrote:
> " table_two_field1_key
"
>
> 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@postgresql
.org
>
>

.... John

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

Mauro Delfino

2005-06-13, 8:24 pm

Yes I'm sure. I was created right before filling it.
Thanks,
Mauro
2005/6/3, Bruno Wolff III <bruno@wolff.to>:
>
> On Thu, Jun 02, 2005 at 20:22:07 +0100,
> Mauro Delfino <maurodelfino@gmail.com> wrote:
>
> Are you sure table_two is empty when you do this?
>

--
Mauro Delfino
maurodelfino@gmail.com
(48) 9933-6933
(MCDBA/MCSE/MCP+I/IBM Lotus CLS)
Microsoft SQL Server Official Beta Tester

Klint Gore

2005-06-14, 3:24 am

On Mon, 13 Jun 2005 10:12:38 -0300, Mauro Delfino <maurodelfino@gmail.com> wrote:[color=darkred
]

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 : :
+---------------------------------------+-----------------+

---------------------------(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

Sponsored Links