If you’re trying to use either lpad() or rpad() to pad a string in PostgreSQL but it’s just not working, maybe it’s because of the reasons listed below.
Fortunately this issue has a simple fix, and it can be resolved within seconds.
Continue readingIn 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 readingSQL 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 readingNothing 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 readingPostgreSQL 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 readingIn 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 readingThe 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.
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 readingBy 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.
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 reading