[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-bugs
Subject: [BUGS] How duplicate data produce when a UNIQUE index exite ?
From: <yongwei_huang () gtmc ! com ! cn>
Date: 2009-11-25 5:56:39
Message-ID: EDA3764BC9DBA043AABB8FF57D73237801595783 () QFWA0016 ! gtmcl ! com
[Download RAW message or body]
Dear all,
I have a table as below:
Table "public.t_sfh_history"
Column | Type | Modifiers
-------------+-----------------------------+-----------
idno | character(10) | not null
lo_date | character(8) | not null
line | character(1) | not null
tp | character(2) | not null
bcno | character(3) | not null
times | character(1) | not null
pdate | timestamp without time zone | not null
cdate | timestamp without time zone | not null
psh | character(1) | not null
hdlytime | numeric(6,0) | not null
hspr_tp | numeric(6,0) | not null
hspr_byo | numeric(6,0) | not null
hspr_deli | numeric(6,0) | not null
ltresult | numeric(6,0) | not null
ltresult_bs | numeric(6,0) | not null
dummy | character(6) | not null
Indexes:
"i_sfh_history0" UNIQUE, btree (idno, lo_date, line, tp, times)
When “reindex” operation do,this error occurred:
Nov 22 10:22:27 SUC11 postgres[14145]: [2-1] ERROR: could not create unique index
Nov 22 10:22:27 SUC11 postgres[14145]: [2-2] DETAIL: Table contains duplicated \
values.
Nov 22 10:22:27 SUC11 postgres[14145]: [2-3] STATEMENT: REINDEX TABLE \
"t_sfh_history"
My question is:
How the Duplicate datas produce when a UNIQUE index exite ?
Is this a bug of PostgreSQL ?
My postgreSQL version is :
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red \
Hat Linux 3.2.3-56)
(1 row)
Thank you very much
Best regards,
Ray Huang
[Attachment #3 (text/html)]
<html xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:st1="urn:schemas-microsoft-com:office:smarttags" \
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="chsdate" downloadurl=""/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:宋体;
panose-1:2 1 6 0 3 1 1 1 1 1;}
@font-face
{font-family:"\@宋体";
panose-1:2 1 6 0 3 1 1 1 1 1;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
text-align:justify;
text-justify:inter-ideograph;
font-size:10.5pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:宋体;
color:navy;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
/* Page Definitions */
@page Section1
{size:595.3pt 841.9pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;
layout-grid:15.6pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=ZH-CN link=blue vlink=purple style='text-justify-trim:punctuation'>
<div class=Section1 style='layout-grid:15.6pt'>
<p class=MsoNormal><b><font size=4 face=宋体><span lang=EN-US style='font-size:
15.0pt;font-family:宋体;font-weight:bold'>Dear all,<o:p></o:p></span></font></b></p>
<p class=MsoNormal><b><font size=4 face=宋体><span lang=EN-US style='font-size:
15.0pt;font-family:宋体;font-weight:bold'> I have a table as \
below:<o:p></o:p></span></font></b></p>
<p class=MsoNormal><font size=3 color=navy face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:navy'>
</span></font><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'>Table \
"public.t_sfh_history"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'>
Column
>
Type |
Modifiers<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'>-------------+-----------------------------+-----------<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> idno
|
character(10)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> lo_date
| character(8)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> line
|
character(1)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> tp
|
character(2)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> bcno
|
character(3)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> times
|
character(1)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> pdate
| timestamp without time zone | not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> cdate
| timestamp without time zone | not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> psh
|
character(1)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> hdlytime
>
numeric(6,0)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> hspr_tp
|
numeric(6,0)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> hspr_byo
>
numeric(6,0)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> hspr_deli
>
numeric(6,0)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> ltresult
>
numeric(6,0)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> ltresult_bs |
numeric(6,0)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'> dummy
| character(6)
| not null<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=blue face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:blue'>Indexes:<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=blue face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:blue'>"i_sfh_history0"
UNIQUE, btree (idno, lo_date, line, tp, times)<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=navy face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><b><font size=5 face=宋体><span
lang=EN-US style='font-size:16.0pt;font-family:宋体;font-weight:bold'>When \
</span></font></b><b><font size=5><span lang=EN-US \
style='font-size:16.0pt;font-weight:bold'>“</span></font></b><b><font size=5 \
face=宋体><span lang=EN-US style='font-size:16.0pt;font-family:宋体; \
font-weight:bold'>reindex</span></font></b><b><font size=5><span lang=EN-US \
style='font-size:16.0pt;font-weight:bold'>”</span></font></b><b><font size=5 \
face=宋体><span lang=EN-US style='font-size:16.0pt;font-family:宋体;font-weight: \
bold'> operation do,this error occurred:<o:p></o:p></span></font></b></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=navy face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=blue face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:blue'>Nov 22 10:22:27
SUC11 postgres[14145]: [2-1] ERROR: could not create unique \
index<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=blue face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:blue'>Nov 22 10:22:27
SUC11 postgres[14145]: [2-2] DETAIL: Table contains duplicated \
values.<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=blue face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:blue'>Nov 22 10:22:27
SUC11 postgres[14145]: [2-3] STATEMENT: REINDEX TABLE
"t_sfh_history"<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=navy face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><b><font size=4 face=宋体><span
lang=EN-US style='font-size:15.0pt;font-family:宋体;font-weight:bold'>My question
is:<o:p></o:p></span></font></b></p>
<p class=MsoNormal style='text-indent:24.0pt'><b><font size=4 face=宋体><span
lang=EN-US style='font-size:15.0pt;font-family:宋体;font-weight:bold'><o:p> </o:p></span></font></b></p>
<p class=MsoNormal style='text-indent:24.0pt'><b><font size=4 face=宋体><span
lang=EN-US style='font-size:15.0pt;font-family:宋体;font-weight:bold'>How the \
Duplicate datas produce when a UNIQUE index exite \
?<o:p></o:p></span></font></b></p>
<p class=MsoNormal style='text-indent:24.0pt'><b><font size=4 face=宋体><span
lang=EN-US style='font-size:15.0pt;font-family:宋体;font-weight:bold'>Is this a
bug of PostgreSQL ?<o:p></o:p></span></font></b></p>
<p class=MsoNormal style='text-indent:24.0pt'><b><font size=4 face=宋体><span
lang=EN-US style='font-size:15.0pt;font-family:宋体;font-weight:bold'>My
postgreSQL version is :<o:p></o:p></span></font></b></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=navy face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:navy'> &nbs \
p; \
&n \
bsp;
version<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=navy face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:navy'>---------------------- \
------------------------------------------------------------------------------------<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=navy face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:navy'> PostgreSQL \
<st1:chsdate IsROCDate="False" IsLunarDate="False" Day="30" Month="12" Year="1899" \
w:st="on">8.1.4</st1:chsdate> on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 \
20030502 (Red Hat Linux 3.2.3-56)<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=navy face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:navy'>(1 \
row)<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=navy face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-indent:24.0pt'><font size=3 color=navy face=宋体><span
lang=EN-US style='font-size:12.0pt;font-family:宋体;color:navy'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=navy face=宋体><span lang=EN-US
style='font-size:12.0pt;font-family:宋体;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Times New Roman"><span
lang=EN-US style='font-size:10.5pt;color:navy'> \
</span></font><b><font size=4><span lang=EN-US \
style='font-size:15.0pt;font-weight:bold'>Thank you very \
much</span></font></b><b><font size=4 color=navy><span lang=EN-US \
style='font-size:15.0pt;color:navy;font-weight:bold'><o:p></o:p></span></font></b></p>
<p class=MsoNormal><b><font size=4 color=navy face="Times New Roman"><span
lang=EN-US style='font-size:15.0pt;color:navy;font-weight:bold'> <o:p></o:p></span></font></b></p>
<p class=MsoNormal><b><font size=4 color=black face="Times New Roman"><span
lang=EN-US style='font-size:15.0pt;color:black;font-weight:bold'>Best \
regards,</span></font></b><b><font size=4 color=black><span lang=EN-US \
style='font-size:15.0pt;color:black; \
font-weight:bold'><o:p></o:p></span></font></b></p>
<p class=MsoNormal><b><font size=4 face="Times New Roman"><span lang=EN-US
style='font-size:15.0pt;font-weight:bold'>Ray Huang<o:p></o:p></span></font></b></p>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic