PostgreSQL vs M$ SQL

Sorry to start another one of these threads -lol , but I want to convince a client that postgresql provides a better solution than M$ SQL

My main question is I know that the stored procedure language in postgreSQL is a copy of Oracle PL/SQL. How good is MS SQL PL/SQL? I was hoping that it wasn't as good as PostgreSQL.

We (well actually our DBA ) try to move all a system's business logic into the database whichs provides better security and speed. Because of this we need a powerful PL/SQL coding language

If there any other compelling reasons I'd be interested.

Awesome and thanks,

Z.

PL/SQL is Oracle's made-up procedural SQL language. T-SQL is Microsoft's and Sybase's.

Well, SQL Server is one of the better of Microsoft's products, so this isn't an easy, clear-cut decision. I would say there are pros and cons, and one should think through needs carefully. PostgreSQL definitely has technical excellence and good programming techniques. It will definitely perform much better on low to midrange hardware. MSSQL, I would say has a few technical problems, but it does have some of the more "enterprise" features, such as clustering, full replication, etc... as well as better integration with other Microsoft products.

If I had to program a Microsoft Access front end to a company DB, I would probably choose SQL Server, for example, but if I had to provide DB access to a heterogenous range of systems, with different hardware, OS, and software, then I would tend to choose PostgreSQL. Also, PostgreSQL offers some nice scientific-minded extra capabilities, which you can read about in some of my other posts in this forum and the PostgreSQL forum.

PostgreSQL actually offers the capability to handle stored procedures in 5 languages, with the possibility of a 6th coming soon:

- SQL
- PL/PgSQL
- Perl (this is great for complex logic!)
- Python
- TCL (can actually connect to front-end GUIS)
- Java (coming soon...)

Thanks that makes excellent reading I really appreciate the responses and the advice. Now back to that RFI - LOL

Z.

I recently had the same problem ... Client has 50+ users - I showed him how much he'd pay for a M$ SQL server

Off the top of your head can I ask how much that was (and in what currency) ? How much approx for 100 users ?

Its 5:45AM and I'm grasping at straws - lol.

Thanx,
Z.

Cost depends on how what your current config is...
Assuming you already have a NT/2000 Server licence that you can use and sufficent Client Access Licenses for it (this assumes that you are going to be accessing the data via a direct ODBC connection or something like that - rather than via a web-page (PHP/ASP etc) (which is the situation for my client - we have a MS Access front end that is being ported to a SQL backend (PostgreSQL))

MS SQL Server 2000 Server with 10 Client Access licences RRP ~$A5,000
MS SQL Server 2000 Client Access Licences ~$A200
gets nasty quick...

One gotcha I've gotten with PostgreSQL however is the inability to switch off case-sensitivity ... My major app is a call centre and if one operator types 'Smith' and another types 'smith' ... the simple query
SELECT * FROM tblContact WHERE LastName = 'smith';
will not match 'Smith'

One thing that PostgreSQL will do which I believe M$SQL won't is partial indexes ... that is, if you have data where 80% of the data is the same and only 20% varies - you can create an index on the 20% so you *can* speed up queries on the 20%

So for 100 client access users that is $50,000? However once you've got M$, what is the cost of setting up a new server with the same clients?

Thanks dude,

G.

One gotcha I've gotten with PostgreSQL however is the inability to switch off case-sensitivity

You can just use regular expression matching:

SELECT * FROM tblContact WHERE LastName ~* 'smith';

This will match 'sMiTh', 'SMith' and all other variations.

And PostgreSQL also allows you to override the meaning of operators, so you could redefine the '=' to be case insensitive when comparing strings.

Don

I think performance isn't so great with regex queries. For this situation, its better to just use the case-insensitive version of the LIKE operator: ILIKE
This more portable SQL, and is also good for partial matches, such as with 'smith%', etc...

Getting around case sensitivity
I read an article about putting an index on a lower-case version of the field - which speeds up the search and it suggested putting
SELECT * FROM tblContact WHERE Lower(LastName) = 'smith'

I have the added complication that I am using an ODBC link in and for various things I'm not wanting to write pass-thru queries - which would rule out regex.

However I'm guessing if you want to use a regex and get some performance out of it (ie provided you have and index on the field) if you use

SELECT * FROM tblContact WHERE LastName ~* '^smith$'

You of course could leave off the $ but that would then match last names beginning with smith eg Smithson - and if you have a lot of names it mightn't use the index ... I suggest using EXPLAIN on your query and then you can play around to get performance.

BACK To the question of M$SQL vs PostgreSQL... if you already have MS SQL and/or the licensing costs arn't an issue. Then depending on the front-end eg Suppose you're using MS Access - you may find the migration to M$SQL easier!!!

Also, I know that M$SQL is quite good at replicating (ie running a DB over 2 sites) haven't found any comments - re Is it possible, or its possible but painful, or it is a no-brainer to have 2 psql db's in sync (assuming you're updating both sides - if you only make changes on one db ... making the copy is a no-brainer (pg_dump))

Has anyone seen a numerical comparison of PostgreSQL and MS SQL Server? It would be very helpful.

Thanks,
Shai Sachs