In PostgreSQL, we can use the setval() function to set a sequence’s value.
We specify the value when we call the function. We also have the option of setting its is_called flag.
Author: Ian
SQL Server FOR XML Explained
In SQL Server, the FOR XML clause allows us to return the results of a query as an XML document.
When we use this clause, we have various options for how we want to shape the resulting XML document.
Below is an explanation of how the FOR XML clause works, including the basic options we have when using it.
Three ISDATE() Alternatives that Work with DATETIME2 Values in SQL Server
SQL Server’s ISDATE() function checks whether or not an expression is a valid date. However, you may be aware that this function doesn’t work on datetime2 values. On datetime2 values it returns 0, which means it’s not a valid date, even when the value is a valid date.
This is obviously not ideal, because the datetime2 type is a valid date type. Microsoft even recommends that we use datetime2 instead of datetime for our dates, as it aligns with the SQL Standard and it provides more fractional seconds precision.
Anyway, below are three options we can use to check whether a datetime2 value is a valid date.
Continue readingHow to Create Decrementing Sequence Numbers in SQL Server
When we create a sequence in SQL Server, we have the option of making it an incrementing sequence or decrementing.
By “decrementing”, I mean that the sequence decreases instead of increases. For example, if it starts at 100, the next value is 99, and then 98, and so on.
To create a sequence that decrements, all we do is provide a negative value for the INCREMENT BY argument.
Fix Error Msg 235 “Cannot convert a char value to money. The char value has incorrect syntax” in SQL Server
If you’re getting error msg 235 which reads Cannot convert a char value to money. The char value has incorrect syntax, it’s probably because you’re trying to convert a string to the money data type, but the string isn’t in a format that can be converted to the money type.
To fix this issue, make sure you’re trying to convert the right value (perhaps you’ve got the wrong column or variable). If you’re sure you’re trying to convert the right value, try a workaround like the one below.
Continue readingHow CURRVAL() Works in PostgreSQL
In PostgreSQL, the currval() function returns the value most recently returned by nextval() for the specified sequence in the current session.
The currval() function is very similar to the lastval() function, except that lastval() doesn’t require the name of a sequence like currval() does. That’s because lastval() doesn’t report on any particular sequence – it reports on the last time nextval() was used in the current session, regardless of which sequence was used. The currval() on the other hand, only reports on the specified sequence.
How LASTVAL() Works in PostgreSQL
In PostgreSQL, the lastval() function returns the value most recently returned by nextval() in the current session.
The lastval() function is very similar to the currval() function, except that lastval() doesn’t require the name of a sequence like currval() does. That’s because lastval() doesn’t report on any particular sequence – it reports on the last time nextval() was used in the current session, regardless of which sequence was used.
How NEXTVAL() Works in PostgreSQL
In PostgreSQL, the nextval() function is used to advance sequence objects to their next value and return that value. We pass the name of the sequence when we call the function. This assumes that the sequence object exists.
Fix “WRONGTYPE Operation against a key holding the wrong kind of value” when using ZINTERSTORE in Redis
If you get an error that reads “WRONGTYPE Operation against a key holding the wrong kind of value” when using the ZINTERSTORE command in Redis, it’s probably because you’re passing a key with the wrong data type.
To fix this issue, be sure that each key you pass to the ZINTERSTORE command is either a set or a sorted set.
How to Change the Range of a Sequence in SQL Server
SQL Server sequence objects allow us to increment or decrement through a range of numbers. Once set, we don’t need to change anything. We can simply use NEXT VALUE FOR to generated the next sequential number.
But what if we want to change the sequence to use a different range?
For example, we created a sequence that increments between 0 and 100, but now we want to change it to increment between 200 and 300.
Easy. We can use the ALTER SEQUENCE statement to do just that.