469,088 Members | 1,248 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,088 developers. It's quick & easy.

unique problem

Hi everyone,

When importing a bunch of data (> 85000 rows) I get an error I can't
explain. The table into which I'm importing has a unique clause on
(code, bedrijf). The rows in the source-table are unique in this
aspect, yet when I do the import I get this "ERROR: duplicate key
violates unique constraint "werknemer_bedrijf_key".

I checked the sourcetable a number of times, even COPYd the relevant
columns to a textfile and did `uniq -d` and `uniq -D` (nothing
non-unique found), tried to delete out non-unique rows (again
nothing found).

Is there a bug in the UNIQUE behaviour? I'm running postgresql
7.4.5-2 (from backports) on a debian stable server. Is there any way
I can DEFER the unique clause, or remove it and put it back later?

Thanks!
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
6 2816
On Mon, Nov 01, 2004 at 04:13:43PM +0100, Joolz wrote:

When importing a bunch of data (> 85000 rows) I get an error I can't
explain. The table into which I'm importing has a unique clause on
(code, bedrijf). The rows in the source-table are unique in this
aspect, yet when I do the import I get this "ERROR: duplicate key
violates unique constraint "werknemer_bedrijf_key".
How are you importing the data? If you use COPY then the error
should show what line is causing the problem, and if you do individual
INSERTs then your import code should be able to recognize the error.
INSERT...SELECT probably won't identify the duplicate record.
I checked the sourcetable a number of times, even COPYd the relevant
columns to a textfile and did `uniq -d` and `uniq -D` (nothing
non-unique found), tried to delete out non-unique rows (again
nothing found).


Did you sort the file before you ran uniq? Duplicate lines need
to be adjacent for uniq to recognize them.

% cat foo
abc
def
abc
% uniq -d foo
% sort foo | uniq -d
abc

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2
On Mon, Nov 01, 2004 at 04:13:43PM +0100, Joolz wrote:

When importing a bunch of data (> 85000 rows) I get an error I can't
explain. The table into which I'm importing has a unique clause on
(code, bedrijf). The rows in the source-table are unique in this
aspect, yet when I do the import I get this "ERROR: duplicate key
violates unique constraint "werknemer_bedrijf_key".
How are you importing the data? If you use COPY then the error
should show what line is causing the problem, and if you do individual
INSERTs then your import code should be able to recognize the error.
INSERT...SELECT probably won't identify the duplicate record.
I checked the sourcetable a number of times, even COPYd the relevant
columns to a textfile and did `uniq -d` and `uniq -D` (nothing
non-unique found), tried to delete out non-unique rows (again
nothing found).


Did you sort the file before you ran uniq? Duplicate lines need
to be adjacent for uniq to recognize them.

% cat foo
abc
def
abc
% uniq -d foo
% sort foo | uniq -d
abc

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3
"Joolz" <jo***@arbodienst-limburg.nl> writes:
Is there a bug in the UNIQUE behaviour?
No known bugs, anyway. I'm inclined to guess that your target table has
slightly different datatypes than the source, and that results in equal
values for some reason (such as fractional values being rounded to
integer, or char vs varchar having different ideas about significance of
trailing blanks).
Is there any way I can DEFER the unique clause, or remove it and put
it back later?


You can always drop and re-add the constraint ... but I'll be pretty
surprised if that gets around the problem (ie, I bet re-adding the
constraint will fail).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4
"Joolz" <jo***@arbodienst-limburg.nl> writes:
Is there a bug in the UNIQUE behaviour?
No known bugs, anyway. I'm inclined to guess that your target table has
slightly different datatypes than the source, and that results in equal
values for some reason (such as fractional values being rounded to
integer, or char vs varchar having different ideas about significance of
trailing blanks).
Is there any way I can DEFER the unique clause, or remove it and put
it back later?


You can always drop and re-add the constraint ... but I'll be pretty
surprised if that gets around the problem (ie, I bet re-adding the
constraint will fail).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #5

Tom Lane zei:
"Joolz" <jo***@arbodienst-limburg.nl> writes:
Is there a bug in the UNIQUE behaviour?


No known bugs, anyway. I'm inclined to guess that your target table
has
slightly different datatypes than the source, and that results in
equal
values for some reason (such as fractional values being rounded to
integer, or char vs varchar having different ideas about
significance of
trailing blanks).
Is there any way I can DEFER the unique clause, or remove it and
put
it back later?


You can always drop and re-add the constraint ... but I'll be pretty
surprised if that gets around the problem (ie, I bet re-adding the
constraint will fail).


You're right, I cannot re-ad the contraint. The insert translates a
column with a subselect to another value (with another datatype).
Before the insert / translation the two columns are unique,
afterwards it appears they are not.

I'll go and have a look what's wrong with the subselect.

Thanks for the reactions so far everyone!
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #6

Tom Lane zei:
"Joolz" <jo***@arbodienst-limburg.nl> writes:
Is there a bug in the UNIQUE behaviour?


No known bugs, anyway. I'm inclined to guess that your target table
has
slightly different datatypes than the source, and that results in
equal
values for some reason (such as fractional values being rounded to
integer, or char vs varchar having different ideas about
significance of
trailing blanks).
Is there any way I can DEFER the unique clause, or remove it and
put
it back later?


You can always drop and re-add the constraint ... but I'll be pretty
surprised if that gets around the problem (ie, I bet re-adding the
constraint will fail).


You're right, I cannot re-ad the contraint. The insert translates a
column with a subselect to another value (with another datatype).
Before the insert / translation the two columns are unique,
afterwards it appears they are not.

I'll go and have a look what's wrong with the subselect.

Thanks for the reactions so far everyone!
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by kevin parks | last post: by
26 posts views Thread by Agoston Bejo | last post: by
2 posts views Thread by reneeccwest | last post: by
9 posts views Thread by Rolf Kemper | last post: by
1 post views Thread by Rajesh Kumar Mallah | last post: by
8 posts views Thread by Marc | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.