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.
Tag: how to
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 readingHow to Select Rows Where a Column is NULL in SQL
When learning SQL, a common mistake when checking for NULL values is to use an equality operator. In SQL, a NULL value is treated a bit differently to other values.
In SQL we use IS NULL instead of = NULL. Likewise, we use IS NOT NULL instead of <> NULL or != NULL to select those columns that don’t have a NULL value.
3 Ways to Get the Data Directory in MySQL
In MySQL, the data directory stores information managed by the MySQL server. Each subdirectory of the data directory is a database directory and corresponds to a database managed by the server.
If you ever need to find out where the data directory is located on your MySQL implementation, below are some options to try.
Continue reading6 Ways to Fix Error 1055 “Expression … of SELECT list is not in GROUP BY clause and contains nonaggregated column…” in MySQL
If you’ve been using MySQL for any decent amount of time, it’s likely you’ll be familiar with error 1055 that reads something like “Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column…“, where #N is the expression number of an expression/column in your SELECT list.
This error can occur when we include a column in the SELECT list, but we omit it from the GROUP BY clause.
There are several ways we can go about resolving this issue. Below are six options for dealing with this issue.
Continue reading4 Ways to Fix Error 1048 “Column ‘…’ cannot be null” in MySQL
If you’re getting error 1048 that reads something like “Column ‘ColumnName’ cannot be null” (where ColumnName is the name of a column you’re trying to insert data into), then it’s probably because you’re trying to insert a NULL value into that column, but the column has a NOT NULL constraint (which prevents NULL values from being inserted).
We have a few options when it comes to fixing this issue. The most obvious is to ensure we provide a non-NULL value for the column. Alternatively, if the column should be able to accept NULL values, then we can remove the NOT NULL constraint from the column. Another option is to use the IGNORE keyword to ignore the error. And another way to deal with the error is to disable strict mode.
How to Disable Strict Mode in MySQL
Strict mode controls how MySQL handles invalid or missing values in statements that change data, such as in INSERT and UPDATE statements.
Strict mode (aka strict SQL mode) is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled.
In MySQL 8.0, the default SQL mode includes STRICT_TRANS_TABLES, which enables strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines.
We can disable strict mode in our session by removing it from our @@sql_mode variable. We can also disable strict mode at server startup, so that we don’t have to do it at runtime.
Fix “ERROR:Â step size cannot equal zero” When Creating a PostgreSQL Series
If you’re getting an error that reads “step size cannot equal zero” when creating a series with the generate_series() function in PostgreSQL, it’s because you’re using a step size of 0 (i.e. zero).
The generate_series() function doesn’t accept a zero step size.
To fix this issue, either use a non-zero step size, or remove the step size altogether (so that the default step is used).
Continue readingHow to Remove a SQL Mode from sql_mode in MySQL
MySQL can operate in different SQL modes. These can be set at server startup or at runtime.
DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements. Users can also set their own SQL mode at the session level via the @@sql_mode system variable.
By default, the sql_mode system variable contains a list of SQL modes that are applied.
As a user, we can modify our sql_mode system variable as required at runtime. This article demonstrates how we can remove a SQL mode from our sql_mode system variable.