Databases 9 results

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.     App Simplification It might seem counterintuitive that you can simplify your tool set by adding another product (in this case, ...

Using TeamSQL with Redis

TeamSQL now supports Redis, a data structure store used as a database, cache, and message broker. Here’s how to connect to your Redis instance and what it’s like working with Redis in TeamSQL. Connecting TeamSQL to Your Redis Environment Launch TeamSQL and sign in to your TeamSQL account.       Click the plus sign next to Connections to bring up the Add Connection wizard.   Select Redis as your Database Type to get the ...

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 &nb...

Connecting to Amazon Redshift with TeamSQL

This article covers how you can connect TeamSQL to your Amazon Redshift database servers, as well as how you can connect using SSL.     NOTE: You can connect to AWS Redshift with TeamSQL, a multi-platform DB client that works with Redshift, PostgreSQL, MySQL & Microsoft SQL Server and runs on Mac, Linux and Windows. You can download TeamSQL Beta for free: https://teamsql.io/   In the Explorer pane, click on the plus icon next to CONNECTIONS. &nb...

Adding a New MySQL Connection via SSH Tunnel

In the Explorer pane, click on the plus icon next to CONNECTIONS.     This launches the Add Connection window where you can create new connections from TeamSQL to your server(s).   To create a connection to a MySQL database set the Database Type to MySQL.   Note how the window now displays Create a new MySQL connection at the top. To connect to your MySQL database server, you’ll need to provide the following paramet...

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 ...

MySQL JSON Support & Using JSON Fields

If you’re integrating TeamSQL with MySQL version 5.7.8 (or later), you can access data in JSON (JavaScript Object Notation) documents.   We’ll start by creating our table. We can easily create tables in TeamSQL by running a Create procedure using a query window. CREATE TABLE `sakila`.`json_examples`( `first_name` VARCHAR(50), `last_name` VARCHAR(50), `languages_spoken` JSON) ENGINE=`InnoDB` AUTO_INCREMENT=1;   We’ll create a simple three-column table that will store ...

Import Data into Redshift Using the COPY Command

Importing a large amount of data into Redshift is easy using the COPY command. To demonstrate this, we’ll import the publicly available dataset “Twitter Data for Sentiment Analysis” (see Sentiment140 for additional information).   NOTE: You can connect to AWS Redshift with TeamSQL, a multi-platform DB client that works with Redshift, PostgreSQL, MySQL & Microsoft SQL Server and runs on Mac, Linux and Windows. You can download TeamSQL for free: https://teamsql.io/  ...