Thread: Postgres and MySQL Rosetta stone??

Postgres and MySQL Rosetta stone??

From
leonardz
Date:
I am reading through HeadFirst SQL (an OReilly book) which uses
specific MySQLisms in its examples. I use postgres on our databases.
Some examples are easy to work around, some are not .

Is there a rosetta stone (table of commands in one and equivalents
inthe other) available?

In particular, MySQl seems to have richer string functions to parse
out sub strings and als trim a string for automatic table insertion
from long multifield strings.

Thanks.

Re: Postgres and MySQL Rosetta stone??

From
Erik Jones
Date:
On Jan 9, 2008, at 12:19 PM, leonardz wrote:

> I am reading through HeadFirst SQL (an OReilly book) which uses
> specific MySQLisms in its examples. I use postgres on our databases.
> Some examples are easy to work around, some are not .
>
> Is there a rosetta stone (table of commands in one and equivalents
> inthe other) available?

There are a few compatibility lists/articles around (have you tried
Google?), but they're often out of date since both databases are in
constant, active development.  I'd suggest getting a different book
that gives examples in Postgres or, failing that, read the Postgres
manual and learn how to translate them yourself.  There is a Postgres
list dedicated specifically to SQL/query questions as well (pgsql-sql@).

>
> In particular, MySQl seems to have richer string functions to parse
> out sub strings and als trim a string for automatic table insertion
> from long multifield strings.


PostgreSQL has extensive string maniputation functions:  http://
www.postgresql.org/docs/8.2/interactive/functions-string.html

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




Re: Postgres and MySQL Rosetta stone??

From
"Scott Marlowe"
Date:
On Jan 9, 2008 12:19 PM, leonardz <leonardz@sympatico.ca> wrote:
> I am reading through HeadFirst SQL (an OReilly book) which uses
> specific MySQLisms in its examples. I use postgres on our databases.
> Some examples are easy to work around, some are not .
>
> Is there a rosetta stone (table of commands in one and equivalents
> inthe other) available?

I've found that a familiarity with the pgsql docs is a necessity when
using examples from other dbs.  Knowing where ot look is half the
battle.

> In particular, MySQl seems to have richer string functions to parse
> out sub strings and als trim a string for automatic table insertion
> from long multifield strings.

Have you read the postgresql manual on string functions?  Seriously,
it's easily a match for MySQL in that arena, and beats it hands down
in a few.

http://www.postgresql.org/docs/8.2/static/functions-string.html

Trimming a string automatically to fit (I think that's what you're
alluding to there) is broken behaviour.  I.e. if I have a varchar(10)
and insert the string 'abcdefghijklmnopqrstuvwxyz' I SHOULD get an
error.  That string's too big.  I don't want my database chopping up
my data as it sees fit by default.  Especially since in pgsql it's
quite easy to setup a trigger to "do the right thing" whatever I might
deem that to be.

I'd suggest loading up the latest mysql stable release as well as
postgresql and playing around with the examples in mysql first to make
sure you've got them down, then translating them to postgresql.  It's
a good training exercise, and will improve your skills in two dbms
instead of just one.

I also think (much as Erik stated) that you'd be better off with a
pgsql, or at least ansi standard SQL oriented book.

Re: Postgres and MySQL Rosetta stone??

From
Ron Mayer
Date:
Scott Marlowe wrote:
>> In particular, MySQl seems to have richer string functions to parse
>> out sub strings and als trim a string for automatic table insertion
>> from long multifield strings.
>
> Have you read the postgresql manual on string functions?  Seriously,
> it's easily a match for MySQL in that arena, and beats it hands down
> in a few.

And if you need more - like extracting Noun Clauses from sentences
you can do that with PostgreSQL and Pl/Perl using natural language
processing libraries that are callable from perl.

I'd be interested to know how MySQL would handle that kind
of string processing function.

Re: Postgres and MySQL Rosetta stone??

From
Martin
Date:
In article <3FB8FB7A-A325-4D9C-AF4A-037503FA011D@myemma.com>,
Erik Jones <erik@myemma.com> wrote:

>I'd suggest getting a different book that gives examples in
>Postgres or, failing that, read the Postgres manual and learn
>how to translate them yourself.

I found O'Reilly's SQL Cookbook quite good for showing the
differences:

  http://www.cornhobble.com/am/0596009763

Every recipe includes discussion of how to approach the
problem in Orance, Postgresql, mysql, and M$.