Re: Inquiry From Form [pgsql]

on 11/18/02 10:07 AM, scott(dot)marlowe(at)ihs(dot)com purportedly said:

>> I code with PHP and use it to communicate with MySQL, if I started
>> using PostgreSQL would I have to change my coding to communicate with
>> the database?
>
> Not really. The only issue is if you used MySQL proprietary stuff.
> There's a lot of things in MySQL that are workarounds for it not being a
> transactional database that won't work in Postgresql, but using the
> "right" method (i.e. a transaction or ANSI SQL) will work just fine.

There are other non-obvious gotchas. For instance, MySQL is much more
"tolerant" (to put it nicely) with column constraints. Take the following
table example:

CREATE TABLE some_table ( number INT NOT NULL, date DATE NOT NULL );

INSERT INTO some_table (date) VALUES ('2002-05-09');
-> MySQL accepts and sets "number" to 0
-> Postgres rejects with error (NOT NULL constraint)
Same goes if you insert only "number" and not date: Postgres rejects, MySQL
accepts with date value '0000-00-00'.

INSERT INTO some_table VALUES(1,'0000-00-00');
-> MySQL accepts
-> Postgres rejects with error--Postgres does not have empty dates or
times

INSERT INTO some_table VALUES(2, 2002-06-05);
-> MySQL accepts (has valid date constants)
-> Postgres rejects: dates must be quoted.

You should thoroughly test your application before making it live, as these
types of issues are not always obvious in the code. These gotchas are the
biggest problems for programmers who have learned SQL from MySQL, which,
IMHO, encourages sloppy SQL coding.

Other than these situations, the biggest difficulty with conversion is with
the database schema itself.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"