Two Important Differences Between SQL Server and PostgreSQL

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:

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:

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:

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:

But that doesn’t work either, because you can’t output data from a DO:

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

GREATEST is like MAX, but across columns. GREATEST and LEAST are two conditional expressions that we don’t get in SQL Server. Nifty.

Previous Post
[Video] Office Hours 2018/8/1 (With Transcriptions)
Next Post
A Visual Guide to Choosing an Index Type

22 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

    Reply
    • Steven Hibble
      August 6, 2018 1:10 pm

      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);

      Reply
  • 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

    Reply
  • Christopher S Case
    August 6, 2018 5:13 pm

    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. 🙂

    Reply
  • David Fetter
    August 6, 2018 9:13 pm

    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;“`

    Reply
  • 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!

    Reply
  • 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.

    Reply
  • Bea Poocherelli
    January 9, 2019 10:07 am

    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

    Reply
  • Bea Poocherelli
    January 9, 2019 10:33 am

    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.

    Reply
    • 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. 😉

      Reply
  • Bea Poocherelli
    January 9, 2019 10:43 am

    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.

    Reply
    • 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.

      Reply
  • Bea Poocherelli
    January 9, 2019 11:00 am

    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. 🙂

    Reply
  • Troy Witthoeft
    January 20, 2019 11:46 am

    These guys implicitly banter. Good read. Six out of ten stars.

    Reply
  • Brent, you are incredibly rude – wind your neck in

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}