In PostgreSQL, the array_length() function returns the length of the specified array dimension.
We pass the array as the first argument, and the dimension as the second.
Continue readingAuthor: Ian
How to Set the datestyle Variable for the Current Session in PostgreSQL
PostgreSQL has a datestyle variable that specifies the display format for date and time values, as well as the rules for interpreting ambiguous date input values.Â
We can set the date/time style with the SET datestyle command, the DateStyle parameter in the postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client.
Below is an example of using the SET datestyle command to change the datestyle for the current session.
A Quick Overview of the CARDINALITY() Function in PostgreSQL
In PostgreSQL we can use the cardinality() function to return the total number of elements in an array. This includes elements across all dimensions of the array.
We pass the array as an argument to the function, and it returns an integer of the total number of elements in that array.
Continue readingFix Error “cannot subscript type text because it does not support subscripting” in PostgreSQL
If you’re getting a PostgreSQL error that reads something like “cannot subscript type text because it does not support subscripting” when selecting data from a database, it’s probably because you’re trying to perform some sort of array operation against a non array value.
The above error specifically mentions text data but we could get the same error when using a different data type, like an integer, character varying, etc.
To fix this issue, be sure to run the array operations against actual arrays. If working with non array data, then don’t use array operations against that data.
Continue readingHow to Create an Empty Array When Using the ARRAY Constructor
In PostgreSQL we can use the ARRAY constructor to create an array. When we do this, we provide the array elements as a comma separated list, enclosed in square brackets. Postgres then works out the data type based on the array elements.
But what if we want to create an empty array?
Creating an empty array can cause issues if we don’t explicitly specify the type. We need to specify the type. Below is an example of creating an empty array in PostgreSQL using the ARRAY constructor.
How ARRAY_POSITION() Works in PostgreSQL
PostgreSQL has an array_position() function that returns the position of the first occurrence of a given value in an array.
We have the option to specify a starting position, so that the function returns the position of the first occurrence after that.
To get all occurrences, use array_positions() instead.
How to Rename a Scheduled Event in MySQL
In MySQL we can use the ALTER EVENT statement to make changes to existing scheduled events, including changing the name of the event.
Fix “function generate_subscripts(text[], integer, integer) does not exist” in PostgreSQL
If you’re getting a PostgreSQL error that reads something like “function generate_subscripts(text[], integer, integer) does not exist“, it’s probably because your third argument is of the wrong type when using the generate_subscripts() function.
The above error specifically implies that an integer was passed as the third argument, but it must be a boolean value.
The third argument of the generate_subscripts() function is optional, but if passed, it must be a boolean expression.
To fix this error, either pass a boolean value as the third argument, or eliminate the third argument altogether.
Continue readingUsing the <@ Operator in PostgreSQL
In PostgreSQL, the <@ operator checks to see whether the second array contains the first array. That is, whether or not the array on the right of the operator contains all elements in the array to the left.
The function returns a Boolean result: It returns true if the second array contains the first array, and false if it doesn’t. If the result is unknown, it returns NULL.
How the @> Operator Works in PostgreSQL
In PostgreSQL, the @> operator checks to see whether the first array contains the second array. That is, whether or not the array on the left of the operator contains all elements in the array to the right.
The function returns a Boolean result: It returns true if the first array contains the second, and false if it doesn’t. If the result is unknown, it returns NULL.