Top 3 differences between PostgreSQL and MS SQL

October 23, 2003

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.

Permalink | Add Comment |

add to del.icio.us | Tags: postgresql, sqlserver, sql

Related Entries

INFORMATION_SCHEMA Support in MySQL, PostgreSQL - February 18, 2008
SQL to Select a random row from a database table - September 14, 2005
SQL Reserved Key Words Checker Tool - March 28, 2005
Top 10 Reserved SQL Keywords - October 28, 2008
SQL Case Statement - October 15, 2008

28 people found this page useful, what do you think?

Trackbacks

Trackback Address: 5/288CAA60A646E39EC8F32262A59C8DB3

Comments

On 06/28/2004 at 3:49:11 PM EDT Mike wrote:

Just stumbled here, believe there's an ILIKE operator in postgres for case-insensitive search, too

On 07/07/2004 at 10:49:23 AM EDT naga_srikanth wrote:

hai

On 10/14/2004 at 4:21:24 PM EDT Mark Lewis wrote:

Be glad that you went from SQL-Server to PostgreSQL. If you're moving from an MVCC database like Oracle or PostgreSQL to SQL Server then there are all kinds of headaches you run into because of SQL Server's locking issues, lack of decent foreign key support, etc.

On 11/26/2004 at 1:47:00 AM EST kaizy wrote:

is there a lot of differences swicthing postgresql to sql-serve?

On 02/26/2005 at 12:57:22 AM EST RayTheGun wrote:

OMG thanks soo much , your small snippet on making CF colfusion SQL statements Case insensitive got me out of a jam... cheeeeeeeers mate

On 07/04/2005 at 4:02:29 AM EDT Nehalkumar Patel wrote:

What is the technical difference between SQL Server and MY SQL ? Please Reply ASAP ..... Thx Regargs, Nehalkumar Patel

On 07/07/2005 at 3:07:36 AM EDT Andrakis wrote:

Nehalkumar Patel - What, are you writing an assignment or something? Please Reply ASAP ... kthxbye

On 07/23/2005 at 2:04:12 PM EDT ObseloV wrote:

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!

On 10/14/2005 at 9:03:13 AM EDT Raymond Camden wrote:

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.

On 10/14/2005 at 4:05:02 PM EDT Pete Freitag wrote:

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.

On 10/14/2005 at 7:14:51 PM EDT Raymond Camden wrote:

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.

On 01/26/2006 at 2:32:26 PM EST Anonymous wrote:

can i migrate postgreysql database to ms sql database

On 01/31/2006 at 4:51:59 AM EST Chen Hendrawan wrote:

The top difference about MSSQL and PostgreSQL is how subquery will be performed. Subquery in PostgreSQL is very slow because they use sequential search in subquery clause.

In MSSQL you could easily use this query: SELECT * FROM table_a WHERE id NOT IN ( SELECT a_id FROM table_b )

But for Postgres you should use: SELECT table_a.* FROM table_a LEFT JOIN table_b ON table_a.id = table_b.a_id WHERE table_b.a_id IS NULL

Both query have the same result but very different performance. In MS-SQL, subquery perform 3times faster than using JOIN. In PostgreSQL, the subquery will perform much-much slower than using JOIN. Try this with at least 10.000 record in both table, and you will get frustated with subquery in Postgres. Hope someone in Postgres dev fix this.

On 03/07/2006 at 11:33:57 PM EST Tomislav wrote:

Well said, Chen. Took me a bit of time to figure it out for myself, too. We arrived at the same solution in the end. I second your suggestion to push this issue forward with the pgdev team.

On 09/21/2006 at 5:37:05 PM EDT edges wrote:

It's not the LIKE statement that is case-insensitive in MS SQL Server, it's the whole database (by default). You can change the collation however to make it case-sensitive like all the real databases.

On 01/14/2007 at 6:33:13 PM EST eXcalibur wrote:

Nice call edges, however Chen is not entirely correct. MS SQL joins are faster then MS SQL sub queries, however when MS SQL comes across a sub query, it will convert it to a join where ever possible.

On 06/01/2007 at 6:44:16 AM EDT Siva wrote:

Dear All, How to write condition satatment Like IF FOR LOOP WHILE LOOP.In PostgreSQL query

Kndly Regards Siva

On 07/11/2007 at 5:26:18 PM EDT Buddy wrote:

Siva is r3tard3d!!!

On 07/18/2007 at 8:38:19 AM EDT pabloj wrote:

Funny how so many think that case sensitive like is a problem that affects all databases but MsSQL (and MySQL sometimes) and not a collation feature.

On 07/28/2007 at 6:45:30 AM EDT Dirar wrote:

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?

On 10/29/2007 at 2:07:12 AM EDT Sunil Ranjan Pradhan wrote:

What are the major differences between MySQL and MSSQL ?

On 11/02/2007 at 8:49:49 AM EDT Rabinder wrote:

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.

On 10/23/2008 at 11:38:28 PM EDT vc wrote:

Another great different is PostgreSQL does not allow cross database query where MS Sql does.

On 12/04/2008 at 6:08:03 AM EST jpvos wrote:

case insensitive searching works only with '~*' instead of 'LIKE'

On 07/13/2009 at 3:24:50 PM EDT Danny Armstrong wrote:

Worried someone might actually believe #23.

The postgres DBlink module provides cross database querying.

On 10/01/2009 at 12:09:36 PM EDT Pedro Lopez wrote:

The differences between SQL Server and Postgress go far beyond some syntax differences: 1) Postgres (abrev. PG) will run on everything from a laoptop to a mainframe. With Sql Server the best you can do is "big tin".

2) You can tune both DB engines, but you can only recompile PG to match your hardware *precisely*.

3) PG is much more stable.

4) PG is much more secure, due to frequent and thorough code reviews.

5) SQL Server integrates well with Microsoft products. PG integrates well with MS products as well as 3rd party products (Oracle etc.).

6) PG has a built in GIS supportDB, if you chose to install it. SQL server has to have it hacked in.

That's just off the top my head.

On 10/20/2009 at 7:34:06 PM EDT Edward Wittlinger wrote:

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.

On 03/12/2010 at 3:15:17 PM EST Karel Sedlacek wrote:

Postgres supports the NOT EXISTS which is faster than either solution above and can use indexes:

SELECT table_a.* FROM table_a where NOT EXISTS (select ' from table_b WHERE table_a.id = table_b.a_id)

Selecting ' forces indexes to be read if they exist on table_b.a_id.

On 05/31/2010 at 6:24:25 AM EDT habeeb perwad wrote:

please add more differences between these two databases.

On 06/11/2010 at 4:07:03 AM EDT Dhaval wrote:

Is there any books available for all queries for postgresql?

I want to make select query which having where condition between two database. In mysql it is very easy e.g. Select * from db1.table1 a,db2.table1 b where a.id=b.id

please help me.

On 05/16/2011 at 5:32:40 PM EDT Evik James wrote:

Thanks! I used the info on TOP / LIMIT today. I couldn't find the answer anywhere else.

On 08/08/2011 at 12:43:12 AM EDT Anonymous wrote:

Well PostgreSQL is very much ANSI compliant so if you can find a good book that teaches ANSI SQL well, that will do. if you are a newbie, I suggest getting the SQL book of Chris Fehily, Peachpit Press I guess..(visual quickstart) A very good book for those who want to learn ANSI SQL (for beginners)

On 05/17/2012 at 3:35:21 AM EDT Ragu wrote:

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.

Top 3 differences between PostgreSQL and MS SQL

Related Entries

Trackbacks

Comments

Post a Comment

Recent Entries