Emulating UPSERT in PostgreSQL
Upsert is an interesting and powerful SQL statement not supported on all database managers. In this article we will cover a technique to emulate the upsert statement with other regular SQL statements in PostgreSQL. You can apply this technique in other database engines too.
The UPSERT statement
The upsert statement is used when we have two tables. One is the destination table, where we will do updates or inserts (hence the name upsert) and the other table is the source ...
Why You Should Use TeamSQL Instead of the Command Line for Redis
Redis doesn’t require much in terms of software, and in fact, you probably went straight into working with Redis using the command-line interface (CLI). While there’s no arguing with simplicity, especially if it works, here’s why you might consider using TeamSQL to manage your Redis environments instead of relying on CLI-based tools.
It might seem counterintuitive that you can simplify your tool set by adding another product (in this case, ...
Sharing Your Query Results to Slack
TeamSQL is always looking for ways to make collaborating with your colleagues on SQL queries easier, and today, you have one more way to share the results of your SQL query: via Slack!
You can now run a query and send the results to the Slack channel, group, or user of your choice. Your colleagues can then access the data by clicking on the hyperlink provided by TeamSQL. To keep your data safe, you can choose to encrypt and password-protect your results set.
How to Share ...
The GROUP BY Clause
The GROUP BY statement is often used with aggregate functions (such as COUNT, MAX, and SUM) so that you can group a set of rows by one or more columns. Here’s a high-level overview of how you can use it, as well as information on how it works and some tips to keep in mind.
As an example, here’s a table with some pets data.
We can count the number of each species present in our table using the GROUP BY clause.
How the GROUP BY Clause Works
Deleting Duplicate Data
In most cases, you don’t want duplicate rows in your database. The best practice is to set up your insert process so that data that is already in your tables aren’t added again, but what if that horse has left that barn and you already have duplicates?
In this post, we’ll show you how you remove duplicate data in your MySQL databases.
At a Glance
There are just a few basic steps when removing duplicate data from your table:
Back up your table!
Cast vs. Convert
When working with Microsoft’s SQL Server (and by extension, T-SQL), you have two options when making explicit conversions: CAST and CONVERT.
Many people use them frequently, and quite often interchangeably, but there are some cases when one is a better choice than the other. This post covers the similarities and the differences between the two, as well as when you’re better off using one over the other.
When doing explicit data conversions, CAST and ...
SQL Injection 101
SQL injection attacks occur when someone executes malicious SQL statements (also called payloads) that control a database server, typically in a manner not favorable to you. Any SQL-based database is vulnerable to these types of attacks, and it is one of the oldest, most common, and most dangerous types of attack that someone can direct toward your database. All it takes is one failure for an entire database to be compromised. In some cases, the attacker can even run commands ...
The Seven Types of SQL Joins
It’s unlikely that you’ll be working exclusively with data from one table, so you’ll need to be able to grab data from multiple tables. One of the ways to do this is using joins, and in relational databases, joins are what make relational databases relational. This post provides a quick refresher of the four main types of SQL Joins and three of its variants.
What is a join?
A join is a way of linking data from two or more tables based on a column shared between the tables. For ...
Using the CREATE Procedure and INSERT INTO Statement Templates
TeamSQL comes with built-in templates to guide you as you create new procedures or statements that change your databases. In this article, we’ll cover using the CREATE Procedure and INSERT INTO Statement Templates.
Procedure Template: CREATE
In a new TeamSQL query tab, begin typing CREATE. Notice that, as you type, the drop down menu of options filters. When you see CREATE PROCEDURE highlighted, you can hit the ENTER key to populate the window with a CREATE stored procedure ...