In PostgreSQL, domains are basically data types with optional constraints. We can create them as a kind of user-defined data type, and then reuse them in our columns going forward.
As with any user-created object, we sometimes need to see a list of existing domains in a PostgreSQL database. Listing all domains can be useful for database management, documentation, or troubleshooting.
This article will show you how to retrieve a list of all domains in your PostgreSQL database.
Continue readingIf you’re using the MERGE statement in SQL Server to merge data between tables, you may or may not be aware that the statement gives us the ability to check which rows were affected by the MERGE operation, as well as how they were affected.
To get this info, we can use the OUTPUT clause along with the $action argument.
Primary keys are fundamental to relational database design, ensuring each row in a table can be uniquely identified. They help to maintain data integrity in our databases.
There are many ways to find the primary key column in SQL Server, but sometimes we might need to identify the name of the primary key constraint itself.
Below are three examples of how we can do this.
Continue readingIf you’re getting an error when specifying LAST or PREV for MySQL’s HANDLER statement, it could be that you’re trying to do a table scan using the index syntax.
While the HANDLER statement does accept the LAST and PREV options, we can only use them with an index.
So to fix this issue, be sure to specify an index when using the LAST and PREV options.
It’s widely understood among SQL Server developers that foreign keys can prevent us from deleting a row from the parent table if there’s child table that references the key in that row.
But did you know that we can specify a different course of action in such cases?
For example, we could configure our foreign key to delete the child as well. Or we could set it to NULL, or to its default value.
These options are all available, thanks to the ON DELETE clause.
In PostgreSQL, a partial index is an index that’s built on a subset of the rows in a table. It includes only the rows that satisfy a given condition specified with the WHERE clause.
Partial indexes can help us achieve faster queries, reduced storage, and lower overhead for write-heavy workloads.
Below is a quick example that demonstrates how to create a partial index in PostgreSQL.
Continue readingWhen a prepared statement is created in PostgreSQL, the server has two possible strategies for executing it: use a generic plan or use a custom plan. Each time we run a prepared statement it will chose one of these two plans.
By default, it will use a custom plan for the first five runs. After that it will decide whether or not it’s more efficient to use a generic plan for subsequent executions, or stay with a custom plan.
This begs the question; how do we check to see which plan PostgreSQL chose?
Continue readingIf you’re getting MySQL error 1064 which reads something like “1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘READ FIRST’ at line 1” when using the HANDLER statement in MySQL, it could be that you’re qualifying the table with the database name when trying to read from or close a table.
When we use the OUTPUT clause in SQL Server, we have the option of providing expressions instead of just the column names. For example, we could use an expression that compares the old price with the new price and returns the difference. Such data could be handy, depending on what your goals are.
Let’s dive straight into an example that uses an expression in the OUTPUT clause.
If you’re getting an error that reads “Ambiguous column name“, followed by a column name, in SQL Server, it could be that you’re performing a join on two tables, but you’re not using the table names or aliases when referring to columns within those tables.
To fix this issue, be sure to qualify the columns with their table names or aliases.
Continue reading