Tag: how to

How to DROP Tables with Foreign Key Relationships in PostgreSQL

Posted on by Ian

Nothing pulls me out of the “zone” quicker than attempting to drop a table, only to be told “Nope, that table has a dependency!”.

Granted, this usually only happens in my development environments, as I rarely have any need to drop tables in a production environment.

And that’s probably why it “pulls me out of the zone”. I suddenly have to stop and start thinking about what tables contain foreign keys to the one I’m trying to drop. In dev environments, we’re often trying things out and so it’s not out of the question that we might inadvertently try to drop a table without realising it has dependent objects.

Fortunately, when we’re working with PostgreSQL, we have a quick and easy method to overcome this barrier.

Continue reading →

How to TRUNCATE a Temporal Table in SQL Server

Posted on by Ian

Temporal tables in SQL Server provide a powerful way to track historical data changes. However, when it comes to clearing out data from these tables, the standard TRUNCATE statement doesn’t work the same as it does on normal (non-temporal) tables.

This article will guide you through the process of truncating a temporal table while maintaining its integrity.

Continue reading →

How to Specify your own Subscript Range when Creating an Array in PostgreSQL

Posted on by Ian

By default, PostgreSQL arrays are one-based. This means that we need to use 1 if we want to reference the first element in the array, 2 for the second, and so on.

But we also have the option of specifying our own subscript range for an an array. For example we could create a zero-based array, a ten-based array, or even a negative value such as a negative ten-based array.

We can do this by using subscripted assignment to specify the actual subscript range for the array. Basically, we prefix the array with the subscript range, enclosed in square brackets, and an equals sign (=) between it and the array.

Continue reading →

Understanding the SQL Subquery

Posted on by Ian

One of the things I love about SQL is just how easy it is to write a simple query that returns a meaningful result set, without having to a whole bunch of complex code. And I imagine most beginners are relieved when they discover this too.

However, while basic queries can go a long way, there’s a whole world of advanced techniques that can take our SQL development to another level. Somewhere in that world, would be the SQL subquery.

In this article, we look at the SQL subquery. We’ll explore what subqueries are, how they work, and when to use them. We’ll also look at some simple examples to demonstrate their use.

Continue reading →

10 Essential Database Concepts that All Beginners MUST Learn

Posted on by Ian

When I first started building websites 25 years ago, everything was static. Our primary building block was HTML, with a bit of CSS and JavaScript sprinkled in for good measure. Our content would be incorporated into the HTML documents, and so there was no separation of functionality and content.

Regardless, I was just happy to be able to build these amazing things that I could publish for the world to see.

But within a few years I had quickly learned about the power of databases and how they could completely transform the websites we were building at the time.

Continue reading →

Time Travel in SQL Server: Using Temporal Tables for Historical Data Analysis

Posted on by Ian

Temporal tables, introduced in SQL Server 2016, provide a powerful mechanism for tracking historical changes to data. This feature is particularly useful for auditing purposes, allowing organisations to maintain a complete history of data modifications without the need for complex triggers or custom logging solutions.

In this article, we’ll explore how to implement and use temporal tables for auditing in SQL Server, along with examples to demonstrate.

Continue reading →

How to Return Values in SQL Without using SELECT

Posted on by Ian

The SQL SELECT statement is possibly the most commonly used SQL statement. It’s often used to return data from a database, but it can also be used to call functions that return data. The SELECT statement can also be used to return static values, such as string literals.

But the SELECT statement isn’t the only way we can return data in SQL. Another way to return values is with the VALUES statement.

Continue reading →

So PostgreSQL ARRAY_APPEND() Works but ARRAY_PREPEND() Doesn’t? Try this.

Posted on by Ian

If you’re updating arrays in PostgreSQL and you’ve suddenly realised that some of the arrays aren’t being updated, it could be due to the following.

If you’ve been using the array_append() function and the array_prepend() function, you may have found that one function works but the other doesn’t. For example array_append() works but array_prepend() doesn’t, or vice-versa.

Continue reading →