A Fine Slice Of SQL Server
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.
Leave a CommentRadim 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(ortimestamp without time zone)timestamptz(ortimestamp 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.
Leave a CommentLukas Eder covers mapping functions:
ARRAYtypes 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 usefulTRIM_ARRAYfunction, exclusively (using which you can remove the lastNelements 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.
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 closedDave 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 closedJosephine 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 closedTobias McNulty now has two problems:
regexp_matches()andregexp_match()are two similar string functions that support regular expression matching directly in the PostgreSQL database.regexp_matches()was added in PostgreSQL 8.3, andregexp_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 closedIt’s not unreasonable to expect these two statements to produce the same execution plan on most RDBMS, given that they’re logically equivalent. Since we’ve grown used to reading things from left to right and top to bottom, I don’t think
RIGHT JOINwill become more popular any time soon.There is, however, one place in the SQL language where
RIGHT JOINis surprisingly ubiquitous!
Read on to see how the MERGE operator includes as part of its operations the equivalent of a RIGHT OUTER JOIN. MERGE in SQL Server had justifiably built a bad reputation in its early years, but it’s surprisingly okay nowadays, with most of the bugs being fixed over time.
Comments closedSteve Jones performs a pair of tests:
I had someone reach out about generate_series() recently, saying they hadn’t realized this was a new feature in SQL Server 2022. They were wondering if it was better than using a tally table.
I didn’t want to do an exhaustive test, but I thought I’d take a minute and try a couple simple things just to see.
Steve used the CTE-based tally table builder, building based on cross joining spt_values. This is one of the classic approaches. The performance differences aren’t enough on their own to justify large-scale changes if you’re using a classical tally table, though it is good to see that GENERATE_SERIES() does perform well. And if you’re not familiar with the power of a tally table, here is one great explanation of the concept.
Leo Hsu and Regina Obe look at updates to the random() function in PostgreSQL:
Have you ever wanted to get a random integer between 1 and 10 and been a little annoyed the slightly cryptic code you had to write in PostgreSQL? PostgreSQL 17 random functions make that simpler. Sometimes it’s the small changes that bring the most joy.
Click through to see what it took to get a random integer or floating point number prior to PostgreSQL 17 and how it’s a fair bit simpler today.
Comments closed