I recently switched a database server from Microsoft SQL Server over to PostgreSQL. Here are the top three differences in SQL:
- NO TOP, so
SELECT TOP 10 * FROM table, becomesSELECT * FROM table LIMIT 10you can also use the maxrows attribute of CFQUERY to do this, if you want cross db code (which is good). MySQL also uses the LIMIT sytax, but Oracle uses yet another syntax -
LIKE statements are case sensitive in postgresql, they can be made case insensitive like this:
SELECT * FROM table WHERE LOWER(column) LIKE '%#LCase(var)#%'(Or you can use the ILIKE operator) -
The plus operator cannot be used for concatination so
SELECT firstname + ' ' + lastname AS fullnamebecomesSELECT firstname || ' ' || lastname AS fullnamethis way works on both servers.
Comments
is there a lot of differences swicthing postgresql to sql-serve?
Oh, wow :) Recently started my first project built entirely on Open Source tools etc... I've been a MS SQL programmer for years now, and am impressed with PostgreSQL's ability to equal the production level of MS SQL. However, the learning curve for such a switch is slightly more steep than to MySQL, but this makes it clear how good PostgreSQL is for what I require. These three differences, are the features I probably use the most when working with data directly. Thanks!
I would argue against your statement saying "cross db is good, use maxrows". In general, yes, making your sql cross db is a good idea. However, MAXROWS should almost never be used. If your query returns a million rows, and you use MAXROWS=10, CF still has to fetch all those rows and trim off the ones you don't want. MAXROWS does nothing for you performance wise. (Maybe not nothing, but still.) I'd always urge folks to use the features of the db to limit db rows, not MAXROWS. I'm all for cross platform, but to be honest, I've seen a client who, after the code was completed, just decided to change database servers. Even if they did, if your code is already properly abstracted anyway, in CFCs, this is not a big deal. Another option to write cross db code - do what I did in BlogCFC. Simply make the current db a setting, and check for it in your queries. Yes, it's more work, but again, the benefits more than outweigh the cost of using maxrows.
Ray, I pretty much agree with that these days. Though depending on the scope of the app I think there are a few cases where maxrows is ok - but majority of cases you should probably use your db specific feature. I guess the reason I think maxrows is a good idea, is because the JDBC api has a setMaxRows method, which could in theory be used to limit the amount of rows returned by the server if the JDBC drivers were smart enough to do so. In practice they don't do this... bummer.
To be fair, my "million row" example was a bit "out there", as most folks will have _far_ less, so the use of maxrows won't be 'horrible' per se.
Dear All, How to write condition satatment Like IF FOR LOOP WHILE LOOP.In PostgreSQL query Kndly Regards Siva
I am planing to start a project, but still didn't decide whether to use Postgresql which I have more experience with and MS Sql server. what made you decide to move?
What are the major differences between MySQL and MSSQL ?
postgresql is better than sql.postgresql supports more than 200 GB of data for storage.It also support 100 users by default but we can make unlimited connections in this database.
Another great different is PostgreSQL does not allow cross database query where MS Sql does.
Worried someone might actually believe #23. The postgres DBlink module provides cross database querying.
The differences between SQL Server and PostgreSQL is the price: SQL Server: Lots of Money PostgreSQL: Free I like that PostgreSQL is platform independent. I would recommend from experience running PostgreSQL on a Linux server like RedHat or CentOS for best results.
please add more differences between these two databases.
Thanks! I used the info on TOP / LIMIT today. I couldn't find the answer anywhere else.
Hi Now we are plan to migrate our SQL Server Database to PostgreSQL. so Kindly help me to migrate what are the steps we need to follow and how to write a functions with Query Results. Kindly help me.