Tag: how to

Fix “could not determine polymorphic type because input has type unknown” Error When using array_sample() in PostgreSQL

Posted on by Ian

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.

Continue reading →

Fix “function array_sample(integer, integer) does not exist” in PostgreSQL

Posted on by Ian

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.

Continue reading →

Create a Generated Invisible Primary Key (GIPK) in MySQL

Posted on by Ian

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

Remove the Comment from an Event in MySQL

Posted on by Ian

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

Move a Scheduled Event to Another Database in MySQL

Posted on by Ian

In MySQL scheduled events are tasks that run according to a given schedule. After creating a scheduled event, we can modify it using the ALTER EVENT statement. This statement allows us to change the event’s definition, change its schedule, enable/disable it, rename it, and more. It also allows us to move the event to another database.

To move an event to another database, we use the ALTER EVENT statement with the RENAME clause, prefixing the event name with the database name (using dot notation).

Continue reading →

3 Ways to Check an Event’s Status in MySQL

Posted on by Ian

When we create a scheduled event in MySQL it’s enabled by default. However, we do have the option of creating it in disabled status. We can also go back later and change an event’s status from enabled to disabled.

Given this fact, we may sometimes find ourselves wondering whether an event is currently enabled or disabled.

Below are three ways to go about checking an event for its enabled/disabled status.

Continue reading →

How to Prevent a MySQL Event from Disappearing Once it’s Completed

Posted on by Ian

By default, scheduled events in MySQL are dropped from the system once they expire. That means that if the event’s schedule has completed, then the event is dropped.

But we can override this behaviour with the ON COMPLETION clause. Specifically we can specify ON COMPLETION PRESERVE to keep the event in the system after it has expired. By default, events have ON COMPLETION NOT PRESERVE added to their definition, which means they are dropped as soon as they expire. Using ON COMPLETION PRESERVE changes this so that they aren’t dropped when they expire.

Continue reading →