Alan G wrote:
>> I've been using MySQL up this day, but would like to convert
>> my program to use Postgresql.
>
> I'm curious. Why?
> Is there some advantage to Postgres over MySql?
Yes and no. Postgresql offers more features and is IMO more flexible
than most SQL servers out there.
Example: postgresql enables you to set up editable sql views. This means
that you can concatenate several tables into a view (A stored SQL
query), and use this as if it was an ordinary table by adding, deleting
and editing data within it.
Very handy if you want to set up "infinite keyword" systems.
E.g. MemoTable with an integer "memoid" field and a text field for a "memo"
KeywordTable with an integer "keyid" field and a text "keyword" field.
MemoKeyTable with an integer "memokeyid" field, an integer
"memoid" field, and an integer "keyid" field - with a UNIQUE
index/constraint across the "memoid" and "keyid" fields.
To get the keywords for each memo, use a view that contains something
like the following SQL query. (Warning, the syntax is probably incorrect.)
FROM MemoTable, KeywordTable, MemoKeyTable GET MemoTable.memoid,
MemoTable.memo, KeywordTable.keyword
WHERE (MemoTable.memoid == MemoKeyTable.memoid) AND (KeywordTable.keyid
== MemoKeyTable.keyid)
SORT BY MemoTable.memoid;
In MySQL, this would let you retrieve the data from the tables, but
Postgresql will let you add entirely new memos, add a new keyword to a
memo, remove a memo cleanly, remove a keyword from a memo, etc. all from
the view.
However, MySQL is used a lot in web pages because it can return queries
much faster than any other database - the catch is that it can only do
this with transaction-less tables.
(I don't know your level of SQL experience, so please forgive me if I
explain things you already know.) Transactions enable you to have a
whole series of SQL commands fail if one fails. Useful if you want to
add interconnected data across a series of tables.
E.g. a finance company moving money from one account to another. You
definitely don't want the money to be removed from the first account
unless you can add it to the second account.
(MySQL can use transactions, however, you immediately lose the speed
advantage.)
Finally, Postgresql does some things differently from other databases
(Calling a function to fill a default value for autoincrementing, rather
than a dedicated SERIAL field type.) If you're planing to access the
database directly from python, this is no hassle, but if you want to use
it through something like OpenOffice.org or ODBC drivers, some things
don't work to well.
Hope that helped?
Joal Heagney
_______________________________________________
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
