Fun With SQL: Text and System Functions
See how to easily format output from Postgres, especially strings.
Join the DZone community and get the full member experience.Join For Free
SQL by itself is great and powerful, and Postgres supports a broad array of more modern SQL including things like window functions and common table expressions. But rarely do I write a query where I don't want to tweak or format the data I'm getting back out of the database. Thankfully, Postgres has a rich array of functions to help with converting or formatting data. These built-in functions save me from having to do the logic elsewhere or write my own functions. In other words, I do less work because Postgres has already done it for me, which I'm always happy about.
We've covered a set of functions earlier, and today we're going to look at some different categories of functions to dive deeper.
When building a nice clean report, I'm often doing some cleaning up of the data. This could be as simple as combining my
last_name column. In that case, a simple concatenation is quite useful:
SELECT first_name || last_name as name FROM users;
But as you can expect, I can do much more. If I'm performing a basic search for a keyword or delimiter, I can search for the position of that string. Then I can feed that into a case statement:
SELECT CASE WHEN position(',' in my_string) > 0 then True ELSE False END FROM my_table;
Of course, if you're working with a field that is delimited, you may want to take the above to first find the correct field. From there, you'd likely want to split that delimited field up. For that, we can use
split_part and grab the appropriate section of our delimited field:
SELECT CASE WHEN position(',' in my_string) > 0 then split_part(my_string, ',', 1) ELSE null END FROM my_table;
Finally, if you're dealing with messy input/machine data, there are a number of functions that can handle cleaning up that data:
substr— allows you to extract a substring
rtrim— removes the longest part of a matching string from the right side
reverse— for the next time you're asked to reverse a string in an interview :)
regexp_replace— for when you need regular expressions
Sizing Made Simple
A less-common category of functions is working with sizes of things like disk/memory. If you work on a tool that records data about how much data you've stored, or even work with the system information about your Postgres database, you often deal with this. Let's say once a month you're querying your database for bloat to track it.
When you query for bloat, you get back something that looks like 37502634 (this is in bytes). At first glance, my thought is we're doomed and the database will die tomorrow because that looks like a lot. But what I really want to do is get a better understanding of it. Fortunately, I don't have to pull up Wolfram Alpha to get a better idea of how large that actually is. I can use
SELECT pg_size_pretty(37502634::bigint); pg_size_pretty ---------------- 36 MB (1 row)
Whew. 36MB isn't a level that concerns me on my 40 GB table, so I'm safe.
Postgres also makes it easy for you to go from the pretty form to the raw number:
SELECT pg_size_bytes ('40 GB'::text); pg_size_bytes --------------- 42949672960 (1 row)
The admin functions are a unique category in and of themselves that can be very handy when operating/managing your database. We make heavy use of them while running and managing our database as a service.
A Function a Day
Postgres functions make the little things that eat up time when creating the perfect query much easier. The next time you're trying to create the perfect output or working with a query, and the SQL itself can't quite get you there, explore Postgres built-in functions. Chances are Postgres has already done the heavy lifting to make working with your data easier.
Published at DZone with permission of Craig Kerstiens, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.