In PostgreSQL, the md5() function computes the MD5 hash of the specific text, and outputs the result in hexadecimal.
We pass the text as an argument when calling the function.
Continue readingAuthor: Ian
SQL for Beginners: 10 Must-Know Concepts to Kickstart Your Database Journey
SQL stands for Structured Query Language. It’s the go-to query language for most of us who develop relational databases. If you want to work with relational database management systems, learning SQL is pretty much non-negotiable. Especially if you want to become a SQL developer.
If you’re interested in becoming a SQL developer, here are ten essential SQL concepts that you need to learn.
Continue readingHow to DROP Tables with Foreign Key Relationships in PostgreSQL
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 reading2 Functions that Return the Position of a Substring within a String in PostgreSQL
PostgreSQL sometimes provides us with multiple functions that basically do the same thing. Such is the case with the following two functions that return the position of a given substring within a string.
Continue readingA Quick Look at TO_HEX() in PostgreSQL
In PostgreSQL, we can use the to_hex() function to convert a number to its equivalent hexadecimal representation.
The function accepts the number as an integer or bigint, and it returns the result as a text representation of its hexadecimal equivalent.
Continue readingIntroduction to the Enum Data Type in SQL
The Enum (enumeration) data type in SQL is a specialised string object that allows us to define a list of predefined values for a column. This data type can be particularly useful when we want to restrict the possible values that can be stored in a specific column to a fixed set of options.
How to TRUNCATE a Temporal Table in SQL Server
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 readingHow to Specify your own Subscript Range when Creating an Array in PostgreSQL
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.
A Quick Look at the ASCII() Function in PostgreSQL
In PostgreSQL, we can use the ascii() function to get the numeric code of the first character of the argument.
When using UTF8, the function returns the Unicode code point of the character. Other multibyte encodings require an ASCII character.
Continue readingUnderstanding the SQL Subquery
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