Thursday, 16 August 2012

PostgreSQL for an Oracle man

I've worked with Oracle since version 7 back in 1997, a total of 15 years. Over this period I've learned to love the language PL/SQL. As everything there are positive and negative things but overall I reckon it is a simple to learn yet powerful language. Being right there, in the database, gives you a lot of opportunities. I've made several large systems, like content management systems, reporting solutions, order systems and much more, using PL/SQL.

Not everyone do like PL/SQL though. I've spoken to several developers that has hated the language. However what they've all had in common is that they've not been very talented nor have done much PL/SQL work. Not saying that there are no talented people with good PL/SQL knowledge that hate that language, but I've just never met them.

Just recently I started developing a new system, codename 6fps. This time around I decided to go with PostgreSQL. Mainly because it is open-source and free, and because it has not got limitations like the free Oracle XE (11GB won't do it for me). I also wanted to learn a new database system, and something tells me that the use of PostgreSQL only will increase over the coming years.

After a few days work I reckon PostgreSQL seems like a great alternative to Oracle. With PL/pgSQL you can do pretty much the same as with PL/SQL. There are some differences though, some that I find annoying, some that I find hard to get used to, but I think I'll get there, eventually. Sooner or later I will get used to:

Not having a dual table. By the way, it's quite easy and logical skipping the entire "from statement" in the SQL in stead of having a fake dual table.
PostgreSQL : select 1;
Oracle : select 1 from dual;

Using now() in stead of sysdate. PostgreSQL date/time functions are actually quite good and you have a lot of opportunities that you can read more about here
PostgreSQL : select now();
Oracle : select sysdate from dual;

Oracle style packages doesn't exist in PostgreSQL. I miss them as I liked having packages for gathering related functions and procedures. Just having a bunch of functions gives you less control. In the PostgreSQL manual it says to use schemas to organise functions into groups. Personally that sounds all wrong to me.

You can not commit inside PL/pgSQL, but then again there is no need to. Just hard to get used to.

There are no procedures in PostgreSQL, but you can write functions as procedures. Here are a couple of examples.

By default giving all users access to all functions seems like a bug coming from an Oracle world. Having to revoke access on every function you create seems wrong. It should be the other way around, having to grant access to the functions you would like to have accessible for other database users. An important read on security is the IBM article Total security in a PostgreSQL database.

And there are many more things that feels weird in the beginning. The article Porting from Oracle PL/SQL is a useful read for understanding the differences in between Oracle and PostgreSQL.

Whenever you try out something new you need to have an open mind. Be careful judging something before you have gained sufficient knowledge about the subject you are judging. And do listen to people with knowledge. There are far too many talking too loud about thing they don't know anything about.

No comments:

Post a Comment

Allowed HTML tags:
<a href="">hyperlink</a>

Please, show the courtesy of identifying yourself when adding a comment. Anonymous comments will, most likely, be removed.