When running SQL database queries, sometimes we need to use a window function in order to get the results we’re looking for. A window function is an aggregate-like function that enables partitioning and ordering of data within a result set.
The OVER clause is what enables us to create a window function.
The examples below demonstrate how we can incorporate the OVER clause in our SQL queries.
If we want to remove a CHECK constraint from a table, but we don’t want to drop the table or column, we can use the ALTER TABLE statement with DROP CHECK.
Once the constraint has been dropped, data can be entered into the table without having to conform to the rules of the CHECK constraint.
If you’re getting MySQL error 1364, that reads something like Field 'column_name' doesn't have a default value, where column_name is the name of a column in your table, it’s probably because you’re trying to perform an operation that retrieves the column’s default value, but it doesn’t have a default value.
This can happen when the column is set to NOT NULL and hasn’t been defined with an explicit default value.
The solution to this issue will depend on what we’re trying to do. Below are examples of how to fix this error.
Continue readingIn MySQL, we can use the sys.quote_identifier() function to quote a string to produce a result that can be used as an identifier in a SQL statement.
The string is returned enclosed by backticks (`), with each instance of a backtick doubled.
This function can be handy when a value to be used as an identifier is a reserved word or contains backtick characters.
Continue readingThe information schema is a standard set of read-only views that provide metadata about objects in a database. These views are typically accessed by qualifying the view name with INFORMATION_SCHEMA (for example INFORMATION_SCHEMA.TABLES). But before we do this, we need to know which views are available in the first place.
Window functions can be a useful tool when writing SQL queries. They allow us to include aggregate data across multiple rows without getting those pesky errors that sometimes occur when we try to use an aggregate function in the wrong way.
In this article, I aim to provide a simple overview of window functions and how they can be used to provide a more useful result set when running SQL queries.
Continue readingWe have a number of options when it comes to returning a list of sequences in PostgreSQL. By “sequences”, I mean “sequence objects”, or “sequence generators”. These are objects we can create in order to generate a series of sequential numbers.
Here are three ways to get a list of sequences in Postgres.
Continue readingIn MySQL, the sys.ps_thread_stack() function returns a JSON formatted stack of all statements, stages, and events within the Performance Schema for a given thread ID.
If you have an existing column in MySQL that you want to add a DEFAULT clause to, the following example may help.
To explicitly add a DEFAULT clause to an existing column in MySQL, we can use the ALTER TABLE statement along with the MODIFY clause.
We can also add a DEFAULT clause implicitly by defining a column as nullable. When we do this, MySQL implicitly adds its own DEFAULT clause.
In MySQL, the sys.extract_schema_from_file_name() function is a stored function in the sys schema.
It accepts a path name, and returns the path component that represents the schema name.
This enables us to get the schema from a given file path.
Continue reading