In MySQL, CHARSET() is a built in function that returns the character set of its string argument.
We provide the string when we call the function.
Continue readingCategory: DBMS
Database Management Systems
Fix Error Msg 8115 “Arithmetic overflow error converting expression to data type…” in SQL Server
If you’re getting error msg 8115 that includes the message Arithmetic overflow error converting expression to data type…, it’s probably because you’re trying to convert a value to a data type that can’t handle that value. For example, trying to convert a number to a tinyint but the number’s too big to fit into a tinyint.
To fix this issue, make sure you convert the value to a data type that can handle the size of the value that you’re trying to convert.
Continue readingCreate a Sequence in PostgreSQL
PostgreSQL allows us to create sequence objects, otherwise known as “sequence generators”, or simply “sequences”. As the name suggests, a sequence object is used to generate sequence numbers.
We can use sequences to generate incrementing numbers or decrementing numbers.
Continue readingFix Error 9809 “The style … is not supported for conversions from … to …” in SQL Server
If you’re getting error msg 9809 which reads something like The style … is not supported for conversions from … to … in SQL Server, it’s probably because you’re trying to convert between data types, but the style that you’re specifying isn’t supported for that operation.
It’s not that the conversion can’t happen, it’s just that the style that you’re providing is wrong.
Continue readingConvert DATE to YYYYMMDD in SQL Server
In SQL Server, we can use functions like CONVERT() or FORMAT() to convert a valid date type into a format like yyyymmdd.
This format adheres to the ISO 8601 standard, which defines dates to be written as yyyymmdd, or when using delimiters, as yyyy-mm-dd.
In SQL Server, the date type expresses dates in the yyyy-mm-dd format, but we can use the following technique to remove the delimiters and express the date as yyyymmdd.
Continue readingFix “WRONGTYPE Operation against a key holding the wrong kind of value” when using ZPOPMIN, ZPOPMAX, BZPOPMIN, or BZPOPMAX in Redis
If you get an error that reads “WRONGTYPE Operation against a key holding the wrong kind of value” when using the ZPOPMIN or ZPOPMAX commands in Redis, it’s because you’re passing a key with the wrong data type.
The same issue can apply when using the blocking variants of these commands (BZPOPMIN and BZPOPMAX).
To fix this issue, make sure you pass a sorted set to these commands.
Continue readingConvert YYYYMMDD to DATE in SQL Server
When working with SQL Server, if we’re given a number that represents a date in the yyyymmdd format, we can use functions like CAST() or CONVERT() to convert that number to a valid date type. This will enable us to perform date operations against it that we might not be able to do when it’s still in numeric form.
How SERIAL Works in PostgreSQL
In PostgreSQL we can create auto-incrementing columns using the serial data type. The serial type causes the column to be automatically populated with an auto-incrementing value each time a new row is inserted. The same applies for the smallserial and bigserial types.
This article provides an overview of how these data types work.
Continue readingCreate an IDENTITY Column in PostgreSQL
Prior to PostgreSQL 10, if we wanted to create an auto-incrementing column, we would typically use the SERIAL type, or we’d create our own sequence and apply it to an integer column.
But from PostgreSQL 10 onwards, we’ve been able to create identity columns.
Continue readingFix “Cannot drop a default constraint by DROP DEFAULT statement. Use ALTER TABLE to drop a constraint default.” in SQL Server
If you’re getting an error that reads something like “Cannot drop a default constraint by DROP DEFAULT statement. Use ALTER TABLE to drop a constraint default“, it’s because you’re trying to use DROP DEFAULT to drop a DEFAULT constraint.
The DROP DEFAULT statement has been flagged for deletion from SQL Server, and Microsoft recommends that we use the ALTER TABLE statement to drop DEFAULT constraints.
Therefore, to fix this issue, use the ALTER TABLE statement to drop the DEFAULT constraint.