SQL ConstantCare® uses PostgreSQL as a back end – specifically, AWS RDS Aurora – so I’ve spent a lot of time writing Postgres queries lately. Here are some of the things I’ve noticed that are different.
CTEs are optimization fences.
In SQL Server, if you write this query:
|
1 2 3 4 |
With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts) SELECT * FROM AllPosts WHERE Id = 1; |
SQL Server builds a query plan for the entire operation at once, and passes the WHERE clause filter into the CTE. The resulting query plan is efficient, doing just a single clustered index seek.
In Postgres, CTEs are processed separately first, and subsequent WHERE clauses aren’t applied until later. That means the above query works just fine – but performs horribly. You’ll get much better results if you include your filters inside each CTE, like this:
|
1 2 3 |
With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts WHERE Id = 1) SELECT * FROM AllPosts; |
That’s less than ideal.
You can’t just leap into an IF statement.
In SQL Server, you can just start typing conditional logic and execute it:
|
1 2 3 4 |
IF EXISTS (SELECT * FROM StackOverflow.dbo.Users) SELECT 'Yay' ELSE SELECT 'Nay'; |
That’s useful if you want to do conditional processing, set variables up, populate them for different scenarios, etc.
In Postgres, you have to do a little setup to declare that you’re doing procedural code:
|
1 2 3 4 5 6 7 8 |
DO $$ BEGIN IF EXISTS (SELECT * FROM rule) THEN SELECT 'Yay'; ELSE SELECT 'Nay'; END IF; END $$; |
But that doesn’t work either, because you can’t output data from a DO:
|
1 2 3 |
ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statement |
<sigh> You really want to create a function. Which reminds me: Postgres functions are the equivalent of SQL Server stored procedures. Sure, SQL Server’s user-defined functions have a really bad reputation: most of ’em get bad row estimates, inhibit parallelism, and cause performance tuners to point and giggle. Postgres functions? Totally different. Just basically stored procs.
And one less-important difference: GREATEST and LEAST.
Every now and then, I need to find the higher (or lesser) of two things in a row. Let’s say our dbo.Users table has two columns, UpvoteDate and DownvoteDate, and I’m trying to find the most recent date that they cast ANY kind of vote. Postgres has this really cool trick:
|
1 2 |
SELECT GREATEST(LastUpvoteDate, LastDownvoteDate) AS VoteDate FROM dbo.Users; |
GREATEST is like MAX, but across columns. GREATEST and LEAST are two conditional expressions that we don’t get in SQL Server. Nifty.
23 Comments. Leave new
Another thing I really love about PostgreSQL is the clear syntax when making selective aggregations:
select avg(price) filter (where price between 10 and 50) as avg_price
from product_store
I know it can be done in MS-SQL through cases but i really don’t like that sintax
Greetings
Similarly, PostgreSQL supports the WINDOW clause. It can make window functions much easier to read. Here’s an example from the doc
SELECT sum(salary) OVER w,
avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
Thought you might have been enjoying formating queries with identifier substitutions, %I, and executing them dynamicly like in you blitz tool
But chear up, real stored procedure are on the way with transaction control, although I don’t know when they will get to Arora .
https://www.postgresql.org/docs/current/static/functions-string.html
https://www.postgresql.org/docs/11/static/sql-createprocedure.html
If you can give pg some
A little curious about why the choice to use AWS RDS Aurora as opposed to an Azure offering? It just seems an interesting choice that ConstantCare is backed by PG and not SQL Server. 🙂
Christopher – sure, I blogged about that: https://www.brentozar.com/archive/2018/04/building-sql-constantcare-the-database-back-end/
We’re working on the CTE optimization fence for PostgreSQL 12.
With respect to IF, you need to understand that PostgreSQL lets you choose among
multiple languages for its functions (and as of the upcoming 11, stored
procedures). This means it can’t integrate tightly with any particular one of
them. That said, you can do what you expected as follows in SQL:
SELECT CASE WHEN EXISTS (SELECT 1 FROM StackOverflow.dbo.Users) THEN'Yay'
ELSE
'Nay'
END;
I thought for sure that “you can’t declare a variable” in PostgreSQL would have made the list. In the online technical test I had to do for my current role, the testing site they use doesn’t have SQL Server but has PostgreSQL. I had a good solution to the problem to solve but wanted to use a variable. Spent 15 minutes of my 45 remaining minutes trying to figure out how to work around that. First/only time working in it, but it really threw me for a loop. Once I realized I had to throw a function in there instead it was fine but it’s certainly something I wish I knew before building a solution while under a tight time constraint!
Another gotcha between the two! I use queries of this format in MS SQL routinely, to review my rows, then UPDATE.
SELECT b.*
-- UPDATE b SET orderId = 10
from A_TBL a
JOIN B_TBL b ON a.c_Id=b.c_Id
In Postgres 9.6, this same format query updates ALL rows in B_TBL with the same value! There is an alternate syntax to accomplish the same work.
Can Postgres do partition switching on a text field with discrete values, like country ? lets assume we need to update an entire country in a table partitioned by country. Can we populate a temp table or other mechanism with the new data and then just swap out the partitions as can be done with SQL Server ? Is it just as fast and non-interruptive as SQL Server ? Thanks
Bea – for random Q&A, head on over to a Q&A site like https://dba.stackexchange.com.
Since you asked and I’ve got some experience with it, figured I’d answer. In short, yes, postgres table inheritance allows you to inherit and uninherit tables without downtime. (Disclaimer: I’m a developer and not a DBA, so PG may have other ways to do partioning without inheritance, but this is how we use it.) Every few months my company loads a few hundred million record table from the government, and we swap out which is the inherited table live, not even a hiccup.
If you’re curious to learn more, the pg docs are a great place to start: https://www.postgresql.org/docs/11/tutorial-inheritance.html
Chris, thank you very much for your answer, its much appreciated, and your link was very helpful.
The reason I posted the question here was that it was in line with the context of your article title, in that it would be an important difference between the two, and that, as a SQL expert, you would be a respected source of information as to your personal experience with PostgreSQL. It was not meant to be random by any means, since I noticed that PostgreSQL people were also commenting to this article. I do appreciate your prompt reply though. Thank you for that.
Ah, sounds like you weren’t asking a question – sounds like you were trying to make a point about one platform being better than the other. That’s cool, but it’s not really a question. May want to phrase that a little better next time.
A friend of mine likes to say there are 3 kinds of questions: when someone wants to learn, when someone wants to show off something they think they know, and when someone fell asleep during the lecture. Make sure you’re asking the right questions. 😉
Fair enough, so the question I would like to ask so that I can learn, is can PostgreSQL do partition switching on a text field with discrete values ? I seriously do not know that answer, and I can’t seem to find the answer in any places I have searched. I will keep looking, I was hoping you would have some input. Thanks again.
Right – and for questions about databases, your best bet is a Q&A site on things like https://dba.stackexchange.com.
It’s kinda like when you’re hungry, you don’t walk up to random strangers saying, “Do you have a muffin? I’d really like a muffin. Do you have a blueberry one, by chance?” Of course not – you go to a coffee shop. That’s where the muffins are.
When you have a question, you go to a Q&A site. That’s where the A’s are.
Thank you for your input. A friend of mine likes to say that any answer to a question is revealing, even if its not the answer you are expecting. 🙂
These guys implicitly banter. Good read. Six out of ten stars.
Brent, you are incredibly rude – wind your neck in
Blow me.
[censored]
Hi John. Thanks for stopping by. Let’s keep the swearing in the comments down. I totally don’t mind you insulting me – feel free to do that as much as you like – but keep in mind that there are folks reading this blog from their work computers. Thanks for understanding!
A friend of mine says there are more questions than answers .. but the more I found out, the less I learnt!