473,320 Members | 1,600 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Postgres Newbie - Case Problems in queries

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
4 2881
michaelb
534 Expert 512MB
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
MarkD
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
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
michaelb
534 Expert 512MB
... 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

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

Similar topics

8
by: wlcna | last post by:
mysql v4.0.16: I had been using mysql with innodb and thought that was fine, until i used it for something requiring a few - perhaps slightly involved - joins, and have now seen the performance...
48
by: Edwin Quijada | last post by:
Hi !! Everybody I am developing app using Delphi and I have a question: I have to save pictures into my database. Each picture has 20 o 30k aprox. What is the way more optimus? That 's table will...
7
by: Abdul-Wahid Paterson | last post by:
Hi, I have had a site working for the last 2 years and have had no problems until at the weekend I replace my database server with a newer one. The database migration went like a dream and I had...
3
by: Gaetano Mendola | last post by:
I found this article: http://www.serverwatch.com/news/article.php/10824_1126981_Ext that is clear out dated, it's anyway a good comparison with mysql. Do you know if someone did the TPC-C...
1
by: Hank | last post by:
Hello, We are in the process of migrating our Access back end to Postgres. Our current version is Access 2000. Among other issues, the reason for the change is to pick up some speed by way of...
3
by: Klint Gore | last post by:
Does anyone know of a mailing list for application developers using postgres? It'd probably be more relevant than pgsql-general for my question. Failing that, what do people use to generate...
12
by: Paul Tillotson | last post by:
At my company we are looking at deploying clients for our client/server app outside our firewall, which will then require our postgres box to be internet-accessible. Does anyone out there have...
10
by: Hank | last post by:
We have just recently migrated the data from our Access 2000 backend to Postgres. All forms and reports seem to run correctly but, in many cases, very slowly. We do not want to switch over until...
3
by: fjm67 | last post by:
I am new to PHP but not so new to Postgres. If someone can either direct me to some howto or even provide me with an example, I would be grateful. I would like to know if it is possible to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.