The PHP-MySQL developer turns into PostgreSQL. What should I know?

advertisements

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?


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