Обсуждение: Case-sensitive problem
Hi I am trying to migrate MS SQL 6.5 database server to the postgresql database )7.1). I have my program made in Power Builder and this program is working without problems with MS SQL server. But when I tried to change the database to the PostgreSQL I received following errors: 1. The program(Power Builder) is not recognizing more big and small letters (case sensitivity). 2. There is also problem with spaces ,with that is also having PosgreSQL problems. How can I fix that problem with letters? Is there any special way how to build PostgreSQL ,so it is working well with PowerBuilder and especially with small and big letters. The PostgreSQL is running on Linux and PowerBuilder on Windows. Thanks for all your answers
igor wrote: > Hi > > I am trying to migrate MS SQL 6.5 database server to the postgresql > database )7.1). I have my program made in Power Builder and this > program is working without problems with MS SQL server. But when I > tried to change the database to the PostgreSQL I received following > errors: > 1. The program(Power Builder) is not recognizing more big and small > letters (case sensitivity). > 2. There is also problem with spaces ,with that is also having > PosgreSQL problems. > > How can I fix that problem with letters? Is there any special way how > to build PostgreSQL ,so it is working well with PowerBuilder and > especially with small and big letters. > > The PostgreSQL is running on Linux and PowerBuilder on Windows. > > Thanks for all your answers Hi, sorry, but your description is not very clear. What problems do you have exactly? For case sensitivity I just know, that MS SQL's behaviour is very strange by default. So ('CaseSensiTive' = 'casesENsitiVE' ) would be true with MS SQL. But this is obviouliy not true to me (and to postgresql too). If this is your problem, you have to test ( upper(text1) = upper(text2)). And for the spaces .. maybe you should check your datatypes. If you have an Varchar(5), 'abc' would be stored as 'abc'. But if you have a char(5), 'abc' would be stored as 'abc '. Andre
> > Hi > > > > I am trying to migrate MS SQL 6.5 database server to the postgresql > > database )7.1). I have my program made in Power Builder and this > > program is working without problems with MS SQL server. But when I > > tried to change the database to the PostgreSQL I received following > > errors: > > 1. The program(Power Builder) is not recognizing more big and small > > letters (case sensitivity). > > 2. There is also problem with spaces ,with that is also having > > PosgreSQL problems. > > > > How can I fix that problem with letters? Is there any special way how > > to build PostgreSQL ,so it is working well with PowerBuilder and > > especially with small and big letters. > > > > The PostgreSQL is running on Linux and PowerBuilder on Windows. > > > > Thanks for all your answers > > Hi, > > sorry, but your description is not very clear. What problems do you have > exactly? > For case sensitivity I just know, that MS SQL's behaviour is very > strange by default. So ('CaseSensiTive' = 'casesENsitiVE' ) would be > true with MS SQL. But this is obviouliy not true to me (and to > postgresql too). If this is your problem, you have to test > ( upper(text1) = upper(text2)). > > And for the spaces .. maybe you should check your datatypes. > If you have an Varchar(5), 'abc' would be stored as 'abc'. But if you > have a char(5), 'abc' would be stored as 'abc '. So ,exactly what I am doing is to migrate all database (MS SQL) to the PostgreSQL server also with the dates. But I am still receiving one strange problem: When some datatype is defined in MS SQL for example like username char(15) and the data there is for example:'peter',then what I receive in PostgreSQL is 'peter ' so exactly 15 characters. When I make SELECT * FROM users where username='peter' I am not receiving no-one row ,but when I type SELECT * FROM users where username='peter ' then I receive whole row. The migration tool which I am using is pgAdmin II and DataBase Migration Wizard ,and there I defined change type char from MSSQL to char in PostgreSQL. When I try change char to varchar ,or to text I am still receiving the same. Can somebody please help me with that problem? Thanks a lot
On 28 Jun 2002, igor wrote: > So ,exactly what I am doing is to migrate all database (MS SQL) to the > PostgreSQL server also with the dates. But I am still receiving one > strange problem: When some datatype is defined in MS SQL for example > like username char(15) and the data there is for example:'peter',then > what I receive in PostgreSQL is 'peter ' so exactly 15 > characters. When I make SELECT * FROM users where username='peter' I > am not receiving no-one row ,but when I type SELECT * FROM users where > username='peter ' then I receive whole row. The migration tool > which I am using is pgAdmin II and DataBase Migration Wizard ,and > there I defined change type char from MSSQL to char in PostgreSQL. > When I try change char to varchar ,or to text I am still receiving the > same. > > Can somebody please help me with that problem? > Thanks a lot Hi Igor, I just tried this in postgresql 7.2.1 and it seems like it auto-chopped the char(10) field I was using for testing, so my advice would be to first try the latest version of postgresql and see if that helps. -- "Force has no place where there is need of skill.", "Haste in every business brings failures.", "This is the bitterest pain among men, to have much knowledge but no power." -- Herodotus
igor dijo: > So ,exactly what I am doing is to migrate all database (MS SQL) to the > PostgreSQL server also with the dates. But I am still receiving one > strange problem: When some datatype is defined in MS SQL for example > like username char(15) and the data there is for example:'peter',then > what I receive in PostgreSQL is 'peter ' so exactly 15 > characters. When I make SELECT * FROM users where username='peter' I > am not receiving no-one row ,but when I type SELECT * FROM users where > username='peter ' then I receive whole row. Well, as you know already, the correct datatype for the username should be varchar. And after that you have to correct the values in the columns, trimming the spaces at the end: 'peter ' is the same in varchar() and in char(); it's 'peter' that's different, because char() appends spaces at insertion time. I think you can correct them with UPDATE users SET username=trim(username) -- Alvaro Herrera (<alvherre[a]atentus.com>) "Y eso te lo doy firmado con mis lagrimas" (Fiebre del loco)