PostgreSQL or MySQL?

Jassim Rahma

Hi, I am developing a hospital management system and I want to know what's best to choose as database? PostgreSQL or MySQL? and why?

Paul Guralivu

It basically depends on the size of the hospital and the language in which you are gone write the application. Check out this article: here. Paul.

Jassim Rahma

I am using C#.Net. My current application is on SQL 2008 and ir has stored procedures, views, function and triggers.

Jassim Rahma

Currently only one hospital is using it with 25users but I am planning to get more clinics and hospitals to use

Simon_Whale

My thoughts / Questions would be 1. Which allows you to fullfill the requirements of the application? i.e. which will comfortably allow you to store the volumes and types of information that you want, run the queries that you need to 2. Which is best scalable for the project? 3. Which are you best with (this allows you to cover support and installation!)? 4. Which is the most cost effective for the overall project?

As barmey as a sack of badgers Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.

Bernhard Hiller

I have done some little work with MySQL, and even less with postgres. My impression was that postgres is slightly more complicated to handle (especially when it comes to automatic row IDs, and querying them from an application), but it performed better when doing reports with "complicated" sql (joining subqueries with real tables) - but the more complicated the queries get, the more likely is also postgres to not complete it within an hour where Microsoft SQL Server or Oracle do that within a few seconds! By the way, there is a hospital information system using postgres with free source code in Java: http://www.hospital-os.com/en/[^]

David Skelly

This may help: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL[^] One thing to bear in mind that many people forget: if you are developing a commercial application, MySQL is not free and you will need to purchase a commercial licence for it. If using MySQL, you also need to consider whether you will have a high read:write ratio as that may influence your choice of MyISAM (faster but no ACID transaction support) versus InnoDB (full transaction support but not as fast).

Daniel Casserly

i agree with the guys here you need to ask yourself what you need for your particular circumstances but my personal opinion is that postgre sql is a much easier to use and speedy database than mysql. Using pgadmin3(which is free) you have loads of help and docs and its all free. However, one comment is really true, it depends what you are most happy with and which suits your needs.

Jassim Rahma

thanks everyone.. clear... seems like I am going to use PostreSQGL.. but one question please... comparing to Microsoft SQL Server 2008 Express Edition, am I going to lose any feature if I decided to move to PostgreSQL?

Bernhard Hiller

Yes, some. But generally, the problem can be solved. Some points to consider: - searching text with MS databases is not case sensitive, in postgres it is. You can use ILIKE instead of LIKE or the LOWER() function on both sides. - the table name USER is not allowed, using square brackets is also not allowed (e.g. [USER]); you can use user_ instead. - the data type Guid is missing. You can use CHAR(36) instead. - autoincrement columns are implemented differently. Use the data type serial, a sequence is created automatically. For querying the last inserted id, "SELECT @@Identity" does not work, you must query the current value of the sequence instead: SELECT currval(pg_get_serial_sequence(mytable, mycolumn));

Daniel Casserly

ive not found anything that majorly impairs me from the move however there is a cool feature of arrays as a data type in pgsql as a bonus, therefore you can have an example field like maybe colors type text[] and fill it with info like '{blue, black, grey, green}' etc. similar to an enum in c#. Quite a nice little feature that i cant find in sql server

JChrisCompton

I worked at a teaching hospital for about 18 months and the db licenses there were dirt cheap. (Less than 1/3 the retail price) I assume because it was attched to a University. This applied to Oracle and SQL Server (and yes, I am talking about a production license in both cases). YMMV - A lot has changed in seven years, so this may be completely irrelevant. HTH, -Chris C.