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?

Michael Buen · Accepted Answer · 2019-04-07 02:26:25Z

20

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:
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

edited Apr 7, 2019 at 2:26

answered Feb 17, 2011 at 0:43

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

arnorhs Over a year ago

It would be really nice/helpful if you could talk a little bit about why you like "index on expression", "user defined aggregates", "richer datatypes", "array support", etc.. and how you've used those in the past

Thomas Over a year ago

I"m not a fan of the functional dependency when grouping on the PK. MySQL has a similar feature and I wish no DB vendor implemented it. Far better to be explicit about how it is choosing the other fields not referenced in Group By clause even if the Group By column is a PK.

Frank Heikens Over a year ago

@Thomas: MySQL doesn't check anything, that's not the same as functional dependency on the primary key. Just download an alfa release of 9.1 and see the difference between both implementations.

wildplasser Over a year ago

@MichaelBuen: excellent list! You could add DOMAINs ; extremely useful wrt constraint minimisation. (and standard, as opposed to UDTs)

Joel Coehoorn Over a year ago

Add LATERAL JOIN to the list.

|

Frank Heikens · Accepted Answer · 2011-02-17 07:46:52Z

7

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.

answered Feb 17, 2011 at 7:46

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Comments

score 7 · Accepted Answer · 2011-02-17 22:52:03Z

7

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

edited Feb 17, 2011 at 22:52

answered Feb 17, 2011 at 8:07

user330315

2 Comments

arthurprs Over a year ago

The check constraints are extremely useful. I think MINUS is in oracle, it's EXCEPT in postgresql.

user330315 Over a year ago

@Arthur: Thanks, you are right. EXCEPT is the standard and that's what PG uses.

Thomas · Accepted Answer · 2011-02-17 00:24:33Z

3

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

Comparison of different SQL implementations

answered Feb 17, 2011 at 0:24

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

Comments

Xavon_Wrentaile · Accepted Answer · 2013-08-10 15:23:21Z

2

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

answered Aug 10, 2013 at 15:23

Xavon_Wrentaile

1513 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

9 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

9 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related