Moving from mysql to postgresql, best features I was missing?

Question

I used to develop everything with mysql, this week an opportunity to work with postgresql appeared, why not!

I was always told that postgresql had a much bigger feature set.
I read some wikis, but most of the info are really outdated.

What are the best features I was missing? Like partial indexes, etc..
Also, I will miss something from mysql?

Answer 1

• One of the nicest feature of

  • Postgresql is index on expression:
  • http://www.postgresql.org/docs/9.0/interactive/indexes-expressional.html
  • http://www.ienablemuch.com/2010/12/postgresql-speeding-up-slow-coalesce.html

• User-defined aggregates:

  • http://developer.postgresql.org/pgdocs/postgres/xaggr.html
  • How to query sum previous row of the same column with with pgSql
  • http://www.ienablemuch.com/2011/02/how-to-find-second-best-grade-of_06.html

• CTE, supports recursion

  • http://www.ienablemuch.com/2010/04/simple-hierarchical-query-display.html

• generate_series

  • http://www.ienablemuch.com/2010/12/date-functions.html

• Windowing functions:

  • http://www.ienablemuch.com/2011/01/postgresql-lag-windowing-function.html

• Superb date functions

  • http://www.ienablemuch.com/2010/12/finding-previous-day-of-week.html

• Array support

  • http://www.ienablemuch.com/2010/12/any-clause-is-superior-to-in-clause-at.html
  • http://www.ienablemuch.com/2010/05/postgresql-users-are-spoiled-with.html

• Richer data types

  • http://www.ienablemuch.com/2010/05/varchar-ip-address-sorting.html

• Functional dependency on primary keys when grouping on it (on next version, 9.1)

  • http://www.ienablemuch.com/2010/08/postgresql-recognizing-functional.html

• User-defined operator

  • Adding sum of current_timestamp and days column in Postgres

• DISTINCT ON

  • How to get the parent given a child in SQL SERVER 2005

• LATERAL JOIN

  • http://www.anicehumble.com/2013/09/sql-server-said-postgresql-said-apply-lateral.html

• Custom data type using DOMAIN

  • http://www.sqlines.com/postgresql/how-to/create_user_defined_type

• Passing the whole row to function

  • SQL SELECT statement expression value reuse for other expression

Answer 2

And don't forget the DDL, it's also transaction safe:

BEGIN;
  ALTER TABLE foo DROP COLUMN bar;
  ALTER TABLE foo ADD COLUMN baz INET;
COMMIT;

Great for maintenance work, you will always have a consistent database, even when you lose the database connection or the server goes down.