Whenever we create a table in PostgreSQL, a composite type is automatically created behind the scenes. This composite type is based on the table that we created. Each column in the table becomes a field in the composite type.
If the table already uses a composite type, then the composite type that PostgreSQL creates will include that type in its definition, thereby creating a situation where we effectively have a nested composite type.
Continue readingIf you’re getting an error that reads something like “Cannot insert the value NULL into column ‘CatId’, table ‘demo.dbo.Cats’; column does not allow nulls. INSERT fails” in SQL Server, it’s because you’re trying to insert a NULL value into non-nullable column (i.e. a column with a NOT NULL constraint).
We can’t insert NULL values into non-nullable columns.
To fix this issue, insert a non-NULL value.
Continue readingCommon Table Expressions (CTEs) are a useful way to simplify complex queries in SQL Server (and other SQL databases). More often than not, we’ll see CTEs that are used by SELECT statements, but this isn’t necessarily required. CTEs can be followed by SELECT, INSERT, UPDATE, DELETE, or MERGE statements.
This article provides a simple example of using INSERT INTO with a CTE in SQL Server.
Dropping a primary key in any RDBMS is a critical operation that requires careful consideration, especially when there are related foreign keys in other tables.
The decision to drop a primary key is not to be taken lightly.
That said, this article walks through the process of how to drop a primary key in SQL Server, while highlighting important considerations.
Continue readingIf you’re getting an error that reads something like “No column name was specified for column 2 of ‘SalaryTotals’” in SQL Server, it could be that you’re running a subquery or CTE, but you’re not providing a column name for an aggregate function, or for the XML or JSON result of the CTE.
To fix this issue, use an alias to define a column name for all aggregate functions and XML/JSON outputs in the subquery or CTE. Or if it’s a CTE, you can specify the column name using the CTE syntax.
Continue readingIn MySQL, prepared statements are a feature that allows us to execute the same SQL query multiple times with different parameters. They can improve performance, make our code cleaner, and help defend against SQL injection attacks.
In this article, we’ll explore prepared statements in MySQL, with examples and practical tips on how to use them effectively. We’ll also cover the importance of deallocating statements to free up resources.
Continue readingThe HANDLER statement in MySQL provides a low-level mechanism to directly access storage engine-level functionality, bypassing some of the overhead associated with regular SQL queries. It can be especially useful for traversing a database in high-performance scenarios.
HANDLER is available for MyISAM and InnoDB tables, and can be used as a faster alternative to the SELECT statement.
If you’re getting an error that reads “Invalid column name ‘$action’” in SQL Server, it could be that you’re using the $action argument of the OUTPUT clause for an operation that doesn’t support this argument.
Or it could be that the operation does support the argument, but you’re referencing it with the wrong syntax.
The $action argument can only be used with MERGE operations, and it must be done correctly.
To fix this issue, be sure to only use the $action argument in MERGE operations and to reference it with the correct syntax.
The MERGE statement in SQL Server allows us to perform INSERT, UPDATE, and DELETE operations in a single query. This makes it an efficient way to synchronize two tables, typically between a source and a target, based on a defined condition. Rather than running separate queries to handle insertions, updates, and deletions, we can combine all of these operations into one statement; the MERGE statement.
Perhaps one of the lesser-known clauses when it comes to SQL joins is the USING clause. While the more widely-used ON clause allows us to explicitly specify join conditions, the USING clause simplifies the syntax when the join is based on columns with the same name in both tables.
In this article, we’ll dive into the USING clause, compare it to the ON clause, and look at examples that illustrate the difference.