Re: Porting Code to Postgresql

On Wed, 15 Oct 2003, Errol Neal wrote:

> Hi all,
>
> Not sure if this is a question for a php list or this one, but I'll give it
> a shot and
> if I am wrong, please do not crucify me. :-)
>
> There is a php based sourceforge project called mailwatch.
> (http://www.sourceforge.net/projects/mailwatch) It logs data from the excellent
> Mailscanner security product into a mysql database. Now, I am not a php
> programmer,
> and I am barely a Postgres DBA, but I would really like to port the code to
> Postgresql.
> I have my trust Postgresql Book which covers the API for Postgresql and the
> PHP statements
> used for Postgresql seem almost identical to those used for Mysql. I
> understand that there are
> some slight differences in the data types supported by Mysql and
> Postgresql, however are the differences
> between the two Databases and API's that great to make task impossible for
> an unexperienced person
> such as myself? We currently use Postgresql in conjunction with sendmail to
> store our access, mailertable
> and other db's, so it would be very convenient for us to achieve this.

The issues you're likely to hit are twofold:

The first is that MySQL silently accepts that which postgresql may reject.
Look for errors on insert when this happens. for example, in postgresql,
you use the SQL keyword DEFAULT when inserting an autoincrement field, or
leave it out of your list of inserted fields. In MySQL you insert,
counterintuitively, a NULL to do the same thing.

The second is the lack of a mysql_last_id type function. While you can
get the last OID of an insert in postgresql, this is discouraged, as OIDs
may or may not exist for a given table depending on how it was declared.
Tis better to use the currval() / nextval() functions for such things.

All the rest if pretty straight forward hacking, having converted or
helped to convert a few other MySQL tools to Postgresql recently.