When we update an array in PostgreSQL, we can update one specific element or we can update a whole slice (a range of elements).
To update a slice, we use a syntax that references the lower and upper bounds of the slice, separated by a colon. We also provide the new values that will be assigned to each element within that slice.
Continue readingSome RDBMSs support an enum type, which comprise a static, ordered set of values. Also known as enumerated types, enum types can be handy for making a column accept just a small set of values, such as days in the week, predefined clothing sizes, or any number of other preset values.
In PostgreSQL, if we want to create a table that uses an enum type, we need to create the enum type first, then apply it against the table. This is a bit different to other RDBMSs such as MySQL, where we don’t need to create the enum type first.
If you’re getting an error that reads “The configuration option ‘fill factor’ does not exist, or it may be an advanced option” in SQL Server, it appears that you’re trying to view or set the default fill factor configuration option.
In SQL Server, fill factor is considered an advanced configuration option. By default, advanced options aren’t available for viewing and changing. However, we can use the following technique to make them available.
One of the various options we have when creating or rebuilding indexes in SQL Server is specifying a fillfactor. If we create or rebuild an index without specifying a fillfactor, then the default fillfactor is applied.
In some cases, using the default fillfactor may be fine, even ideal. In other cases it could be less than ideal, even terrible.
Let’s look at what fillfactor is, how it works, and how we can use it to enhance database performance.
Continue readingIn SQL Server, we can use UNIQUE constraints to ensure that a column (or columns) contain only unique values. When we have a UNIQUE constraint against a column, the system will prevent any duplicate values are entered into that column.
Sometimes we need to return a list of UNIQUE constraints, so that we simply know what we’re working with. Other times we might want to create scripts for all of our UNIQUE constraints so that we can recreate the constraints later.
Regardless of the reason, here are five methods for returning UNIQUE constraints in a SQL Server database.
If you’re getting SQL Server error 3723 that reads “An explicit DROP INDEX is not allowed on index…” it’s probably because you’re trying to drop a PRIMARY KEY or UNIQUE constraint by dropping its index.
In PostgreSQL, a composite value is stored as a comma separated list of values, surrounded by parentheses.
When we have a table that contains composite values, we can extract individual fields by using dot notation to refer to the specific field that we want to extract.
Continue readingIn SQL (and computer science in general), a Boolean expression is a logical statement that evaluates to either true or false. Some RDBMSs provide a boolean data type that can store values that are either true or false (or unknown). PostgreSQL is one such RDBMS.
In PostgreSQL, the Boolean type is called boolean and it uses 1 byte.
If you’re getting an error that reads something like “Index cannot be created on object ‘Customers’ because the object is not a user table or view” it could be that you’re trying to create an index, but there’s also a synonym of the same name (but in a different schema). It would appear that SQL Server thinks that you’re trying to create the index on the synonym instead of the table or view of the same name.
Continue readingWHILE loops are a powerful feature in most programming languages that allow developers to execute a block of code repeatedly as long as a specified condition is true. However, there are times when we may need to exit a loop prematurely based on certain criteria. SQL Server caters for this possibility with the BREAK keyword.
In this article, we’ll explore how to effectively use the BREAK keyword within WHILE loops in SQL Server.