Pete Freitag Pete Freitag

Top 3 differences between PostgreSQL and MS SQL

databases

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, becomes SELECT * FROM table LIMIT 10 you 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 fullname becomes SELECT firstname || ' ' || lastname AS fullname this way works on both servers.

Like this? Follow me ↯

Top 3 differences between PostgreSQL and MS SQL was first published on October 23, 2003.

If you like reading about postgresql, sqlserver, or sql then you might also like:

Comments

is there a lot of differences swicthing postgresql to sql-serve?
by kaizy on 11/25/2004 at 11:47:00 PM UTC
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!
by ObseloV on 07/23/2005 at 12:04:12 PM UTC
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.
by Raymond Camden on 10/14/2005 at 7:03:13 AM UTC
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.
by Pete Freitag on 10/14/2005 at 2:05:02 PM UTC
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.
by Raymond Camden on 10/14/2005 at 5:14:51 PM UTC
Dear All, How to write condition satatment Like IF FOR LOOP WHILE LOOP.In PostgreSQL query Kndly Regards Siva
by Siva on 06/01/2007 at 4:44:16 AM UTC
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?
by Dirar on 07/28/2007 at 4:45:30 AM UTC
What are the major differences between MySQL and MSSQL ?
by Sunil Ranjan Pradhan on 10/29/2007 at 12:07:12 AM UTC
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.
by Rabinder on 11/02/2007 at 6:49:49 AM UTC
Another great different is PostgreSQL does not allow cross database query where MS Sql does.
by vc on 10/23/2008 at 9:38:28 PM UTC
Worried someone might actually believe #23. The postgres DBlink module provides cross database querying.
by Danny Armstrong on 07/13/2009 at 1:24:50 PM UTC
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.
by Edward Wittlinger on 10/20/2009 at 5:34:06 PM UTC
please add more differences between these two databases.
by habeeb perwad on 05/31/2010 at 4:24:25 AM UTC
Thanks! I used the info on TOP / LIMIT today. I couldn't find the answer anywhere else.
by Evik James on 05/16/2011 at 3:32:40 PM UTC
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.
by Ragu on 05/17/2012 at 1:35:21 AM UTC