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

  • 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

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.


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

Tags:

Mysql

Sql

Postgresql

Recent Posts