Postgres Pro
Downloads
Home   >   mailing lists

Thread: non unique value error... pgsql,,help needed.

non unique value error... pgsql,,help needed.

From
Shri
Date:
Dear friends,

I am trying to import db from oracle to pgsql,

meanwhile facing a difficulty during importing oracle numeric(38) to pgsql numeric(38) column, Because in oracle that column is unique, but PGsql not able to correctly identify different and unique values with numeric(38) field.

for eg.

column: a numeric(38) primary

giving me error of non unique value in following two data.
1. 111122223333444455556666777788889999
2. 111122223333444455556666777788889997  (<----- both are different.)

When i enter first data into column (using EMS SQL MANAGER),it converts it to 1.11122223333444E35
and same way second data is converted to the same, so both got matches and it gives me error of non unique rows.

can somebody please advise how to tackle with this,, as this column will be used to join to another table,, i cant convert it to varchar,

is this happening due to some fault in pgsql ? EMS SQL MANAGER ? or  ME :-) 



Re: non unique value error... pgsql,,help needed.

From
"A. Kretschmer"
Date:
In response to Shri :
> Dear friends,
> 
> I am trying to import db from oracle to pgsql,
> 
> meanwhile facing a difficulty during importing oracle numeric(38) to pgsql
> numeric(38) column, Because in oracle that column is unique, but PGsql not able
> to correctly identify different and unique values with numeric(38) field.
> 
> for eg.
> 
> column: a numeric(38) primary
> 
> giving me error of non unique value in following two data.
> 1. 111122223333444455556666777788889999
> 2. 111122223333444455556666777788889997  (<----- both are different.)
> 
> When i enter first data into column (using EMS SQL MANAGER),it converts it
> to 1.11122223333444E35

Can you try to insert such values with plain psql? I have tried that and
it works without trouble:

test=# create table foo (id serial, n numeric(38));
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"
CREATE TABLE
test=*# insert into foo (n) values (111122223333444455556666777788889999);
INSERT 0 1
test=*# select * from foo;id |                  n
----+-------------------------------------- 1 | 111122223333444455556666777788889999
(1 row)

test=*# insert into foo (n) values (111122223333444455556666777788889997);
INSERT 0 1
test=*# select * from foo;id |                  n
----+-------------------------------------- 1 | 111122223333444455556666777788889999 2 |
111122223333444455556666777788889997
(2 rows)

test=*# 


Which version do you have?

I think, it's a problem with EMS SQL MANAGER, but i don't know that tool.



Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: non unique value error... pgsql,,help needed.

From
Tom Lane
Date:
Shri <shripatel@gmail.com> writes:
> When i enter first data into column (using EMS SQL MANAGER),it converts it
> to 1.11122223333444E35

I think you need to complain to EMS.
        regards, tom lane

PS: this is not a hacker-grade question.