PostgreSQL vs Oracle

Comparison from a developer’s point of view

Immediately make a reservation - I have nothing against placing part of the business logic in the stored functions, if it is provided for in the system architecture and justified for a number of practical considerations that are beyond the scope of this article.

C Oracle I have an old and close relationship. I saw a lot of great architectures and code, and a lot of terrible "slap". Oracle provides the developer with an inexhaustible abyss of opportunities, and almost always there is the right thing right now.
Overall, Oracle is an amazing tool, and I'm not surprised at how all this wealth can work in principle and work stably.

About two years ago, I moved from the Enterprise World to free floating, where the Oracle machine with its $ 47k per core is out of reach.
One of the first freelance projects was a small billing for a sub-satellite operator. There was a question of choosing RDBMS. MySQL immediately disappeared due to the underdeveloped procedural language, the choice fell on PostgreSQL.
')
As I worked on this and the following projects, I compiled a list of the subjective advantages and disadvantages of PostgreSQL compared to Oracle from the point of view of the database developer. I present it to your attention:

PostgreSQL vs Oracle

PRO:

• Pseudo serial type - combines the best features of auto_increment from MySQL and sequence from Oracle.
• You can write functions in pure SQL . For example, a function consisting of one update c returning, which returns the identifier of the value just added. Allows you to get rid of the explicit declaration of variables in which data is selected and which are then returned to return.
• Wonderful with in which you can use not only select, but also insert, update, delete returning and which can be made recursive (replaces connect by) as a request. In addition, it replaces the Oracle multi-insert insert all.
• Generate_series instead of select type perversions from dual connect by level <n
• A very powerful data integrity control mechanism aka CONSTRAINTS - for example, EXCLUDE allows you to perform clever checks on OTHER rows when inserting a new one (otherwise you would have to write a trigger), REFERENCES (foreign key) with actions when deleting or modifying records that the table refers to. For example, constraint constname references tablename on delete cascade will delete the associated records when the parent is deleted.
• Remarkable, but potentially dangerous (as triggers) rule system ( RULES ) allowing to substitute the request text sent to the server. Through it, for example, implemented VIEW .
• A convenient WHERE clause in the index definition allows reducing the size of the index without creating functional indexes and unreadable conditions such as where decode (status, 1.1, null) = 1
• LIMIT with OFFSET , to avoid hemorrhoids with rownum, sorting and subqueries.
• Nice documentation, devoid of dryness and monstrous (but also meticulous) oraklovskoy.

CONS:

• Unpleasant syntactic anachronisms, such as the need to escape the body of HF, for example:
  

  	function test() returns void as
  	$$
  	begin
  	end;
  	$$ language plpgsql;
         

  
  perform, :
  perform my_proc(); my_proc();
• ( ).
• , (, cron).
• packages . , .
• +parallel ( ) ETL DWH- . — .
• MERGE.
• . , commit rollback .
• , . . , , Oracle.

DBA, , , . .

? PostgreSQL , — .