Re: 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)