4 improvements in Postgres 16 that you might not know about yet
In E84 of “5mins of Postgres” we’ll talk about four new features in Postgres 16 that you might not know about yet. You’ll learn about pg_input_is_valid, pg_input_error_info, improvements to \watch, number separators in the SQL:2023 standard, and more!
Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel.
Transcript
Let's dive in!
pg_input_is_valid and pg_input_error_info
Let’s start with two blog posts on Luca Ferrari's personal blog. In the first post here Luca talks about two new functions that help you validate input to Postgres.
Let's say you have a timestamp value and you want to be using that to, for example, insert into a timestamp column or cast to a timestamp. What you want to make sure is that this is actually a legitimate timestamp that Postgres will not error on. In Postgres 16, you can use this new pg_input_is_valid function against the value that you're passing in, together with the data type that you want to validate.
This will tell you whether it's a valid argument for that specific data type or not. We can also use the pg_input_error_info function to get any details on the error message.
Let's take an actual case where that is an error. In Luca’s article you can see an example where we have the year 1978, but we have the month 19. In Postgres 16, you can use pg_input_is_valid, and it will actually give you false, as this is not a correct timestamp. If you use the input error function, it will tell you this is because that datetime field value is out of range.
It also suggests that you might need a different datestyle setting. Sometimes datestyles could be different, so for example, this could be a valid year, day, month timestamp, just not a valid year, month, date timestamp. This is very useful when you're using PL/pgSQL functions, because previously you'd have to go through a lot of trouble to catch these errors that Postgres would throw on your input.
But now you can actually check whether the input is correct, and if the input is not correct, you could, for example, decide to output a custom error message that talks about those details or parse the input in different ways altogether.
Define how often \watch issues statements
Luca also had a good blog post about the new \watch improvement in PostgreSQL 16. This is on the client side, so in order to make use of this, you will have to make sure that your psql locally or wherever you're running it is actually upgraded to PostgreSQL 16. You could also use this in older Postgres servers, by the way, it's just the client that needs to be updated for this! \watch is essentially a very convenient way to run the same query again and again on a particular interval, and it's been in psql for quite a number of releases.
In this case, we're doing a SELECT * FROM pg_stat_progress_cluster and we do \watch 5: so every five seconds we run the query and we show the result of that query on the console. Now, the problem is that I have to actually stop that command once I no longer need it. This means I have to remember to do Ctrl C. Otherwise, as long as my terminal is active, this will just keep running and running.
That doesn't seem ideal. There are some hacks that let you do this in earlier releases, but the nice thing is that now, in Postgres 16, what we can do is specify how often at most we want to issue the command. So here, for example, we can say \watch i=2 c=7, i standing for interval and c standing for count.
That means the statement that we're running here would be run seven times every two seconds, and then will automatically stop. It's just a really nice convenience, even if you set this to 10 or 100, it will make sure that at some point it stops issuing these statements automatically.
Separator for numbers in SQL for better readability
Now, switching gears a little bit, coming back to data input. Peter Eisentraut added a feature to Postgres 16, which is the ability to specify numbers with additional separators for thousands in the numeric literal. Imagine you're specifying 1 million as a number in your SQL query, and you want to make sure it's easier to read. In many programming languages, what you can do is to use underscores to make it easier to understand where the thousands separators are.
So in 1 million, you'd have 1_000_000. The same idea applied to Postgres is what Peter made an effort to add. The hard part here was not necessarily making the change in Postgres, but making sure that nothing breaks from a backwards compatibility perspective, and also to make sure that this is standard SQL, not just a particular Postgres feature.
Peter goes into more details in his blog post, but the gist of it is that he actually went through all the effort and got this into the new SQL standard SQL:2023. Now, we can rely on this new feature in Postgres 16 with the separators for numbers, not just as a Postgres feature, but as a SQL standard feature. Really useful if you want to make your queries more readable.
Get a particular distribution of randomness with random_normal
Now, last in Postgres 16, I want to point out something that we actually talked about previously in 5mins of Postgres episode 50.
And this is part of a blog post that Paul Ramsey wrote on using the Postgres random functions. This is a good post and I encourage you to read it, but I want to recall one thing that Paul pointed out here. Which is the new random_normal function. If you're using random functions in Postgres to generate data, sometimes you want your data to have a certain structure, a certain kind of distribution of the randomness.
With Postgres 16, what you can do is use the random_normal function* to get a particular distribution of randomness. It follows a normal distribution so that you can have your data shaped in a certain way. For example, if you combine this with generate_series, it can be very useful for benchmarking data as well.
Setting per-user variables
Now actually last... I lied, there's actually one more thing I want to talk to you about. This is a Postgres 15 addition that Bruce Momjian recently blogged about. Bruce talks about setting per user variables. What he means is configuration settings that affect a particular user or a particular connection.
In older Postgres releases before Postgres 15, not 16 but 15, you had to go through a lot of trouble to have ways to have non superusers set log_statement, for example, on a per connection basis. In Postgres 15, you can use the new GRANT SET ON PARAMETER syntax to grant permission to change the setting on a per connection basis to individual non superusers. And I mostly mention this because I forgot that this was a change in Postgres 15 and I could see myself using this to make my systems work better for non superusers. So, great addition as well that of course is also in Postgres 16 as well as in Postgres 15.
This was episode 84 of 5mins of Postgres. Subscribe to our YouTube channel, sign up for our newsletter and follow us on LinkedIn and Twitter to get updates about new episodes!