In this article, we will learn how to create a new instance of Azure Database for PostgreSQL as well as understand how to use existing tables as templates while creating new tables.
Introduction
Database development typically consists of building as well as maintaining a variety of database objects like schemas, tables, views, functions, procedures, and other similar objects. Often as part of regular development exercises for a variety of day-to-day needs, one needs to create databases as well as database objects using an existing object as a template. This helps to reduce any manual errors that may get introduced while trying to replicate or reference existing objects. Tables are at the top of the database objects hierarchy. Creating new tables from existing tables is not a new concept and CREATE TABLE AS is one of the most popular commands that is used for creating new tables from existing tables, where even the data is populated in the new table is created. Let’s say that the intention is to just replicate the fields of the table or constraints or specific part of the table definition as well as attributes, then we need more granular control over the table creation process where we can control what part of table definition should be referenced from the template. Postgres is one of the most popular open-source database management systems and it offers the same database object hierarchy using the most popular SQL query language format. Azure Database for PostgreSQL is Azure cloud’s offering of PostgreSQL databases on Azure cloud. Postgres offers some unique features in its query language to facilitate the use of existing tables as templates while creating new tables.
Pre-requisites
We need to first have an Azure account in place with the required privileges to operate and administer Azure Database for PostgreSQL service. It is assumed that such an account and setup are already in place. Assuming this setup exists, navigate to the Azure portal home page and type “Azure Database for PostgreSQL”, and open the resulting link which will land you on the homepage of this service. Click on the New button to invoke the new instance creation wizard. On the first page, we would be shown the option to select the edition of PostgreSQL as shown below. The single server option is for a default instance where we do not need much customization. The rest of the options like Flexible server are for an instance where we need greater flexibility in configuration, Hyperscale is for a massively parallel processing (MPP) option while Arc is for a hybrid option. For our needs of this exercise, the Single server option would suffice.
In the next step, select the basic details of the instance like the subscription, region, server name, location as well as the username and password for the instance being created. These details will determine where the instance would be hosted as well as the credentials to access it.
In this step, we can optionally select the encryption-related setting as shown below. For now, we can skip this option and move to the next step.
We can optionally specify tags in this section. This can help one remember that this instance is being created for a specific purpose and we may want to terminate it once it is not required.
Move to the next step where we can review the configuration that we have selected so far and confirm the creation of the instance. Once the instance gets created, click on the Go to Resource button to land on the home page of the instance. From the left pane, click on the Connection Strings to find the connection string that you can use with a variety of tools and frameworks to connect to this instance.
We would need an editor like pgAdmin which is one of the most popular editors for working with PostgreSQL. It is assumed that at least one such editor is already installed on your local machine and configured to connect with the instance. For this exercise, we will be using pgAdmin. Open pgAdmin and add a new server as shown below. When we are creating a new server, we need to provide the endpoint of the instance as well as the credentials for connecting to the instance. Once the instance has been connected, it would get listed on the left pane as shown below.
This completes our pre-requisites for starting the actual exercise of exploring the options to using existing tables as templates for creating new tables in the Azure Database for PostgreSQL.
Using existing tables as templates
We need at least one table to start with which we can use as a template while creating another table in Azure Database for PostgreSQL. The default schema in Azure Database for PostgreSQL is a public schema. We will create a sample table as shown below. This table has a few fields and one primary key constraint. This table is just a sample, and one can use any existing table or create a new table which we will consider as a template while creating the new table.
Let’s say that we intend to create a new table named employee_copy and we intend to just use the existing fields as part of the table definition. We do not intend to carry forward anything else from this existing table in our new table – neither constraints nor data or anything else. In that case, we can use the LIKE operator and specify the name of the table as shown below. Here we are using the CREATE TABLE command which is the standard command in SQL to create new tables in Azure Database for PostgreSQL. The key difference here is the use of the LIKE operator with the name of the table as shown below. Here we are not specifying the schema name along with the table name as the referenced table and the new table being created are both in the same public schema. One thing to observe here is that the new table got created without any primary key constraint. This can be seen in the left pane as shown below where the employee table has the fields listed with a primary key constraint in the constraints section, but this constraint did not get created in the new table employee_copy. This is the default behavior when we just specify the table name using the like operator in Azure Database for PostgreSQL.
While in the above case, the constraint did not get created, this may not be desired in many cases as we may want to have some of the same properties as the referenced table. Examples of such properties include Defaults, Constraints, Indexes, Comments, etc. To include these properties or objects that are associated with the referenced table, we can use the INCLUDING keyword and specify the property or object that we want to include in the new table being created on Azure Database for PostgreSQL. As shown below, we can use the keyword INCLUDING ALL which will clone the existing table with all the associated properties and objects but without data.
In this way, by using two simple keywords LIKE and INCLUDING we can use existing tables as templates and have full granular control over cloning properties as well as objects associated with existing tables. While CREATE TABLE AS command helps to have the same fields and data that are resulting from the SELECT query in the command, this command helps to granularly curate the table definition of an existing table and create a new table. In this way, both commands serve a different purpose and cater to different needs of developing tables.
Conclusion
In this article, we learned how to create an instance of Azure Database for PostgreSQL and connect to it using pgAdmin installed on a local machine. Then we created a new table on this instance, which we used as a template for creating a new table with granular control to select which associated properties or objects from the template table should be created for the new table.
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023
- Overview of the SQL Median function - January 4, 2023