I've developed most of my applications in PHP-MySQL, because it was quick and easy. Now, with more complex applications and I'm wondering if MySQL is a good choice. I'll be building my latest application with PostgreSQL. What are things I need to be aware of? What was I missing when using MySQL?
Add a comment
|
2 Answers
This Wiki page is a good start:
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL
Edit: to answer the second part (things you have been missing):
- generate_series()
- deferrable constraints
- check constraints
- recursive queries
- table functions
- common table expressions
- windowing functions
- function based index
- partial indexes
- full text search on transactional tables
- GIS features on transactional tables
- MINUS or INTERSECT operator
Edit2: things you might find problematic
- PostgreSQL is far more strict in terms of matching datatypes (where character_column = 1 will throw an error)
- no cross-database queries, if you need something like that, mapping MySQL databases to PostgreSQL schemas is probably easier
- No variables in regular SQL statements (set @nr = 1; select @nr + 1...)
3 Comments
binaryLV
I'm not a PG user, so I might be wrong, but if I remember correctly, sequences in PG get updated even if
insert query fails (e.g., if NULL value is being added into non-NULL field). This is different from MySQL's auto_increment, which gets updated only on successful insert.
Frank Heikens
@binaryLV: innoDB uses locks on auto_increment, that can be a problem. The reset of the auto_increment to the current highest value after restart of the server, can be another problem. You just can't compare auto_increment in MySQL and a sequence in PostgreSQL or any other database. Different things.
binaryLV
Sure, they are different things, but people that are used to MySQL's
auto_increment tend to use sequences in other DBMS to simulate auto_increment. As author is used to MySQL, I thought it would be worth noting that the results with sequences might be different from those with auto_increment.Read the fine manual, chapters 2 - 9 are the most important ones to start with.
Make sure you do some proper error handling in PHP and read all error messages carefully: In most cases it tells you exactly what went wrong and how to fix it. Appendix A has all error messages and codes, you need them. PostgreSQL doesn't accept wrong input or queries, it's correct or you get an error to start debugging. And that's good, less bugs and less time you will spend on scripting.
pg_query_params() and pg_fetch_all() are some great functions in PHP to interact with PostgreSQL, check the PHP manual.
answered Feb 2, 2011 at 11:59
5 Comments
binaryLV
About
pg_query_params() and pg_fetch_all() - it's a matter of taste, of course, but I would use PDO - it's quite handy when you have to change DBMS from time to time.Frank Heikens
PDO is nice, but you won't get the best possible performance: COPY is not going to work, very fast insert's will be out of the question using PDO. You PHP-code will also be more complex using PDO.
binaryLV
Performance might be lower, but it would be worth testing if that might be a bottleneck. I personally have had no problems with performance because of PDO. About code being more complex - could you explain?
Frank Heikens
@binaryLV: Take a look at pg_query_params(), by far the most simple function to use prepared statements to avoid SQL injection. PDO has PDOStatement, but that's not half as simple as pg_query_params(). A function like pg_put_line() doesn't exist in PDO, that makes PDO a NoGo for me, lack of performance for large csv imports. PDO has it's pro's, but also it's conn's: You will never get the best out of any database if you want to support all databases using the same code.
binaryLV
for PDO there's
PDO->prepare($query) and PDOStatement->execute($params), followed by PDOStatement->fetchAll() - not complex at all. And sometimes it's good to write some wrapper-class for things like this, so it becomes something as simple as $data = $db->select($query, $params). P.S. As I already wrote, it's a matter of taste (and needs). For some it's a NoGo, for some - very handy tool to get the job done.