Skip site navigation (1) Skip section navigation (2)

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

From: Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au>
To: Mauro Delfino <maurodelfino(at)gmail(dot)com>
Cc: Bruno Wolff III <bruno(at)wolff(dot)to>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1698: Different behavior in UNIQUE and DISTINCT
Date: 2005-06-14 01:53:58
Message-ID: 42AE38B669.B5F3KG@129.180.47.120 (view raw or flat)
Thread:
Lists: pgsql-bugs
On Mon, 13 Jun 2005 10:12:38 -0300, 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 );
> > >
> > > 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(at)kgb(dot)une(dot)edu(dot)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             :                 :
+---------------------------------------+-----------------+

In response to

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2005-06-14 03:08:17
Subject: Re: BUG #1707: statistics collector starts with stats_start_collector
Previous:From: Oliver JowettDate: 2005-06-13 21:57:01
Subject: Re: BUG #1712: JDBC column precision for bigint is 0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group