Curious why have postgres and SQL server?
Sounds like they develop a product that can use multiple RDBMS back-ends. Also, I'm guessing that since 90% of the businesses out there use Microsoft products, you would probably want to support their products in such a venture.
On a personal note, I find anything that's not SQL Server to be tedious to work with due to the lack of high quality GUIs, client tools and certain features.
For instance - did you know that Postgres can't easily return multiple resultsets? In SQL Server I just write one procedure containing multiple SELECT statements and my client code can get all of those resultsets with only one call to that procedure. This one feature alone cuts down on all sorts of boilerplate code and trips to the database.
you can do that on postgres as well btw, my first idea would be to create a function [1] and just throw the rows together. still can't imagine a usecase for that though, i'd always want to get them separately if they're unrelated.
you're correct on the GUI side however. Everything i've tried is a buggy mess. i've actually mostly given up on that front and just went with pgcli [2]. its pretty good for ad hoc queries. But it doesn't really help if you're trying to write a function. Its just nice for selects with lots of joins or nested selects.
[1] https://www.postgresql.org/docs/9.1/static/sql-createfunctio... [2] https://github.com/dbcli/pgcli
I usually write queries that return multiple resultsets when the caller needs to get a bunch of data from different tables all at once. (So, all of the time.) Instead of sending each table query to the database server individually, I just query one "stored procedure" in SQL Server. It saves a lot of round-trips on the network and there are other benefits too.
For instance, say my API needs to take the relational data from tables `Customer, Order, OrderItem(s) and OrderShipment(s)` and return them as a JSON object `{order:{customer: {...}, items: [...], shipments: [...]}}`. Or, say I need to render a PDF using those same tables. In either case the client needs to get a whole graph of data out of the database and with SQL Server, they can only send one little query to call my procedure: `EXEC dbo.GetCustomerWithOrderDetail @OrderId=999;` instead of sending 4 or 5 single-resultset queries.
Sometimes I do also end up sending 2 or 3 procedure queries separately, for instance the client might send a query for `GetCustomerWithOrderDetail` followed by `GetPdfTemplateWithDetailForRendering`. So, I get code encapsulation too.
The benefits of this style are exponential in my opinion. Most of the time, my client apps don't even need to know the names of the tables because they are calling procedures, not embedding "SELECT * FROM TABLE" in the client code. The only minor drawback is that the client does need to know the order that the resultsets are returned in ahead of time.
I think there are a few ways to attempt this in PG but none of them seem to come very highly recommended. I looked around just now and found this post:
https://stackoverflow.com/questions/36717138/postgres-functi...
You can see the programmer wants to do what they need to in a very simple way, similar to how it would be done in SQL Server. However, it's not possible. Here is an article that explores some of the options in PG and the problems with each approach:
https://blog.dsl-platform.com/multiple-result-sets-alternati...
You might be "joining" client side if you had something like a master/detail UI. Network round trips are relatively slow and to be avoided if you can (n+1 errors being the pathological case):
https://people.eecs.berkeley.edu/~rcs/research/interactive_l...
Looks like it can, just not as easily: https://github.com/docevaad/Chain/wiki/Multiple-Result-Sets-...
Like the business intelligence features
Datawarehouse functionality Machine learning capabilities within the warehouse suite
Mdx (which enables things like power pivot / power query)
Myself I am a Postgres guys, but man those extras are really enticing to me as a problem solver.