pf » Top 3 differences between PostgreSQL and MS SQL

October 23, 2003

Top 3 differences between PostgreSQL and MS SQL

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
Backwards LIKE Statements - January 10, 2007
Cheat Sheet for PostgreSQL - September 7, 2005

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

Trackbacks

Trackback Address: 5/288CAA60A646E39EC8F32262A59C8DB3

Comments

On 06/28/2004 at 1:49:11 PM MDT Mike wrote:

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

On 07/07/2004 at 8:49:23 AM MDT naga_srikanth wrote:

hai

On 10/14/2004 at 2:21:24 PM MDT 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/25/2004 at 11:47:00 PM MST kaizy wrote:

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

On 02/25/2005 at 10:57:22 PM MST 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 2:02:29 AM MDT 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 1:07:36 AM MDT Andrakis wrote:

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

On 07/23/2005 at 12:04:12 PM MDT 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 7:03:13 AM MDT 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 2:05:02 PM MDT 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 5:14:51 PM MDT 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 12:32:26 PM MST Anonymous wrote:

can i migrate postgreysql database to ms sql database

On 01/31/2006 at 2:51:59 AM MST 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 9:33:57 PM MST 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 3:37:05 PM MDT 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 4:33:13 PM MST 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 4:44:16 AM MDT 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 3:26:18 PM MDT Buddy wrote:

Siva is r3tard3d!!!

On 07/18/2007 at 6:38:19 AM MDT 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 4:45:30 AM MDT 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 12:07:12 AM MST Sunil Ranjan Pradhan wrote:

What are the major differences between MySQL and MSSQL ?

On 11/02/2007 at 6:49:49 AM MST 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.