By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,326 Members | 2,873 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,326 IT Pros & Developers. It's quick & easy.

Postgres Newbie - Case Problems in queries

P: 3
Hi,

I'm sure I'm asking a very easy question, but I am currently in the process of migrating from MS SQL to PostgreSQL and I am facing a few issues, which I would appreciate your help/advice on :

1. Is there a way to overcome what seems to be everything turning to lowercase in a query? For example this was the original stored procedure in MS SQL :

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE sp_GetMessages
  2. @UserID Int,@ReadStatus1 int,@ReadStatus2 int
  3. AS
  4. SELECT messaging.id,MsgRead,MsgSubject,MsgAction,UserID,SenderID,nick_name,priority,MsgBody
  5. FROM messaging LEFT JOIN user_profiles ON messaging.SenderID=user_profiles.id
  6. WHERE UserID=@UserID AND (MsgRead=@ReadStatus1 OR MsgRead=@ReadStatus2)
  7. ORDER BY messaging.id DESC
Now I know that the syntax will change slightly and I have already tried to change the code for this, but it always gives me errors, no matter how hard I try to correct it. For example it will change every mixedcase field name to lowercase - can you preserve this, or do I change every field name to lower case?

2. Secondly is it actually better to use stored functions, or am I better off using normal SQL queries?

In case it helps, I am running a VB frontend to access this database.

Thanks for any advice you can give.
Sparky.
Feb 9 '07 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 534
By default Postgres is not case-sensitive, so these two queries are both valid:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Select FirstName, LastName StudentID from StudentRegistry;
  3. select firstname, lastname studentid from studentregistry;
  4.  
  5.  
You can change this behavior if you create a table using double-quotes around the table and field names:

Expand|Select|Wrap|Line Numbers
  1.  
  2. create table "StudentRegistry" (
  3.    -- fields omitted
  4.   "FirstName"  varchar(64) not null,
  5.   "LastName"   varchar(64) not null
  6. );
  7.  
  8.  
This is rarely justified and it makes life more difficult, because you would have to use double-quotes in your query as well:

Expand|Select|Wrap|Line Numbers
  1.  
  2. select "FirstName", "LastName"  from "StudentRegistry";
  3.  
  4.  
P.S. Hope this helps, but if you're running one of the latest versions you may want to check the docs on potential changes to this behavior.
Feb 9 '07 #2

P: 4
Just a thought but do you mean you want to preserve the case so that the client app can bind to the results correctly without having to change everything to lowercase in the client?

If so I had a similar problem with migrating a MySQL db to postgres. To resolve the issue I just use the AS to alias the fields, example:

SELECT Hello FROM table1; -- reference to "Hello" can fail

SELECT Hello AS "Hello" FROM table1; -- references to "Hello" work

Thanks.
Feb 10 '07 #3

P: 3
Hi,

Thanks for the replies - it explains a few things for me. The case was causing me grief, but in the end I simply went and changed the tables and the fields to lowercase throughout - as there werent many it wasnt too bad.

I think I understand what you were saying, and it's nice to know I wasnt the first person to have had problems with it.

Cheers,
Sparky
Feb 15 '07 #4

Expert 100+
P: 534
... I simply went and changed the tables and the fields to lowercase throughout ...
This is exactly what I was subtly suggesting!
Feb 15 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.