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.

Answer 3

In addition to Michael's list (of which I like windowing functions the most)

• check constraints
• table functions (functions that can be used in like this select * from my_func(42)
• partial index (CREATE INDEX idx1 ON some_table (some_column) WHERE some_flag = true)
• division by zero is an error
• delete from some_table where 42 is considered an error and doesn't delete the whole table
• you can have a subquery in an UPDATE or DELETE that selects from the same table as you are updating
• much smarter query optimizer
• deferrable constraints (seldomly used, but when you need them, they are really helpful)
• foreign keys are evaluated for the whole statement not row by row
• full text search and spatial extensions on transactional tables
• EXCEPT

Answer 4

Here's a link that lists out the difference in features between many of the major database products:

Comparison of different SQL implementations

Answer 5

FULL OUTER JOINs. The lack of these is one of my biggest complaints about MySQL. But Postgresql supports them.