PHP-MySQL developer transitioning to PostgreSQL. What do I need to know?

Question

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?

Answer 1

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...)

Answer 2

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.