[BioSQL-l] Exploring data in database

[Hilmar Lapp]

On Thursday, March 27, 2003, at 01:58  AM, Kris Boulez wrote:

> From different sides I here good stuff about PostgreSQL. I've been 
> using
> MySQL for years, just because it was there and did the job.
> Is there some documentation that describes the differences between 
> MySQL
> and PostgreSQL ? I found multiple manuals on the web site, but I would
> rarther not have to wade through all these pages to find the
> syntax/concepts that make PostgreSQL special.
>

There is few pieces in SQL syntax that are special to Postgres; there 
is much more in MySQL's implementation that is non-standard SQL. I 
don't know whether there is a direct comparison or 'migration' guide. 
Generally speaking, PostgreSQL has all the features that you expect 
from a modern RDBMS like, but not limited to,

	-transactions and transaction isolation
	-enforced integrity constraints
	-views
	-subselects, and subselects used as tables
	-triggers
	-stored functions and procedures

One of the notable differences when you start with simple tables is 
that there is no auto_increment. Instead you use the mechanism you find 
in I think all modern RDBMSs, namely sequences. You define a sequence, 
which is basically a transaction-safe primary key generator, and 
default the primary key column to the next available number from that 
sequence. There is even a PostgreSQL-specific shortcut SERIAL which 
essentially translates into the aforementioned steps.

PostgreSQL offers a bunch of things on top of its SQL92 implementation, 
like user-defined types, geometric types, inheritance, function 
indexes, etc. These come in handy sometimes.

I guess you don't really want to skip reading a PostgreSQL manual 
altogether. There is one on-line at www.postgresql.org, which is very 
useful (at least it has been to me). You don't  need to read the 
section on what a SELECT statement is (although you should acquaint 
yourself with subselects, if you have never taken advantage of that).

	-hilmar
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------