Update Views in TimescaleDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this lesson, we’ll learn how to Update Views in TimescaleDB. At times you may have a query so complex you would like to package it, give it a name, and call it from within other queries as if it were a table or view. Views provide a way to attain that in a fairly easy manner, making query design much easier, more efficient, and more modular. We’ll learn here to create, use, and update views in our TimescaleDB SQL. This lesson is structured as follows:

  • What? What’s a View in TimescaleDB and what does it do? Including exploration of syntax.
  • How? How do we create, update, and use Views in our TimescaleDB database code?

Prerequisites

  • Novice-level understanding of how to build SQL statements in TimescaleDB (or Postgres, Microsoft SQL Server, etc.), with something that will work with TimescaleDB like the free Dbeaver database admin tool or some other relational database administration package, or by using server-side script scripting languages like ASP.net, Python, PHP, Java, C#, Node.js, VB.Net, Ruby, etc. that supplies a connection to your TimescaleDB as well as a method for sending it SQL database commands.
  • Experience with the use of the more basic SQL commands, including use of and relation to column, table, SELECT, AS, JOIN, HAVING, and WHERE.

The first thing to do is set up a database instance. We prefer using Objectrocket because we like the amount of value and efficiency they provide.

Create a TimescaleDB database instance on ObjectRocket

  • (1) Go to kb.objectrocket.com and “Log In”.
  • (2) Use left menu to “Create Instance”.
  • (3) In the “Name” field, add a descriptive name for your instance.
  • (4) Under “Select your service” choose the system you want to use from “CockroachDB, Elasticsearch, PostgreSQL, Redis, TimescaleDB, and MongoDB”.
  • (5) Choose the “Cloud Provider” and “Type” you want.
  • (6) Select “Version” and “Region”.
  • (7) Click the “GO TO STEP 2” button.
  • (8) Make a choice in the “Pick Your Flavor” section.
  • (9) Choose the “Capacity (Storage/Memory)”. Notice how this choice influences your total fee at the bottom right.
  • (10) Click the “GO TO STEP 3” button.
  • (11) Under “Add a Whitelist IP” pick “ALOW ANY IP” or “USE MY IP” or “Add my IPs later”.
  • (12) Choose between “Master” and “Replica” and click the “ADD” button.
  • (13) Now click the “CREATE INSTANCE” button at the bottom.

Before we start building queries, let’s look at how to use Flask’s psycopg2 to connect to a TimescaleDB instance at ObjectRocket.

Connect to TimescaleDB

1
2
3
4
5
6
7
8
9
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

What’s a TimescaleDB View?

TimescaleDB Views are like a query you can create, name, and call into use as if it is a database table. Pretty simple, really. If you ever developed with a Microsoft Access database, you would instantly know the term because MS Access, since version one, has had “saved queries” that are pretty much exactly what a View is in TimescaleDB and Postgres. Quite often, the more normalized the database happens to be, the more utility you may find in using Views.

Creating a View

View Creation Syntax

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE
    VIEW viewName
    (
        colName01
        , colName02
        , colName03
        , etc
    )
    WITH
    (
        nameOption [VALUE] [, ... ]
    )
    AS query;

Let us take the above example line-by-line in a quick and high level manner:

  • We used CREATE OR REPLACE instead of CREATE because we need to guard against retrieveting overwrite errors. You may choose to use just CREATE in case you need to find and trap errors and react because your situation dictates that you want to understand if a particular View already existed. We are taking the simpler route here to bring simplicity to your learning.
  • viewName is the name you give the new View so you can call it from within other queries, code, script, SQL, etc. Remember, TimescaleDB treats Views as if they’re tables.
  • colName01, colName02… This is the structure you would use to reference column names in your source table.
  • WITH is optional. It refers to parameters you may need to give to the new view.
  • query is your SELECT or VALUES statement that gives the view its records and columns. See below for how this looks in a use case.

IMPORTANT!

The new query must return the same columns that were created by the existing view SQL. This means the same columns in the same order and being the same data types, but the view may add new columns to the end. If the column names are not given, the query provides them based on order. The functions or operations generating the output columns may be different.

Let us now use that above syntax to create a view of our own. We will start by using two data tables.

tblTechnologies table:

idTechtxtNameidCategory
0Postgres5
1Mongo6
2VB.NET7
3TimescaleDB5
4Python7
5PHP7

tblCategories table:

idtxtNameCategory
5RDB
6NoSQL
7Language

Before we go any further, plsimplicity notice that in the tblTechnologies table, focusing on the idCategory column, the numbers you see there refer to the column we named “id” in the tblCategories table.

Normally, in a world without Views or Stored Procedures, we would query the data for a company report by using a join like the following:

1
2
3
4
5
6
7
SELECT
    tblTechnologies.idTech
    , tblTechnologies.txtName
    , tblTechnologies.idCategory
    , tblCategories.txtNameCategory
FROM tblCategories
INNER JOIN tblTechnologies ON tblCategories.id = tblTechnologies.idCategory;

Which gives us the following records when executed:

![TimescaleDB view of records from joined tables](https://i.gyazo.com/a57f7b3105ea2fb5dc14eac8f2ad1a0f.png “TimescaleDB view of records from joined tables”)

Note: Odds are that we won’t require an output of “idCategory” here, but we left it in to provide additional clarity.

The next step is to make our SQL into a Timescale View. We’ve already written all the relevant SQL above and now only want to put it together. Here’s what it looks like when we have done so:

1
2
3
4
5
6
7
8
CREATE OR REPLACE VIEW viewTechnologies AS
SELECT
    tblTechnologies.idTech
    , tblTechnologies.txtName
    , tblTechnologies.idCategory
    , tblCategories.txtNameCategory
FROM tblCategories
INNER JOIN tblTechnologies ON tblCategories.id = tblTechnologies.idCategory;

It’s that easy! Note how the only change to our Timescale query was to add the following line of code at the top before our SQL SELECT statement?

1
CREATE OR REPLACE VIEW viewTechnologies AS...

Yay: now you can use that View via the name viewTechnologies anywhere you would normally reference a table, like in your code or SQL.

Miscellaneous

  • As we discussed a bit above, adding “OR REPLACE” to “CREATE VIEW”, serves to update our view with whatever new code you put after that “CREATE OR REPLACE VIEW viewTechnologies AS” phrase in case a view with the same name already exists and prevents excessive dialog boxes and even errors.
  • Another way to update a view would be if you need to change the name of a view. To do that, you could use the following SQL command: “ALTER VIEW viewTechnologies RENAME TO viewTechnologiesJoined”.
  • As you may have noticed above, you don’t have to name your view with a prefix of “view”. That’s just the naming convention we use here so as to provide as much clarity, readability, and simplicity as possible.
  • Finally, if you need to delete a view, the following SQL command will make it happen: “DROP VIEW viewNameToBeDeleted”.

Conclusion

In this lesson document we studied and practiced creating Views in TimescaleDB and we looked into why and when Views can be useful and powerful tools for increasing simplicity, efficiency, modularity, and even how readable your code is to yourself and others who might inherit your application. Code samples included.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.