>>>>>>Hello.
>>>>>>We are in the planning stage to move our ERP from SQL Server to PostgreSql.
>>>>>>We rely heavily in Stored Procedures and remote views and want to change as little code as possible.
>>>>>>We have ported our databases to PostgreSQL (now we are doing the pk replacements) and would like to know any tips or experiences you have about this.
>>>>>
>>>>>While I haven't done a migration, I've been spending some time with PostgreSQL lately. The first big hurdle, in my view, is that it's case-sensitive for table and field names. Best practice is to make everything lowercase, since that's the default. If not, you have to live in a world of double-quotes.
>>>>>
>>>>>Tamar
>>>>
>>>>Use postix regular expressions to do case insensitive searches. select ... lastname ~* '^my name$'
>>>>
>>>>
https://www.postgresql.org/docs/9.3/static/functions-matching.html>>>
>>>What I was talking about is that if your field is called Name, you can't just SELECT name or even, for that matter, SELECT Name, you have to SELECT "Name"
>>>
>>>Tamar
>>
>>Column names are not case sensitive unless the "name" is quoted. I just tried this on my DB.
>>
>>select firstname,lastname from names;
>>select firstName,lastname from names;
>>select firstname,Lastname from names
>>select "firstname","Lastname" from names --> this one fails
>>select "firstname","lastname" from names
>>
>
>That's true as long as the tables are created with lowercase column names. Try a table with mixed-case columns.
>
>Tamar
I learned something new. What surprised me was the only way to create a mixed case column name is to quote it. This script creates all lower case column names.
CREATE TABLE public._coupcount
(
count integer,
myTest character varying
)
What is ever worse is this script is valid.
CREATE TABLE public._coupcount
(
count integer,
"myTest" character varying,
mytest character varying
)
Thanks for pointing this out.