Thread: Postgres and MySQL Rosetta stone??
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.
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
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.
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.
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$.