In PostgreSQL, the strpos() function returns the first starting position of a given substring within a string.
We pass the string as the first argument and the substring as the second.
Continue readingCategory: DBMS
Database Management Systems
A Brief Overview of SUBSTR() in PostgreSQL
In PostgreSQL, we can use the the substr() function to return a substring from a string, based on a starting point. We have the option of specifying how many characters to return.
We pass the string as the first argument and the start position as the second. If we want to specify how long the substring should be, we can pass a third argument that specifies how many characters to return.
The function returns the same result as the substring() function (which uses a slightly different syntax).
Fix “function array_sample(numeric, integer) does not exist” in PostgreSQL
If you’re getting an error that reads “function array_sample(numeric, integer) does not exist” when using the array_sample() function in PostgreSQL, it’s probably because your first argument is a numeric type instead of an array.
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 a numeric value.
How to Create a MySQL Event Only if it Doesn’t Already Exist
In MySQL we can use the CREATE EVENT statement to create scheduled events. As with many CREATE ... statements, we have the option of using the IF NOT EXISTS clause to specify that the object should only be created if it doesn’t already exist.
Of course, we wouldn’t normally be trying to create an event if we know that it already exists. But there may be times where we’re not sure, and we want our code to handle this scenario without throwing an error if an event with the same name already exists. This is common when creating scripts that are designed to be run across multiple environments. That’s where the IF NOT EXISTS clause can come in handy.
About PostgreSQL’s PARSE_IDENT() Function
In PostgreSQL, the parse_ident() function splits a qualified identifier into an array of identifiers, and removes any quoting of individual identifiers.
We pass the qualified identifier to the function when we call it. We can also specify whether or not to use strict mode.
Continue readingA Quick Look at PostgreSQL’s REGEXP_LIKE() Function
In PostgreSQL, we can use the regexp_like() function to check whether or not a match of a POSIX regular expression occurs within a given string.
We pass the string as the first argument and the pattern as the second argument. We can also provide a flag as an optional third argument, which determines how the function behaves.
Continue readingAn Overview of QUOTE_IDENT() in PostgreSQL
In PostgreSQL, we can use the quote_ident() function to quote identifiers in a SQLÂ statement string.
Quotes are only added if necessary. Any embedded quotes are doubled.
Continue readingAn Overview of the FORMAT() Function in PostgreSQL
In PostgreSQL, the format() function produces output formatted according to a format string. The first argument is the format string (consisting of one or more format specifiers that start with the % character), and the subsequent arguments are the values to format.
Fix “function array_shuffle(integer) does not exist” in PostgreSQL
If you’re getting an error that reads “function array_shuffle(integer) does not exist” when using the array_shuffle() function in PostgreSQL, it’s probably because the argument you’re passing to the function is not an array.
More specifically, this error message implies that you’re passing an integer. The same error can occur when passing other non-array types (such as numeric), but the solution is the same.
The argument for this function must be an array.
To fix this error, replace the integer value with an array value when calling the array_shuffle() function.
Understanding PostgreSQL’s REGEXP_INSTR() Function
In PostgreSQL, the regexp_instr() function returns the starting or ending position of the N‘th match of a POSIX regular expression pattern to a string. If there’s no match, it returns zero.
We pass the string and pattern as arguments. The function also accepts some optional arguments that allow us to be specific with how the function works.
Continue reading