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

Question

I used everything with mysql for development, this week I got the opportunity to work with postgresql, why not!

I've always been told that postgresql has a much larger feature set.
I read some wiki, but most of the information is really out of date.

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

Answer 1

• One of the nicest features

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

• Custom aggregates:

  • http://developer.postgresql.org/pgdocs/postgres/xaggr.html
  • How to query the sum of the previous row of the same column using 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

• Window functions:

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

• Superb Date Features

  • 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 dependence on primary keys when grouped by them (in the next version, 9.1)

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

• User statement

  • Adding the sum of the current_timestamp and days column to Postgres

• DIFFERENT IN

  • How to get the parent in SQL SERVER 2005

• Side connection

  • 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 entire string to a function

  • Reusing the value of an SQL SELECT statement expression for another expression

Answer 2

And do not forget DDL, it is also safe for transactions:

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 if you lose the connection to the database or the server goes down.

Answer 3

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

• check restrictions
• table functions (functions that can be used in 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 does not delete the entire table
• you may have a subquery in UPDATE or DELETE that selects from the same table that you are updating
• much more intelligent query optimizer
• pending restrictions (rarely used, but when you need them, they are really useful)
• foreign keys are evaluated for the entire statement, not line by line
• full-text search and spatial extensions in transactional tables
• BESIDES

Answer 4

Here is a link listing the differences in functions between many of the major database products:

Comparing various SQL implementations

Answer 5

FULL EXTERNAL COMPOUNDS. The lack of this data is one of my biggest complaints about MySQL. But Postgresql supports them.