Sometimes when working with very large sets of data, you may not want to return all of the data at once. I discussed using TOP in my previous article, which allowed you to only get a number of rows from the start of the results from a query. However, if you want to see the … Read more
The SELECT statement is used to retrieve information from a database. Following the execution of a SELECT statement, a tabular result is held in a result table (called a result set). It allows you to select the table columns depending on a set of criteria. SELECT statements might be as straightforward as selecting a few … Read more
Configuration parameters are the most mythical creatures in the world of PostgreSQL. Ordinary PostgreSQL users often do not know that PostgreSQL configuration parameters exist, let alone what they are and what they mean. There is a good reason for such ignorance since, in real life, ordinary users don’t have any say in how these parameters … Read more
When you are writing T-SQL scripts to save away, for example one to create a new database, there are often optional bits of code you need to run sometimes, and not others. In the creating a database script, there are quite a few parameters you will want as part of your script. The database name, … Read more
Applications of all types commonly access MySQL to retrieve, add, update, or delete data. The applications might be written in Python, Java, C#, or another programming language. Most languages support multiple methods for working with a MySQL database and manipulating its data. The approach you take when accessing MySQL will depend on the programming language … Read more
In this article, Joe Celko gives us a history of the different character sets that are used in computing and how that can pertain to your usage in relational databases. Some of these you may have never heard of!… Read more
I’ve been at this for a while now, and have a very particular set of rules and coding conventions that I follow when writing and, more importantly, reviewing T-SQL code. I often perform code reviews and thought it would be fun to frame this exercise: a completely fictitious stored procedure hits my desk, I’ll reject … Read more
In the first security article, PostgreSQL Basics: Roles and Privileges, I discussed how roles (users and groups) are created and managed in PostgreSQL Depending on your background with permissions, particularly in other database products, some of those nuances in how permissions work may have been surprising. Understanding how roles and privileges work in Postgres is … Read more
There might be a time when you might want to return just a few rows of a result set, instead of the complete set. This might be useful if you want to just validate a selection criteria or a few rows of data. For whatever the reason the TOP clause can be used to return a specific number or a percentage of rows from a result set. This article will cover using the TOP clause in a SELECT statement and how it can be used to return a partial set of records.… Read more
One of the most important aspects of data management is the ability to ensure that the data in your database is well defined and consistent. Some aspects of that are ensured through the relational data structures you design. Another piece of control is using the correct data type. Then, we get to constraints. A constraint is a way to validate data prior to adding it to your database. This is one more tool in the toolbox that helps you maintain good data.… Read more
Optimizing read operations is one of the most prominent problems concerning any database administrator. No matter what kind of database management system is in use – MySQL, its flavors like Percona Server or MariaDB, MongoDB, TimescaleDB, SQL Server, or others, read queries are a concern. The primary read query example is SELECT queries, but a lot of this pertains to UPDATE and DELETE as well since they too have to fetch rows to be operated on.… Read more
Information security, whether in databases, applications, or larger IT systems, is based on the principles of Authentication and Authorization, often referred to as AuthN and AuthZ respectively.
Likewise, PostgreSQL security involves creating roles that allow access to server resources (AuthN) and then granting privileges to database objects appropriately (AuthZ). Understanding how each of these components work together is essential to effectively manage security in a PostgreSQL cluster.… Read more
Once data is in a table it needs to be maintained. New records will need to be added, and existing records will need to be updated, and/or deleted. To keep data maintained, the insert, update or delete actions may need to be performed. This article will show how those three data manipulation language (DML) actions can be synchronized together within a single basic MERGE statement.… Read more
As with any other relational data management system (RDBMS), PostgreSQL uses indexes as a mechanism to improve data access. PostgreSQL has a number of different index types, supporting different behaviors and different types of data. In addition, again, similar to other RDBMS, there are properties and behaviors associated with these indexes.
In this article I’m going to go over the different types of indexes and some index behaviors. We’ll get into what the indexes are, how they work, and how best you can apply them within your databases. I’m hoping you’ll develop an understanding of which indexes are likely to work better in each situation.… Read more
As with many relational database management systems, MySQL provides a variety of methods for combining data in a data manipulation language (DML) statement. You can join multiple tables in a single query or add subqueries that pull data in from other tables. You can also access views and temporary tables from within a statement, often … Read more
In this article, I want to talk about a topic that you may never need. The only time I have really had need to look at the bits in a byte pattern. In (what is now) ancient versions of SQL Server didn't have such self-describing columns like in its metadata objects like it does now.… Read more
A subquery is a type of query that is embedded—or nested—into a data manipulation language (DML) statement. The data returned by the subquery is passed into the DML statement and incorporated into its overall logic. The subquery itself is typically a SELECT statement, although you can also use a TABLE statement or VALUES statement. Even so, the SELECT statement continues to be the most common choice for subqueries, and that’s the one I focus on in this article.… Read more
If only the entire world used UTC, wouldn’t life be so much easier? We can dream, can’t we? While some software applications can live in an ecosystem where all dates and times can be stored in a single time zone with no conversions needed, many cannot. As applications grow and interact more with international customers, … Read more
In the last few articles in this series, you learned about three important data manipulation language (DML) statements: SELECT, INSERT, and UPDATE. The statements make it possible to retrieve, add, and modify data in a MySQL database. Another DML statement that is just as important is DELETE, which lets you remove one or more rows from a table, including temporary tables. In this article, I focus exclusively on the DELETE statement to help round out our discussion on the core DML statements in MySQL. Overall, the DELETE statement is fairly basic, but one that’s no less necessary to have in your arsenal of DML tools.
… Read more
Over time data in SQL Server tables needs to be modified. There are two major different aspects of modifying data: updating and deleting. In my last article “Updating SQL Server Data” I discussed using the UPDATE statement to change data in existing rows of a SQL Server table. In this article I will be demonstrating … Read more
Subscribe for more articles
Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.
