Re: PostgreSQL vs. MSSQL
I just read through Chapter 7 of the PostgreSQL docs ( http://www.postgresql.org/docs/8.2/static/queries.html) regarding queries, and could find no examples or mention of a sub-query used in the select list. For example: SELECT t1.foo, (SELECT COUNT(t2.id) FROM tblTwo t2 WHERE t2.id = t1.id) AS my_count FROM tblOne t1 Is that a valid query in PostgreSQL? Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266110 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Matt Quackenbush wrote: I just read through Chapter 7 of the PostgreSQL docs ( http://www.postgresql.org/docs/8.2/static/queries.html) regarding queries, and could find no examples or mention of a sub-query used in the select list. quote 7.3.1. Select-List Items The simplest kind of select list is * which emits all columns that the table expression produces. Otherwise, a select list is a comma-separated list of value expressions (as defined in Section 4.2). /quote And if you follow that link to section 4.2 of the manual: quote 4.2. Value Expressions (..) A value expression is one of the following: (..) * A scalar subquery. * Another value expression in parentheses, useful to group subexpressions and override precedence. /quote Yes, you can use a subquery in the select list. Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266115 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Sweet! Sorry about that Jochem, I read the one Chapter (7), but didn't follow the referenced links. Obviously, I should have. Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266157 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: PostgreSQL vs. MSSQL
Jon, Thanks for the reply. Do you currently run MySQL and/or PostgreSQL on Windows boxes? If so, what version(s) for both the db server and windows servers? The cost is currently not an issue, although it certainly has the potential to become one in the relatively near future, at the current database growth rate. Thus my looking into my options :-) Also, just to clarify on the case-sensitivity point you made, am I correctly understanding that the following query **typically** would not have any issues? cfquery SELECT my_field FROM my_table; /cfquery Even if the field was named as 'my_Field' (minus the quotes) in the database? Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266025 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Matt Quackenbush wrote: Thanks for the reply. I forgot to post his specific gripes about it. Here is a quick summary: - non-standard sql I think that depends on what you call 'standard sql'. To me, standard SQL is what is defined in ISO/IEC 9075 and few do better then PostgreSQL in that respect. But if you take 'standard sql' to mean 'the SQL in product X', you may discover many differences. - difficult to backup / migrate What is difficult about scheduling pg_dumpall -f dumpfile? For migration he may have a point. PostgreSQL will not allow you to shut down the database, pack up the data files, put them on another machine and start up there. While this technically is possible in some circumstances, it is not a documented procedure because it is guaranteed not to work when you move from for instance Windows to an AIX mainframe. Other products that only run on limited platforms and do not have to worry about memory alignment and the endianness of the host operating system do not have this limitation. - user authenication is weak PostgreSQL allows the DBA to use anything from no authentication to Kerberos and you can even create single-signon with a Windows domain. User authentication is as weak as the DBA configured it to be. And if we extend this to security in general you might want to Google a bit. PostgreSQL is highly regarded for its security track record and is often an example to other databases. For instance, while many databases install themselves under an account with elevated privileges by default even when that is technically not necessary, PostgreSQL refuses to run if it has more permissions then it needs. - difficult to setup http://www.suite101.com/article.cfm/oracle/115560 - difficult to manage To manage PostgreSQL you need to do a few things that are clearly spelled out in the manual: http://www.postgresql.org/docs/8.2/static/maintenance.html http://www.postgresql.org/docs/8.2/static/backup.html Again I would have to ask what is so hard about setting up a scheduled task. In short: you may want to ask him if he is not confusing PostgreSQL with some other database. Your post was most informative, for sure. But there's one thing that you mentioned that I was unaware of that is pretty much an instant turn-off for me: cAsE sEnSiTiViTy. PostgreSQL identifiers are only case sensite if you make them case sensitive by enclosing them between double quotes. The following code will run just fine: CREATE TABLE fOo (BaR INTEGER); SELECT bAr FROM foo; Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266028 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Matt Quackenbush wrote: I've always used MSSQL and quite frankly am quite fond of it - except for licensing costs. I've read a few posts here and there where people have talked highly of PostgreSQL, so I'm thinking about giving it a shot on a new server (windows box). I mentioned this to a buddy of mine who is a DBA, and he said to steer well clear of it. While I value his opinion greatly, I was wondering if the users here would care to share their personal + vs. - arguments for PostgreSQL? + rich SQL support + easy to install / configure + portable + low resource usage + excellent documentation + great community support + many options for commercial support + liberal license + understandable source code The only thing I miss in PostgreSQL is eager update-everywhere replication. There are commercial offerings for that, but all the community offerings are either lazy or master-slave. Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266031 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Jochem van Dieten wrote: wondering if the users here would care to share their personal + vs. - arguments for PostgreSQL? + rich SQL support + easy to install / configure + portable + low resource usage + excellent documentation + great community support + many options for commercial support + liberal license + understandable source code you forgot postGIS :-) ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266034 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: PostgreSQL vs. MSSQL
I agree, I think every database has some non-standard SQL. An example in MS SQL is the isNull() function, which is functionally equivalent to the ANSI standard coalesce(). Why did MS make their own? Who knows, maybe because they liked their name better? SQL Server also supports COALESCE. And, while the two are very similar, they aren't identical in functionality. A Google search on coalesce vs isnull turns up all sorts of mildly interesting stuff. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266037 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
On Jan 9, 2007, at 3:23 AM, Matt Quackenbush wrote: Thanks for the reply. Do you currently run MySQL and/or PostgreSQL on Windows boxes? If so, what version(s) for both the db server and windows servers? Both. 8.2 for Postgres and 5.1 for MySQL.XP Pro and Server 2003 for the OS. Also, just to clarify on the case-sensitivity point you made, am I correctly understanding that the following query **typically** would not have any issues? cfquery SELECT my_field FROM my_table; /cfquery Even if the field was named as 'my_Field' (minus the quotes) in the database? Actually, the other way around. When you convert your tables over to Postgres, you'll have to lowercase them. Once again, though if you don't quote the table names in the script, Pg will automatically take care of lower casing that for you when you run the Create's. Then any queries you have with mixed case names for the table attributes will still run. If you uppercase any of the table/column names, you will have to quote them. HTH, Jon ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266041 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
SQL Server also supports COALESCE. And, while the two are very similar, they aren't identical in functionality. A Google search on coalesce vs isnull turns up all sorts of mildly interesting stuff. You're right on both counts, Dave. My main point was that MS even bothered to make a non-standard function. IIRC, Coalesce does more than isNull, but the basic thing that most people use isNull() for is this: isNull(mname,'blank') so you don't have nulls in your return values, and this works the same as coalesce. However, I can't justly bash MS for this, because Oracle, MySQL and DB2 have proprietary functions for this as well. I just wanted to point out that all the popular DB platforms have non-standard SQL to some degree. -- My Sites: http://www.techfeed.net/blog/ http://www.cfquickdocs.com/ http://cfformprotect.riaforge.org/ ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266042 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: PostgreSQL vs. MSSQL
Coalesce is a WAY cooler name. Those guys at MS must have been smoking crack! Andy Matthews Senior Coldfusion Developer Office: 877.707.5467 x747 Direct: 615.627.9747 Fax: 615.467.6249 [EMAIL PROTECTED] www.dealerskins.com -Original Message- From: Jacob Munson [mailto:[EMAIL PROTECTED] Sent: Monday, January 08, 2007 7:41 PM To: CF-Talk Subject: Re: PostgreSQL vs. MSSQL - non-standard sql Indeed. This is true in some rare cases (like case sensitivity) however, it seems all databases take some liberties in regards to this. I agree, I think every database has some non-standard SQL. An example in MS SQL is the isNull() function, which is functionally equivalent to the ANSI standard coalesce(). Why did MS make their own? Who knows, maybe because they liked their name better? Back to your regularly scheduled programming. :) -- My Sites: http://www.techfeed.net/blog/ http://www.cfquickdocs.com/ http://cfformprotect.riaforge.org/ ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266048 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Hi Mark, Thank you for the additional information there. I really appreciate it. Are you aware of anyone who currently runs MS SQL in case sensitive mode? I'd be curious to know how often that happens. Warm regards, Jordan Michaels Vivio Technologies http://www.viviotech.net/ Blue Dragon Alliance Member [EMAIL PROTECTED] Mark A Kruger wrote: Jordan, One note - MS SQL is not case insensitive. In it's default collation it is case insensitive - but it can be either case sensitive OR case insensitive depending on install options and collations. -mark -Original Message- From: Jordan Michaels [mailto:[EMAIL PROTECTED] Sent: Monday, January 08, 2007 6:10 PM To: CF-Talk Subject: Re: PostgreSQL vs. MSSQL MS SQL is a fine database, and will get the job done. However, why pay for a database like that when you can get essentially the same thing for free? I've got nothing but positive things to say about PostgreSQL, and I'd be very interested to hear what your friend has against it. In my experience, usually these kinds of opinions are derived from a lack of understanding about how something operates. With PostgreSQL, there are two items that generally end up as road blocks for most MS SQL users who try it out. 1) User permissions: PostgreSQL is not forgiving as far as user/access permissions go. This is both good (for security purposes) and bad (because it can be frustrating to learn). However, like most things, once you understand how it's permission system works, it's not that difficult - it's just different. 2) Case sensitivity: Unlike MS SQL, PostgreSQL is a case sensitive database. This can be the deciding factor when choosing to port an application from MS SQL to PostgreSQL. If you have a database called User in PostgreSQL, your queries will need to have that database name in quotes. Here's an example: Database: User Field Names: ID, UserName, Password MS SQL Query: CFQUERY name=GetUser datasource=MSSQL SELECT id, username, password FROM user /CFQUERY PostgreSQL Query: CFQUERY name=GetUser datasource=PostgreSQL SELECT ID, UserName, Password FROM User /CFQUERY In our shop we user lowercase names for everything in our databases, so I haven't had to do that for a long time when working with PostgreSQL. As such, I cannot remember if it was single quotes, or double-quotes that I had to use when dealing with names that had uppercase values in them. Still, you get the idea. With the exception of those two items, PostgreSQL is wonderful. The price is great, the performance is great, and the community support is great. I would highly recommend PostgreSQL to *anyone* looking for a high-performance, low cost alternative to MS SQL. Hope this helps! Warm regards, Jordan Michaels Vivio Technologies http://www.viviotech.net/ Blue Dragon Alliance Member [EMAIL PROTECTED] Matt Quackenbush wrote: Hello, I've always used MSSQL and quite frankly am quite fond of it - except for licensing costs. I've read a few posts here and there where people have talked highly of PostgreSQL, so I'm thinking about giving it a shot on a new server (windows box). I mentioned this to a buddy of mine who is a DBA, and he said to steer well clear of it. While I value his opinion greatly, I was wondering if the users here would care to share their personal + vs. - arguments for PostgreSQL? Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266051 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Jon, Thanks for the clarification. Matt On 1/9/07, Jon Clausen [EMAIL PROTECTED] wrote: On Jan 9, 2007, at 3:23 AM, Matt Quackenbush wrote: Thanks for the reply. Do you currently run MySQL and/or PostgreSQL on Windows boxes? If so, what version(s) for both the db server and windows servers? Both. 8.2 for Postgres and 5.1 for MySQL.XP Pro and Server 2003 for the OS. Also, just to clarify on the case-sensitivity point you made, am I correctly understanding that the following query **typically** would not have any issues? cfquery SELECT my_field FROM my_table; /cfquery Even if the field was named as 'my_Field' (minus the quotes) in the database? Actually, the other way around. When you convert your tables over to Postgres, you'll have to lowercase them. Once again, though if you don't quote the table names in the script, Pg will automatically take care of lower casing that for you when you run the Create's. Then any queries you have with mixed case names for the table attributes will still run. If you uppercase any of the table/column names, you will have to quote them. HTH, Jon ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266080 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Jochem, I was hoping that you would respond. I've seen a number of your posts regarding PostgreSQL, and certainly wanted your opinion. :-) We all know what opinions are like, which is why I didn't want to just take my friend's opinion as the end-all official truth. Thanks to you all, I believe that I'm going to give Postgre a shot and see how I like it myself. My intent is to have a dedicated db server (windows server 2003 - std ed.), which will run only the db. However, in a test/dev environment, I don't have that luxury. So, are there any issues that I need to be aware of when running on XP Pro along with IIS and CF 7.x in my dev environment? Thanks, Matt On 1/9/07, Jochem van Dieten [EMAIL PROTECTED] wrote: Matt Quackenbush wrote: Thanks for the reply. I forgot to post his specific gripes about it. Here is a quick summary: - non-standard sql I think that depends on what you call 'standard sql'. To me, standard SQL is what is defined in ISO/IEC 9075 and few do better then PostgreSQL in that respect. But if you take 'standard sql' to mean 'the SQL in product X', you may discover many differences. - difficult to backup / migrate What is difficult about scheduling pg_dumpall -f dumpfile? For migration he may have a point. PostgreSQL will not allow you to shut down the database, pack up the data files, put them on another machine and start up there. While this technically is possible in some circumstances, it is not a documented procedure because it is guaranteed not to work when you move from for instance Windows to an AIX mainframe. Other products that only run on limited platforms and do not have to worry about memory alignment and the endianness of the host operating system do not have this limitation. - user authenication is weak PostgreSQL allows the DBA to use anything from no authentication to Kerberos and you can even create single-signon with a Windows domain. User authentication is as weak as the DBA configured it to be. And if we extend this to security in general you might want to Google a bit. PostgreSQL is highly regarded for its security track record and is often an example to other databases. For instance, while many databases install themselves under an account with elevated privileges by default even when that is technically not necessary, PostgreSQL refuses to run if it has more permissions then it needs. - difficult to setup http://www.suite101.com/article.cfm/oracle/115560 - difficult to manage To manage PostgreSQL you need to do a few things that are clearly spelled out in the manual: http://www.postgresql.org/docs/8.2/static/maintenance.html http://www.postgresql.org/docs/8.2/static/backup.html Again I would have to ask what is so hard about setting up a scheduled task. In short: you may want to ask him if he is not confusing PostgreSQL with some other database. Your post was most informative, for sure. But there's one thing that you mentioned that I was unaware of that is pretty much an instant turn-off for me: cAsE sEnSiTiViTy. PostgreSQL identifiers are only case sensite if you make them case sensitive by enclosing them between double quotes. The following code will run just fine: CREATE TABLE fOo (BaR INTEGER); SELECT bAr FROM foo; Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266081 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: PostgreSQL vs. MSSQL
Are you aware of anyone who currently runs MS SQL in case sensitive mode? I'd be curious to know how often that happens. Quite a few people do this, in my experience. You can specify different collations for each database within an instance. Collations determine other things in addition to case sensitivity. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266095 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
On 1/8/07 7:29 PM, Matt Quackenbush wrote: - non-standard sql It is different to SQL Server, but that doesn't mean that SQL Server is standard compliant either. In 8.x they've made many tweaks to make it even more standards compliant in its syntax, so please have him check his facts. - difficult to backup / migrate The backup seems straight forward using pg_dump, what were his specific issues? - user authenication is weak No it isn't, it just doesn't link into Active Directory :-P - difficult to setup No it isn't :-P - difficult to manage Both PGAdmin III and phpPgAdmin (latest betas) are great, and then there's the command-line tools, what else does he want? mentioned that I was unaware of that is pretty much an instant turn-off for me: cAsE sEnSiTiViTy. It depends on how you write your code, please RTFM: pages 26 and 27 or sections 4.1.2.1 and 4.1.2.2. -- Damien McKenna - Web Developer [EMAIL PROTECTED] The Limu Company - http://www.thelimucompany.com/ - 407-804-1014 #include stdjoke.h ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266096 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
On 1/8/07 8:40 PM, Jacob Munson wrote: I agree, I think every database has some non-standard SQL. An example in MS SQL is the isNull() function, which is functionally equivalent to the ANSI standard coalesce(). Why did MS make their own? Now() vs GetDate(), etc. -- Damien McKenna - Web Developer [EMAIL PROTECTED] The Limu Company - http://www.thelimucompany.com/ - 407-804-1014 #include stdjoke.h ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266097 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: PostgreSQL vs. MSSQL
On 1/9/07 6:03 AM, Jochem van Dieten wrote: The only thing I miss in PostgreSQL is eager update-everywhere replication. There are commercial offerings for that, but all the community offerings are either lazy or master-slave. I'll second that, I read through the manual and instantly wanted to elope ;-) but the lack of more solid replication / clustering abilities are a bit limiting. At the very least you can start off with the basic system and migrate / upgrade to EnterpriseDB in the future if you need to, which'll give you more flexible replication support. -- Damien McKenna - Web Developer [EMAIL PROTECTED] The Limu Company - http://www.thelimucompany.com/ - 407-804-1014 #include stdjoke.h ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266099 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
MS SQL is a fine database, and will get the job done. However, why pay for a database like that when you can get essentially the same thing for free? I've got nothing but positive things to say about PostgreSQL, and I'd be very interested to hear what your friend has against it. In my experience, usually these kinds of opinions are derived from a lack of understanding about how something operates. With PostgreSQL, there are two items that generally end up as road blocks for most MS SQL users who try it out. 1) User permissions: PostgreSQL is not forgiving as far as user/access permissions go. This is both good (for security purposes) and bad (because it can be frustrating to learn). However, like most things, once you understand how it's permission system works, it's not that difficult - it's just different. 2) Case sensitivity: Unlike MS SQL, PostgreSQL is a case sensitive database. This can be the deciding factor when choosing to port an application from MS SQL to PostgreSQL. If you have a database called User in PostgreSQL, your queries will need to have that database name in quotes. Here's an example: Database: User Field Names: ID, UserName, Password MS SQL Query: CFQUERY name=GetUser datasource=MSSQL SELECT id, username, password FROM user /CFQUERY PostgreSQL Query: CFQUERY name=GetUser datasource=PostgreSQL SELECT ID, UserName, Password FROM User /CFQUERY In our shop we user lowercase names for everything in our databases, so I haven't had to do that for a long time when working with PostgreSQL. As such, I cannot remember if it was single quotes, or double-quotes that I had to use when dealing with names that had uppercase values in them. Still, you get the idea. With the exception of those two items, PostgreSQL is wonderful. The price is great, the performance is great, and the community support is great. I would highly recommend PostgreSQL to *anyone* looking for a high-performance, low cost alternative to MS SQL. Hope this helps! Warm regards, Jordan Michaels Vivio Technologies http://www.viviotech.net/ Blue Dragon Alliance Member [EMAIL PROTECTED] Matt Quackenbush wrote: Hello, I've always used MSSQL and quite frankly am quite fond of it - except for licensing costs. I've read a few posts here and there where people have talked highly of PostgreSQL, so I'm thinking about giving it a shot on a new server (windows box). I mentioned this to a buddy of mine who is a DBA, and he said to steer well clear of it. While I value his opinion greatly, I was wondering if the users here would care to share their personal + vs. - arguments for PostgreSQL? Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266016 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Jordan, Thanks for the reply. I forgot to post his specific gripes about it. Here is a quick summary: - non-standard sql - difficult to backup / migrate - user authenication is weak - difficult to setup - difficult to manage Your post was most informative, for sure. But there's one thing that you mentioned that I was unaware of that is pretty much an instant turn-off for me: cAsE sEnSiTiViTy. While I subscribe to the same philosophy that your shop does (everything lower-case) I've run into too many situations in the past where other people wrote things and changed the case. It has always proven to be more of a headache than it was worth. Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266017 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Hi Matt, I've responded to your message in-line below: Matt Quackenbush wrote: Jordan, Thanks for the reply. I forgot to post his specific gripes about it. Here is a quick summary: - non-standard sql Indeed. This is true in some rare cases (like case sensitivity) however, it seems all databases take some liberties in regards to this. - difficult to backup / migrate Really? I've had exceptionally good luck with pg_dump. Just set up a cron job (scheduled task) that dumps the database at specific times, then back up the sql file that it generates. Piece of cake. - user authenication is weak I completely disagree with this statement. - difficult to setup I've never personally found clicking on an OK button (windows) or installing an RPM (linux) difficult, but to each their own. ;) - difficult to manage This goes back to what I was saying earlier. It's not difficult, it's just different then what you're used to. You had to learn the details of MS SQL server at one time too, and back when you were a MS SQL newbie, it may have seemed difficult. However, once you became familiar with it and how it worked, it's not difficult at all. The same goes for PostgreSQL. This is one of those places where PostgreSQL community support comes in really handy. I've always had good luck finding answers to my questions via Google and the PostgreSQL user lists. With regards to management tools, I've personally enjoyed using phpPgAdmin with our PostgreSQL databases. It's extremely useful to be able to connect to your database from anywhere. PgAdmin also has it's uses. Alternatively, there are impressive commercial products available for PostgreSQL. They cost, but they're still a good deal less the MS SQL server licences. Here's a good example: http://www.sqlmanager.net/products/postgresql/manager Your post was most informative, for sure. But there's one thing that you mentioned that I was unaware of that is pretty much an instant turn-off for me: cAsE sEnSiTiViTy. While I subscribe to the same philosophy that your shop does (everything lower-case) I've run into too many situations in the past where other people wrote things and changed the case. It has always proven to be more of a headache than it was worth. Honestly, I completely agree with you on this point. I understand why they did it this way, but it's not very user friendly at all. I personally believe that this is one of the last technical hurdles that PostgreSQL will need to overcome before it can really break in to the database market. It's done amazingly well so far, and once this road block is removed I will be recommending PostgreSQL to a lot more of our customers who wish to move to something more liberal and less expensive then MS SQL. Until then, as you say, porting applications with databases that have upper-case table names and field names is more headache then it's worth... This doesn't stop me from taking advantage of PostgreSQL when writing new applications though. Hope this helps! Warm regards, Jordan Michaels Vivio Technologies http://www.viviotech.net/ Blue Dragon Alliance Member [EMAIL PROTECTED] ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266019 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
- non-standard sql Indeed. This is true in some rare cases (like case sensitivity) however, it seems all databases take some liberties in regards to this. I agree, I think every database has some non-standard SQL. An example in MS SQL is the isNull() function, which is functionally equivalent to the ANSI standard coalesce(). Why did MS make their own? Who knows, maybe because they liked their name better? Back to your regularly scheduled programming. :) -- My Sites: http://www.techfeed.net/blog/ http://www.cfquickdocs.com/ http://cfformprotect.riaforge.org/ ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266020 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: PostgreSQL vs. MSSQL
Jordan, One note - MS SQL is not case insensitive. In it's default collation it is case insensitive - but it can be either case sensitive OR case insensitive depending on install options and collations. -mark -Original Message- From: Jordan Michaels [mailto:[EMAIL PROTECTED] Sent: Monday, January 08, 2007 6:10 PM To: CF-Talk Subject: Re: PostgreSQL vs. MSSQL MS SQL is a fine database, and will get the job done. However, why pay for a database like that when you can get essentially the same thing for free? I've got nothing but positive things to say about PostgreSQL, and I'd be very interested to hear what your friend has against it. In my experience, usually these kinds of opinions are derived from a lack of understanding about how something operates. With PostgreSQL, there are two items that generally end up as road blocks for most MS SQL users who try it out. 1) User permissions: PostgreSQL is not forgiving as far as user/access permissions go. This is both good (for security purposes) and bad (because it can be frustrating to learn). However, like most things, once you understand how it's permission system works, it's not that difficult - it's just different. 2) Case sensitivity: Unlike MS SQL, PostgreSQL is a case sensitive database. This can be the deciding factor when choosing to port an application from MS SQL to PostgreSQL. If you have a database called User in PostgreSQL, your queries will need to have that database name in quotes. Here's an example: Database: User Field Names: ID, UserName, Password MS SQL Query: CFQUERY name=GetUser datasource=MSSQL SELECT id, username, password FROM user /CFQUERY PostgreSQL Query: CFQUERY name=GetUser datasource=PostgreSQL SELECT ID, UserName, Password FROM User /CFQUERY In our shop we user lowercase names for everything in our databases, so I haven't had to do that for a long time when working with PostgreSQL. As such, I cannot remember if it was single quotes, or double-quotes that I had to use when dealing with names that had uppercase values in them. Still, you get the idea. With the exception of those two items, PostgreSQL is wonderful. The price is great, the performance is great, and the community support is great. I would highly recommend PostgreSQL to *anyone* looking for a high-performance, low cost alternative to MS SQL. Hope this helps! Warm regards, Jordan Michaels Vivio Technologies http://www.viviotech.net/ Blue Dragon Alliance Member [EMAIL PROTECTED] Matt Quackenbush wrote: Hello, I've always used MSSQL and quite frankly am quite fond of it - except for licensing costs. I've read a few posts here and there where people have talked highly of PostgreSQL, so I'm thinking about giving it a shot on a new server (windows box). I mentioned this to a buddy of mine who is a DBA, and he said to steer well clear of it. While I value his opinion greatly, I was wondering if the users here would care to share their personal + vs. - arguments for PostgreSQL? Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266021 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: PostgreSQL vs. MSSQL
Matt, I'll offer my 2 cents: I use both Pg and MySQL daily now. I used MSSQL for quite a time, but being a small company, when I started to think about scaleability and long-term costs I moved all of my projects over to MySQL. Most of my external projects, however, run Pg, so I spend quite a bit of time with it. Here's my take to some of your concerns raised in a follow to your post: - non-standard sql Yes, though mostly on Create/Alter. The query syntax for 90% of what you do is the same. I find their serial sequencing (auto- incrementing) to be a bit of a pain compared to MySQL or MSSQL, but Pg has a really rich set of aggregate functions that I like more and more every day. - difficult to backup / migrate I find pg_dump to be very easy to migrate between my dev DB server and remote machines. Backing up is easy, as Jordan said. - user authenication is weak Like Jordan, I very much disagree. I'm not a DBA, but in my experience it's very strong. - difficult to setup Not at all. The installer for Pg9 on Windows, is very good. Linux, of course, is well-documented and binaries are available or you can compile. - difficult to manage Not a DBA, again, but between PgAdminIII (Their Windows Client) and my main DB client, Aqua Data Studio, I'm pretty much set in the office. I use phpPgAdmin on remote machines and that works well too.Their documentation is excellent as well. - case sensitivity I've been bitten a couple of times by this, but you can pass a mixed case query without quotes to Pg and it will handle it fine for the most part. I help to maintain an existing application that has to be compatible with MSSQL,MySQL,Pg and Access and there are quite a few queries in there, running every day on Postgres, that have mixed case column names. As long as you pass the values unquoted, they are converted to lower case in translation. See: http://www.postgresql.org/docs/8.0/ static/sql-syntax.html In other words, you may have to do some tweaking, but you won't have t touch every query to migrate. HTH, Jon On Jan 8, 2007, at 2:01 PM, Matt Quackenbush wrote: Hello, I've always used MSSQL and quite frankly am quite fond of it - except for licensing costs. I've read a few posts here and there where people have talked highly of PostgreSQL, so I'm thinking about giving it a shot on a new server (windows box). I mentioned this to a buddy of mine who is a DBA, and he said to steer well clear of it. While I value his opinion greatly, I was wondering if the users here would care to share their personal + vs. - arguments for PostgreSQL? Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266023 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
