PostgreSQL <> MySQL: first draft

Attached are two text files:
pgsql_mysql.txt
- an annotated list of PostgreSQL features not available in MySQL;
pgsql_mysql-short.txt
- a condensed version of the above.

The document covers the major differences, though it is not
exhaustive (and full of spleling errors). Corrections, suggestions for
improvement gratefuly received. Or if someone wants to wikify it or,
whatever, fine by me ;-). I've tried to make it readble, i.e. something
to show the technically-aware PHB.

I will be away from tomorrow and though I will have Internet access
I might not have time to read the mailing list for a week or so.

It's taken me longer than I though due to other committments and partly
because of the level of involvment, I keep getting sidetracked by
interesting things in the documentation.

One area where I haven't had time to go into any detail is
MySQL's transaction model. Of particular interest would be
why it just took a MySQL InnoDB table nearly 30 minutes to
rollback about 270,000 insertions when PostgreSQL did it
almost instantly.

On a related note: since I posted the link to the MySQL gotchas
the site has unexpectedly found its way into Google and about
half the hits are from people who've pasted their MySQL error
message into the search field; and I also got a mail from MySQL
in Germany saying how well researched it is and they had comments
on three of the points which I've addressed in various ways.

Ian Barwick
barwick(at)gmx(dot)net

Mention partial and expressional indexes under the Indexes section - they're
biggies that MySQL doesn't have.

Chris

> On a related note: since I posted the link to the MySQL gotchas
> the site has unexpectedly found its way into Google and about

Google does a good job indeed. I remembered that a link to the page was
posted but I didn't have access to my mail archive. So the natural thing
to do was to search google for 'mysql gotchas'. Guess what page came out
on top :)

Kaarel

> Stored Procedures
> -----------------
>
> Stored procedures are blocks of code stored and executed in
> the database backend. They are typically created in an SQL-like procedural
> language or a fourth-generation language such as Java or Perl.
>
> - MySQL has no support for any form of stored procedure. Implementation
> is planned at an unspecified future date:
> http://www.mysql.com/doc/en/ANSI_diff_Triggers.html
>
> - PostgreSQL provides mature and tested support for function creation
> in SQL and the following procedural languages:
> PL/PgSQL (PostgreSQL's own procedural language, similar
> to e.g. Oracle's PL/SQL)
> Perl
> Python
> Tcl

pl/ruby exists too:

http://moulon.inra.fr/ruby/plruby.html

-sc

--
Sean Chittenden

Sean,

For the list:

http://www.linuxworld.com.au/index.php?id=1482975508&fp=2&fpid=1

--
-Josh Berkus
Aglio Database Solutions
San Francisco

> http://www.linuxworld.com.au/index.php?id=1482975508&fp=2&fpid=1

:) Already on there. :)

http://sean.chittenden.org/postgresql/news.html

-sc

--
Sean Chittenden

> > - PostgreSQL provides mature and tested support for function creation
> > in SQL and the following procedural languages:
> > PL/PgSQL (PostgreSQL's own procedural language, similar
> > to e.g. Oracle's PL/SQL)
> > Perl
> > Python
> > Tcl
>
> pl/ruby exists too:
>
> http://moulon.inra.fr/ruby/plruby.html

And pljava (aka plpga):

pljava.sourceforge.net

Chris

On Tue, 16 Sep 2003, Christopher Kings-Lynne wrote:

> > > - PostgreSQL provides mature and tested support for function creation
> > > in SQL and the following procedural languages:
> > > PL/PgSQL (PostgreSQL's own procedural language, similar
> > > to e.g. Oracle's PL/SQL)
> > > Perl
> > > Python
> > > Tcl
> >
> > pl/ruby exists too:
> >
> > http://moulon.inra.fr/ruby/plruby.html
>
> And pljava (aka plpga):

:)) Yes but there is no working version of it on the network. But don`t
believe the sf.net statistics, we are working on it offline. Net
connection is too expensive :(

(The word java is trademarked both by people of Java island or Sun
microsystems inc. so we can not call it 'java'. Hopefully the 'j' wont be
a trademark of anyone.)

>
> pljava.sourceforge.net
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

Don't forget plPHP ;)

Hornyak Laszlo wrote:

>On Tue, 16 Sep 2003, Christopher Kings-Lynne wrote:
>
>
>
>>>>- PostgreSQL provides mature and tested support for function creation
>>>> in SQL and the following procedural languages:
>>>> PL/PgSQL (PostgreSQL's own procedural language, similar
>>>> to e.g. Oracle's PL/SQL)
>>>> Perl
>>>> Python
>>>> Tcl
>>>>
>>>>
>>>pl/ruby exists too:
>>>
>>>http://moulon.inra.fr/ruby/plruby.html
>>>
>>>
>>And pljava (aka plpga):
>>
>>
>
>:)) Yes but there is no working version of it on the network. But don`t
>believe the sf.net statistics, we are working on it offline. Net
>connection is too expensive :(
>
>(The word java is trademarked both by people of Java island or Sun
>microsystems inc. so we can not call it 'java'. Hopefully the 'j' wont be
>a trademark of anyone.)
>
>
>
>
>>pljava.sourceforge.net
>>
>>Chris
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>

On Mon, 2003-09-15 at 21:05, Christopher Kings-Lynne wrote:
> > > - PostgreSQL provides mature and tested support for function creation
> > > in SQL and the following procedural languages:
> > > PL/PgSQL (PostgreSQL's own procedural language, similar
> > > to e.g. Oracle's PL/SQL)
> > > Perl
> > > Python
> > > Tcl
> >
> > pl/ruby exists too:
> >
> > http://moulon.inra.fr/ruby/plruby.html
>
> And pljava (aka plpga):
>
> pljava.sourceforge.net
>

plr (http://www.joeconway.com/plr/)
plsh (http://developer.postgresql.org/~petere/plsh.html)
plmono (http://gborg.postgresql.org/project/plmono/projdisplay.php)

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Guys,

Let's not re-invent the wheel here, hey?

http://techdocs.postgresql.org/guides/PLLanguages

--
Josh Berkus
Aglio Database Solutions
San Francisco