Author: Ian

Understanding the PS_THREAD_ID() Function in MySQL

Posted on by Ian

In MySQL, we can use the PS_THREAD_ID() function to get the Performance Schema thread ID assigned to a given connection ID. This can be useful whenever we use a function that takes the thread ID as its parameter, but we only know the connection ID.

If no thread ID exists for the connection, then NULL is returned.

The built-in PS_THREAD_ID() function does the same thing as the now deprecated sys.ps_thread_id() function. Therefore, we can call the function without needing to qualify it with sys or making sys our current schema.

Continue reading →

Understanding INFORMATION_SCHEMA in SQL

Posted on by Ian

Behind every well-structured and organised SQL database lies a treasure trove of information called metadata. Metadata is data that provides information about other data. In relational database management systems, this metadata can typically include information like the names of databases, tables, columns, the data type of a column, access privileges, and much more.

The SQL standard provides a collection of tables/views that allow us to view metadata about the objects in the database. The SQL standard refers to this as the information schema and calls it INFORMATION_SCHEMA.

Continue reading →

Fix “column … can only be updated to DEFAULT” in PostgreSQL

Posted on by Ian

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.

Continue reading →

Using the sys.format_path() Function to Dynamically Replace a Subpath with its Equivalent System Variable in MySQL

Posted on by Ian

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 reading →

Create a CHECK Constraint in MySQL

Posted on by Ian

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).

Continue reading →

6 Ways to Fix “nextval: reached minimum value of sequence” in PostgreSQL

Posted on by Ian

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 reading →