Tag: how to

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 →

Fix “date/time field value out of range” in PostgreSQL

Posted on by Ian

If you’re getting an error that reads ‘date/time field value out of range‘ in PostgreSQL while using a function such as date_add(), date_subtract(), or date_trunc(), it’s probably because the date value you’re passing to the function is an invalid date.

It’s possible that you’ve got the month and day in the wrong order.

To fix this issue, be sure that you pass a valid date. It may be that all you need to do is switch the day and the month around. Or it could be that you need to change your datestyle setting.

Continue reading →

Fix Error “cannot take logarithm of zero” in PostgreSQL

Posted on by Ian

If you’re getting an error that reads “ERROR: cannot take logarithm of zero” when using either the log() function or log10() function in PostgreSQL, it’s probably because you’re passing an argument of zero.

These functions require a value greater than zero. This is true even for the base argument of the log() function (the argument that specifies which base to use).

To fix this issue, be sure to pass a value greater than zero to these functions.

Continue reading →

5 String Functions that Return Length in PostgreSQL

Posted on by Ian

PostgreSQL provides us with a handful of string functions that return the length of a given string.

But the result between these functions can be different, depending on which function we use. That’s because the “length” can be different, depending on what we’re measuring. Are we talking about the number of characters in the string? Or the number of bytes in the string? Or perhaps we want to know the number of bits in the string.

The function we use will depend on which of the above we’re trying to measure. Below are five functions that cater for each of the above questions.

Continue reading →