unique problem

[Joolz]

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)

[Michael Fuhr]

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

[Michael Fuhr]

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

[Tom Lane]

"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

[Tom Lane]

"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

[Joolz]

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

[Joolz]

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