Press "Enter" to skip to content

Category: Syntax

Using DATETRUNC() in SQL Server

Rajendra Gupta shows off a nice feature in SQL Server 2022:

Suppose you are a data strategist or analyst for an organization. You have been tasked with getting actionable insights from customers who want to track customer patterns at different intervals, such as hourly, daily, or weekly. To do this, you need to use several date functions such as DATEADDDATEDIFFDATEPART, and DATEFROMPARTS to get the required date format.

In SQL Server 2022, this got a lot easier to do using the DATETRUNC function.

Solutions using DATETRUNC() are significantly easier to read and understand than the alternative of combiningDATEADD() and DATEDIFF()

Comments closed

SELECT FOR UPDATE in PostgreSQL

Umair Shahid preps for an update:

When multiple transactions need to modify the same rows, ensuring data consistency can become tricky. A single wrong approach to locking can lead to suboptimal performance or even bring your application to a standstill as numerous transactions block one another. One tool in PostgreSQL’s arsenal to handle concurrency is SELECT FOR UPDATE. It allows you to lock specific rows before updating them, preventing other transactions from modifying those rows until your transaction completes.

In this blog, we will dive deep into SELECT FOR UPDATE in PostgreSQL. We will explore how it helps in reducing contention, avoiding deadlocks, and ultimately boosting performance when dealing with highly concurrent applications.

Click through to understand how this works and also some notes on when to use it and when not to use it.

Comments closed

Handling NULL in T-SQL

Chad Callihan deals with non-existent data:

Handling NULL values can be a challenge and may lead to unexpected query results when mixed in with non-NULL values. Thankfully, there are a few SQL Server functions devoted to handling NULL values. Let’s look at two of them. The first, NULLIF, will help you to return a NULL value. The second, ISNULL, will help you with an alternative to a NULL value if you need another value in its place.

For the longest time, I strongly preferred COALESCE() over ISNULL() because of how it can handle multiple scenarios, as well as it being ANSI SQL syntax. But it turns out that, if you do only have two things to compare, ISNULL() is a little bit faster.

Comments closed

Setting TEXTSIZE in SQL server

Steve Jones limits results:

There is a SET command in SQL Server that changes how much data is returned from some fields. This short post shows what I learned about the SET TEXTSIZE command.

I’m pretty sure that, if I’ve ever used this command before, it was by accident. It’s kind of a neat concept in how it can limit the result size of larger blocks of text, but I think it’s too limiting to be a good solution save for some very specific circumstances.

Comments closed

Dealing with Time in PostgreSQL

Radim Marek clocks in for the day:

The next possible source of confusion when working with time in PostgreSQL is presence of two distinct data types:

  • timestamp (or timestamp without time zone)
  • timestamptz (or timestamp with time zone) Despite what the names suggest, the key difference isn’t whether they store timezone information, but rather how they handle it during storage and retrieval.

Click through for quite a bit of detail and plenty of examples on how to handle date and time data in PostgreSQL. And I’m still jealous about support for intervals, especially in window functions.

Comments closed

Dealing with Arrays in SQL and jOOQ

Lukas Eder covers mapping functions:

ARRAY types are a part of the ISO/IEC 9075 SQL standard. The standard specifies how to:

  • Construct arrays
  • Nest data into arrays (e.g. by means of aggregation or subqueries)
  • Unnest data from arrays into tables

But it is very unopinionated when it comes to function support. The ISO/IEC 9075-2:2023(E) 6.47 <array value expression> specifies concatenation of arrays, whereas the 6.48 <array value function> section lists a not extremely useful TRIM_ARRAY function, exclusively (using which you can remove the last N elements of an array, something I have yet to encounter a use-case for)

There are a few database platforms that support the ARRAY type, as Lukas lays out.

Comments closed

Domains in ANSI SQL

Joe Celko describes a domain:

For example, if there though is that there is a domain called voltage which has a base unit called “volt” that’s otherwise meaningless. Yes, you can get a voltmeter you can watch the needle, you can be told what the IEEE specification for defining how much work a volt should do or shock you. I’ve discussed scales and types of measurements in a previous article, It’s worth mentioning that you should not confuse domain with the representation and symbols of the units being used. Some domains are limited, such as degrees that measure planar angles. An angle can be from 0 to 360°, or it can be between zero and 2π radians.

Joe has an explanation but doesn’t have any concrete examples in psql. Here’s one from the PostgreSQL documentation:

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

The idea of a domain here is that you define a valid slice of some data type. We can do something similar with check constraints on an attribute, but the difference is that we’d need to create the check constraint for each relevant attribute, whereas the domain would include this check automatically, making it quite useful if we have multiple instances of, say, us_postal_code in our database. Then, we wouldn’t need to worry about creating a check constraint on each instance and ensuring that the code remains the same across the board.

This also leads to a very common sentiment in functional programming: make invalid states unrepresentable. In other words, make it impossible for a person or piece of code to generate a result in an invalid state. By defining a domain with the scope of our valid state, we make it impossible for someone to create a US postal code value that does not pass our check, and so we can’t have dirty data of this sort in our database.

Comments closed

COPY and \COPY in PostgreSQL

Dave Stokes runs two commands:

PostgreSQL is equivalent to a Swiss Army Knife in the database world. There are things in PostgreSQL that are very simple to use, while in another database, they take many more steps to accomplish. But sometimes, the knife has too many blades, which can cause confusion. This is one of those cases.

Read on to understand what the difference is between these two commands.

Comments closed

Linting SQL with SQLFluff

Josephine Bush busts out a linter:

I thought I didn’t care about linting, and lately, I haven’t written a lot of SQL, but for the SQL I do write, I have SQLFluff to help me format it. A friend of mine is big into SQLFluff and finally talked me into installing and using it. For more information about SQLFluff itself, visit here.

Josephine shows off some of the configuration for PostgreSQL’s psql as well as SQL Server’s T-SQL.

Comments closed

Regular Expression Matches in PostgreSQL

Tobias McNulty now has two problems:

regexp_matches() and regexp_match() are two similar string functions that support regular expression matching directly in the PostgreSQL database. regexp_matches() was added in PostgreSQL 8.3, and regexp_match() was added in PostgreSQL 10 (keep reading to see how ChatGPT struggled to answer this question).

Read on for that as well as plenty more information on how the two work, and even a bonus snippet on another regular expression function.

Comments closed