Top 3 differences between PostgreSQL and MS SQL

October 23, 2003
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.


Related Entries

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

Comments

Just stumbled here, believe there's an ILIKE operator in postgres for case-insensitive search, too
hai
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.
is there a lot of differences swicthing postgresql to sql-serve?
OMG thanks soo much , your small snippet on making CF colfusion SQL statements Case insensitive got me out of a jam... cheeeeeeeers mate
What is the technical difference between SQL Server and MY SQL ? Please Reply ASAP ..... Thx Regargs, Nehalkumar Patel
Nehalkumar Patel - What, are you writing an assignment or something? Please Reply ASAP ... kthxbye
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.
can i migrate postgreysql database to ms sql database
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.
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.
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.
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.
Dear All, How to write condition satatment Like IF FOR LOOP WHILE LOOP.In PostgreSQL query Kndly Regards Siva
Siva is r3tard3d!!! 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.
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.
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.
case insensitive searching works only with '~*' instead of 'LIKE'
Worried someone might actually believe #23. The postgres DBlink module provides cross database querying.
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.
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.
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.
please add more differences between these two databases.
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.
Thanks! I used the info on TOP / LIMIT today. I couldn't find the answer anywhere else.
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)
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.
Three main Differences are....
1) 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
2) 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)
3) 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.

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?