Avoiding Duplication Of Records
The issue of avoiding duplicate records in relational databases is one of the most common issues or problems. The primary mechanism to prevent that are the primary keys, however, in many databases or tables, primary keys are not defined. We are not going to discuss if not having a primary key is unacceptable or acceptable under the specific circumstance. We know that occurs, especially on data warehouses schemas. Then, in this environment where we don’t have primary keys or unique constraints ...
A Day At Races With TeamSQL 🏎
A DAY AT THE RACES WITH TEAMSQL
In this article, we took a public database with real and historical data about formula one races located at http://ergast.com/mrd/db/, we imported into a PostgreSQL database engine, and then we start querying using TeamSQL trying to cover and use a broad spectrum type of business queries and at the same time using different SQL clauses. We expect you can find some of the exciting queries presented here usable and applicable perhaps to your business data.
XPATH Language Usage With An Example In PostgreSQL
In these days XML documents are everywhere. They are mainly used to exchange information between applications; however, they are also used for many other purposes. Moreover, almost all database engines supported by TeamSQL include XML as a data type. In this article we use TeamSQL to store XML documents on a database table, then we extract elements from the XML by using XPath expressions, and finally, we execute queries inside the XML document.
XML documents are very ...
PostgreSQL Snippet List
Snippets are available with TeamSQL Pro Plan.
First item is the label of snippet so you can start writing one of the labels in TeamSQL Query Editor and use them.
SELECT * FROM...
Simple SELECT command
SELECT * FROM with WHERE clause...
Simple SELECT command with WHERE clause
SELECT with INNER JOIN...
SELECT Command with INNER JOIN
Insert into a table on "public"
INSERT INTO with SELECT...
Insert into a table on "public" using ...
Which database engines will you support?
In addition to MySQL, Microsoft SQL Server, PostgreSQL and AWS Redshift, we will be supporting those engines in the following weeks:
ALL YOU WANT TO KNOW ABOUT VIEWS
WHAT IS A VIEW?
In a short sentence, a view is a “saved query” associated with a name. Once you create the view (or save the query), you can use it as a table in other queries. Trying to explain with more detail what a view is, we can say that a view is another type of database objects like tables, indexes or stored procedures. From the point of view of a developer who is trying to write a SELECT query, a view is like any other regular table. The view can be used in the FROM clause as any ...
SQL COALESCE Function
The first time I saw the term COALESCE in a SQL manual, I thought it was a scientific function for some complex calculation. However, later I found that it is a straightforward and handy function. In this article, I will show several examples to explain how to use the COALESCE function.
THE REASON BEHIND COALESCE: THE NULL VALUE
Before go deep with COALESCE function, the reader needs to understand how the NULL value works in some expressions. A NULL value in a table column means ...
Keep your data safe with QuerySense
I will start this article with a real-life story: As a database consultant, I do a lot of different works. I write technical articles, do some other database tasks like SQL development and database administration. So, I have a lot of SQL clients, and tools installed on my computer. One sunny day I begin to work in the morning. While I am enjoying my coffee I opened my SQL client, and I run a simple script to create and populate a table for an article. Before I create a table, I try a “drop ...
Visualize your data with charts in TeamSQL
TeamSQL offers a new and redesigned feature to create your own charts based on query resulting data. The Charts feature of TeamSQL is now more powerful with advanced aggregation and visualization options. TeamSQL Charts is all free during Release Candidate period. Visualize your data by creating the charts you need, right in TeamSQL.
You can easily create bar, area, line, pie, scatter, funnel, and bullet charts, as well as heat maps, using the data you return from your database. You ...
Emulating UPSERT in PostgreSQL
Upsert is an exciting 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 to other database engines too.
The UPSERT statement
We use "UPSERT" statement 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 table, where the informat...
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 ...