In PostgreSQL we can create arrays with the ARRAY constructor. An array constructor is an expression that builds an array value using values for its member elements.
Author: Ian
How to Check the Current datestyle Setting in PostgreSQL
PostgreSQL has a datestyle setting that specifies the display format for date and time values, as well as the rules for interpreting ambiguous date input values.
We can check the current value of our datestyle setting by running SHOW datestyle.
An Overview of the GENERATE_SUBSCRIPTS() Function in PostgreSQL
PostgreSQL has a generate_subscripts() function that generates a series comprising the valid subscripts of a given array.
We pass the array to the function, along with the dimension that we want to use. We also have the option of returning the series in reverse order.
Continue readingFix “could not determine polymorphic type because input has type unknown” Error When using array_sample() in PostgreSQL
If you’re getting an error that reads “could not determine polymorphic type because input has type unknown” when using the array_sample() function in PostgreSQL, it’s probably because your first argument is of the wrong type.
The array_sample() function requires an array as its first argument. Passing a non-array value will result in an error. The actual error can vary, depending on the argument you pass, but either way, the error is usually due to a non-array being passed.
To fix this error, be sure to pass an array as the first argument when calling the array_sample() function.
A Quick Overview of the ARRAY_SHUFFLE() Function in PostgreSQL
In PostgreSQL array_shuffle() is a system function that randomly shuffles the first dimension of a given array.
The array_shuffle() function was introduced in PostgreSQL 16, which was released on September 14th 2023.
Fix “function array_sample(integer, integer) does not exist” in PostgreSQL
If you’re getting an error that reads “function array_sample(integer, integer) does not exist” when using the array_sample() function in PostgreSQL, it’s probably because your first argument is not an array. In particular, this specific error message implies that the first argument is an integer.
The first argument for this function must be an array.
To fix this error, be sure that your first argument to array_sample() is an array, not an integer.
Create a Generated Invisible Primary Key (GIPK) in MySQL
MySQL 8.0.30 introduced generated invisible primary keys (GIPKs), which are primary keys that are automatically created whenever we create a table without explicitly defining a primary key.
GIPKs only work with the InnoDB storage engine, and they only work when we have GIPKs enabled.
In this article, I check whether or not GIPKs are enabled on my system, I then enable GIPKs, and finally I create a table with a GIPK.
Continue readingAn Introduction to the ARRAY_SAMPLE() Function in PostgreSQL
In PostgreSQL array_sample() is a system function that returns an array of a specified number of items randomly selected from the given array.
The array_sample() function was introduced in PostgreSQL 16, which was released on September 14th 2023.
Remove the Comment from an Event in MySQL
When we create a scheduled event in MySQL we have the option of using the COMMENT clause to add a comment to the event. Comments can be a handy addition that help explain what the event does, why it was created, etc.
So it’s probably quite rare that we would want to remove the comment from an event. But in the event that we do (pun intended!), we can simply update the event with a blank comment.
Continue readingAn Overview of the DATE_SUBTRACT() Function in PostgreSQL
PostgreSQL 16 introduced the date_subtract() function that allows us to subtract an interval from a timestamp with time zone.
It computes times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted.