Thread: ANSI Standard
<div class="Section1"><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">Hi,</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">How do I know if a function (or a certain sql syntax) in Postgres is a SQL ANSI Standard, hence it workson all databases such as MySQL, SQL Server, Oracle…</span></font><p class="MsoNormal"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt; font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">I noticed that concat, decode, nvl, instr are functions that work for some databases and don’t for others.</span></font><pclass="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">I only want to use ANSI Standard functions that work everywhere.</span></font><p class="MsoNormal"><fontface="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">How do I identify them?</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">How do I know if a function is specific to Postgres and don’t work for other databases.</span></font><pclass="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">Sorry, I may have asked the same question more than a time</span></font><p class="MsoNormal"><font face="Verdana"size="2"><span style="font-size:10.0pt; font-family:Verdana">But I just wanted it to be clear</span></font><p class="MsoNormal"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt; font-family:Verdana">I’d appreciate any reply to my question</span></font><p class="MsoNormal"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt; font-family:Verdana">Thanks</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">Pascal </span></font></div>
On Tue, Jun 24, 2008 at 7:33 AM, Pascal Tufenkji <ptufenkji@usj.edu.lb> wrote: > How do I know if a function (or a certain sql syntax) in Postgres is a SQL > ANSI Standard, hence it works on all databases such as MySQL, SQL Server, > Oracle… > I noticed that concat, decode, nvl, instr are functions that work for some > databases and don't for others. > > I only want to use ANSI Standard functions that work everywhere. Well the problem is that most database do not support that same full set of ANSI features. So if you design your schema with a wide range of ansi features, certain ddl will probably not commit or at least will be ignored on most databases. We database brand has incorporated a different set of features. > How do I identify them? This should get you started : http://www.postgresql.org/docs/8.3/interactive/features.html -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
At 02:20 AM 6/25/2008, pgsql-sql-owner@postgresql.org wrote: >Date: Tue, 24 Jun 2008 17:33:11 +0300 >From: "Pascal Tufenkji" <ptufenkji@usj.edu.lb> >To: <pgsql-sql@postgresql.org> >Subject: ANSI Standard >Message-ID: <000601c8d607$3acda550$150fa8c0@interne.usj.edu.lb> > >Hi, > > > >How do I know if a function (or a certain sql syntax) in Postgres is a >SQL >ANSI Standard, hence it works on all databases such as MySQL, SQL >Server, >Oracle. In general, I find that the Pg docs pretty clear state what is ANSI standard and what isn't within Pg. You can also view the ANSI-92 standard here: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt In terms of making sure you're cross platform compatible, I'd say you have to designate a series of platforms (e.g. MySQL 5, Pg 8.3, Oracle X, MS SQL X, ext) which you will test against and explicitly support. You will find that no matter how tightly you attempt to build your platform against ANSI-92 (or any other std) if you do not regularly test against a set of platforms, your solution will converge on supporting only the platforms you do regular test against. I hope that helps, Steve
<br />- Try SQL 2003 standards....92 is way old<br />- You'll find that even the big boys like Oracle, DB2 etc will divergefrom SQL standards if they make more $$ thier way...let alone toys like MySQL and MS-SQL<br /><br />Cheers<br /> Medi<br/><br /><div class="gmail_quote">On Thu, Jun 26, 2008 at 12:19 PM, Steve Midgley <<a href="mailto:public@misuse.org">public@misuse.org</a>>wrote:<br /><blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> At 02:20 AM 6/25/2008, <ahref="mailto:pgsql-sql-owner@postgresql.org" target="_blank">pgsql-sql-owner@postgresql.org</a> wrote:<br /><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:1ex;"> Date: Tue, 24 Jun 2008 17:33:11 +0300<br /> From: "Pascal Tufenkji" <<a href="mailto:ptufenkji@usj.edu.lb"target="_blank">ptufenkji@usj.edu.lb</a>><br /> To: <<a href="mailto:pgsql-sql@postgresql.org"target="_blank">pgsql-sql@postgresql.org</a>><br /> Subject: ANSI Standard<br />Message-ID: <000601c8d607$3acda550$<a href="mailto:150fa8c0@interne.usj.edu.lb" target="_blank">150fa8c0@interne.usj.edu.lb</a>><br/><br /> Hi,<br /><br /><br /><br /> How do I know if a function (ora certain sql syntax) in Postgres is a SQL<br /> ANSI Standard, hence it works on all databases such as MySQL, SQL Server,<br/> Oracle.<br /></blockquote><br /> In general, I find that the Pg docs pretty clear state what is ANSI standardand what isn't within Pg. You can also view the ANSI-92 standard here:<br /><br /><a href="http://www.contrib.andrew.cmu.edu/%7Eshadow/sql/sql1992.txt" target="_blank">http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt</a><br/><br /> In terms of making sure you're crossplatform compatible, I'd say you have to designate a series of platforms (e.g. MySQL 5, Pg 8.3, Oracle X, MS SQL X,ext) which you will test against and explicitly support. You will find that no matter how tightly you attempt to buildyour platform against ANSI-92 (or any other std) if you do not regularly test against a set of platforms, your solutionwill converge on supporting only the platforms you do regular test against.<br /><br /> I hope that helps,<br /><br/> Steve<br /><font color="#888888"><br /><br /> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"target="_blank">pgsql-sql@postgresql.org</a>)<br /> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br />