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_bedr ijf_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 YourEmailAddres sHere" to majordomo@postg resql.org)

[Michael Fuhr]

Re: unique problem

On Mon, Nov 01, 2004 at 04:13:43PM +0100, Joolz wrote:[color=blue]
>
> 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_bedr ijf_key".[/color]

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.
[color=blue]
> 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).[/color]

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]

Re: unique problem

On Mon, Nov 01, 2004 at 04:13:43PM +0100, Joolz wrote:[color=blue]
>
> 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_bedr ijf_key".[/color]

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.
[color=blue]
> 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).[/color]

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]

Re: unique problem

"Joolz" <joolz@arbodien st-limburg.nl> writes:[color=blue]
> Is there a bug in the UNIQUE behaviour?[/color]

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).
[color=blue]
> Is there any way I can DEFER the unique clause, or remove it and put
> it back later?[/color]

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]

Re: unique problem

"Joolz" <joolz@arbodien st-limburg.nl> writes:[color=blue]
> Is there a bug in the UNIQUE behaviour?[/color]

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).
[color=blue]
> Is there any way I can DEFER the unique clause, or remove it and put
> it back later?[/color]

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]

Re: unique problem


Tom Lane zei:[color=blue]
> "Joolz" <joolz@arbodien st-limburg.nl> writes:[color=green]
>> Is there a bug in the UNIQUE behaviour?[/color]
>
> 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).
>[color=green]
>> Is there any way I can DEFER the unique clause, or remove it and
>> put
>> it back later?[/color]
>
> 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).[/color]

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]

Re: unique problem


Tom Lane zei:[color=blue]
> "Joolz" <joolz@arbodien st-limburg.nl> writes:[color=green]
>> Is there a bug in the UNIQUE behaviour?[/color]
>
> 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).
>[color=green]
>> Is there any way I can DEFER the unique clause, or remove it and
>> put
>> it back later?[/color]
>
> 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).[/color]

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