Press "Enter" to skip to content

Curated SQL Posts

How Postgres Parses Conditional Expressions in PL/pgSQL

Published 2024-11-04 by Kevin Feasel

Deepak Mahto parses a command:

At first glance, this code block seems incomplete. Notice the IF condition: it appears to be missing an additional condition after the AND operator. Logically, this should cause an exception due to the incomplete condition following AND.

  IF i = 0 AND THEN

However, during PL/pgSQL execution, the condition is evaluated without any syntax errors or warnings. This raises a critical question:

How does PostgreSQL internally process this condition?
What allows this seemingly incomplete expression to work?

Read on for those answers.

Leave a Comment

Replacing Blanks with Zeroes in DAX

Published 2024-11-04 by Kevin Feasel

Chris Webb takes us from zero to blank in DAX:

My post from a few months ago about the dangers of DAX measures that never return blank attracted quite a lot of attention – this is a hot topic on the forums and adding zeros to measures is a common cause of memory errors in Power BI. In that post, though, I didn’t talk about what the best way to replace blanks with zeros is if you absolutely have no choice but to do so. One of the comments on that post mentioned that visual calculations is an option and this is something I hadn’t thought about before; now, after conversing with the Gods of DAX (no, not the Italians, I mean Akshai, Marius and Jeffrey!) and doing some testing I can reveal that Visual Calculations can be a good choice sometimes, while more traditional DAX approaches are fine at other times.

Read on to learn different techniques for doing this, as well as when they make sense to use.

Leave a Comment

Finding Foreign Key Constraints without Backing Indexes

Published 2024-11-04 by Kevin Feasel

Andy Brownsword goes searching:

That’s all well and good if we’re creating a new foreign key and adding our own index. How do we find existing foreign keys which aren’t yet indexed?

That’s where the query below comes into play.

We use a CTE to find foreign keys which are already indexed. From this we can then return details of other foreign keys along with scripts to create (and roll back) an index to mirror the key:

Click through for the script and notes on how it all works.

Leave a Comment

Using the OR Operator in R

Published 2024-11-01 by Kevin Feasel

Steven Sanderson asks, ketchup or catsup:

The OR operator is a fundamental component in R programming that enables you to evaluate multiple conditions simultaneously. This guide will walk you through everything from basic syntax to advanced applications, helping you master logical operations in R for effective data manipulation and analysis.

Click through for several examples.

Comments closed

Finding Mutable and Immutable Properties in Microsoft Fabric Spark

Published 2024-11-01 by Kevin Feasel

Sandeep Pawar wants to make a change:

Spark properties are divided into mutable and immutable configurations based on whether they can be safely modified during runtime after the spark session is created.

Mutable properties can be changed dynamically using spark.conf.set() without requiring a restart of the Spark application – these typically include performance tuning parameters like shuffle partitions, broadcast thresholds, AQE etc.

Immutable properties, on the other hand, are global configurations that affect core spark behavior and cluster setup and these must be set before/at session initialization as they require a fresh session to take effect.

Read on to see how you can tell which is which.

Comments closed

The Downside of UNISTR()

Published 2024-11-01 by Kevin Feasel

Solomon Rutzky shares some thoughts:

Since the new UNISTR function doesn’t provide new functionality, only convenience (“syntactic sugar” as some would say; see comment below), I would argue that it should not only use a more standard syntax, but also not waste the opportunity and provide more substantive convenience by handling several commonly used escape sequences. I suspect that the number of times people would use “\n” is several orders of magnitude more than the number of times people would inject emojis or other non-keyboard characters. Even better would be to incorporate common escape sequences into standard string parsing.

Read on for Solomon’s comment explaining why he is not a fan of UNISTR().

Comments closed

Cost Optimization in Azure

Published 2024-11-01 by Kevin Feasel

Albert McQuiston shares some advice:

Organizations using Azure Cloud services often overspend, eventually decreasing their operational efficiency. Leveraging cost-optimization techniques can help these businesses to focus on areas requiring more capital investment.

There are a few tips around specific actions you can take to understand why you’re spending so much and how to cut it down a bit. Albert also mentions but does not share a link to the Azure pricing calculator. This is a great tool if you already know what Azure resources you need and intend to price them out. It’s a real challenge getting the number close enough to right (especially for complex services with a lot of inputs, like Azure Synapse Analytics was), but can be useful in getting in the ballpark. But I also highly recommend going through a Well-Architected Review assessment, based on Azure’s Well-Architected Framework. This framework and its associated reviews cover cost-effectiveness as a key tenet.

Comments closed

Generative AI Answers: Do Not Trust, Do Verify

Published 2024-11-01 by Kevin Feasel

Erik Darling speaks wisdom:

Here’s what I’ve used it for with some success:

  • Creating images for Beer Gut Magazine
  • Summarizing long documents
  • Writing boilerplate stuff that I’m bad at (sales and marketing drivel, abstracts, lists of topics)

But every time I ask it to do that stuff, I really have to pay attention to what it gives me back. It’s often a reasonable starting place, but sometimes it really goes off the rails.

That’s true of technical stuff, too. Here’s where I’ve had a really bad time, and if there’s anything you know deeply and intimately, you’ll find similar problems too.

Click through for Erik’s experience. That’s pretty close to my own, and is a big part of why I refer to generative AI models as being akin to drunken interns: sure, give them assignments, but you’d better double-check every part of it.

Comments closed

Installing PostgreSQL Offline

Published 2024-11-01 by Kevin Feasel

Semab Tariq performs an installation:

Many companies, choose to store their databases in secure, closed environments—machines without internet access or outside the cloud. This is often done to maintain tight control over sensitive data and to meet strict security requirements. However installing PostgreSQL in a restricted, offline environment can be a real challenge, as it limits access to typical installation tools. 

Recently, I worked on a client project with a similar setup—a secure, offline environment without internet access—where we needed to install and configure PostgreSQL from scratch. If you’re facing the challenge of setting up PostgreSQL in a closed environment, this blog will guide you through the process step-by-step.

It turns out to be pretty straightforward, so long as you can start from a machine with internet access.

Comments closed

Monitoring R Models in Production with Vetiver

Published 2024-10-31 by Kevin Feasel

Myles Mitchell continues a series on Vetiver:

In those blogs, we introduced the {vetiver} package and its use as a tool for streamlined MLOps. Using the {palmerpenguins} dataset as an example, we outlined the steps of training a model using {tidymodels} then converting this into a {vetiver} model. We then demonstrated the steps of versioning our trained model and deploying it into production.

Getting your first model into production is great! But it’s really only the beginning, as you will now have to carefully monitor it over time to ensure that it continues to perform as expected on the latest data. Thankfully, {vetiver} comes with a suite of functions for this exact purpose!

Click through for the full story.

Comments closed