If you’re getting an error in PostgreSQL that tells you that a column “…can only be updated to DEFAULT” with detail that explains that it “…is an identity column defined as GENERATED ALWAYS“, it’s probably because you’re trying to update an identity column with your own value, but the identity column was created with the GENERATED ALWAYS option.
Using the sys.format_path() Function to Dynamically Replace a Subpath with its Equivalent System Variable in MySQL
In MySQL, the sys.format_path() function is a stored function in the sys schema. It accepts a path name, and returns the modified path name after replacing subpaths that match the values of certain system variables.
So we can use the function to dynamically replace part or all of our path with a system variable that matches that path segment.
Continue readingUnderstanding the sys.extract_table_from_file_name() Function in MySQL
In MySQL, the sys.extract_table_from_file_name() function is a stored function in the sys schema.
It accepts a path name, and returns the path component that represents the table name.
Continue readingHow to Use the sys.ps_is_account_enabled() Function in MySQL
In MySQL, we can use the sys.ps_is_account_enabled() function to check whether Performance Schema instrumentation for a given account is enabled.
The function returns YES or NO, depending on whether or not the instrumentation for the given account is enabled.
Using the sys.ps_thread_account() Function to Get the Account for a Given Thread IDÂ in MySQL
In MySQL, the sys.ps_thread_account() function returns the account associated with a given thread ID. It returns the account in the form user_name@host_name.
Understanding the sys.ps_is_instrument_default_enabled()Â Function in MySQL
In MySQL, we can use the sys.ps_is_instrument_default_enabled() function to check whether a given Performance Schema instrument is enabled by default.
The function returns YES or NO, depending on whether the instrument is enabled by default.
Create a CHECK Constraint in MySQL
As of MySQL 8.0.16, we can create CHECK constraints in MySQL.
A CHECK constraint is a type of integrity constraint. It specifies a search condition to check the value being entered into a row. If the value being entered violates the CHECK constraint, then the result of the search condition is FALSE and an error occurs (unless the IGNORE clause is used in the SQL statement, in which case a warning is reported and the offending row is skipped).
The ability to create CHECK constraints was introduced in MySQL 8.0.16. Prior to version 8.0.16, MySQL actually allowed a limited version of CHECK constraint syntax, but it was completely ignored (no CHECK constraint was created or evaluated).
6 Ways to Fix “nextval: reached minimum value of sequence” in PostgreSQL
If you’re getting an error that reads something like “nextval: reached minimum value of sequence “sequence1” (-3)” in PostgreSQL, it’s probably because you’re trying to generate a new value from a descending sequence, but the sequence has already reached its minimum value.
We have a number options when it comes to dealing with this issue.
Continue readingUnderstanding the sys.ps_is_instrument_default_timed()Â Function in MySQL
In MySQL, we can use the sys.ps_is_instrument_default_timed() function to check whether a given Performance Schema instrument is timed by default.
The function returns YES or NO, depending on whether the instrument is timed by default.
Understanding the sys.ps_is_thread_instrumented()Â Function in MySQL
In MySQL, we can use the sys.ps_is_thread_instrumented() function to check whether Performance Schema instrumentation for a given connection ID is enabled.
The function returns YES or NO, depending on whether the instrumentation is enabled for the given connection. It can also return UNKNOWN if the connection ID is unknown, and NULL if the connection ID is NULL.