Rajendra Gupta
PostgreSQL extension for Azure Data Studio

Access PostgreSQL databases using the Azure Data Studio extension

January 16, 2020 by

This article installs PostgreSQL on Ubuntu 16.4 operating system and connects the database using Azure Data Studio.

Introduction

PostgreSQL is an open-source relational database system. It is a cross-platform database that works on Windows, Linux, and macOS as well. It is popular among organizations for reliability, data integrity, correctness, and multi-version concurrency control. We also call it just Postgres.

Few useful features of PostgreSQL database are as follows:

  • User-defined data types
  • Multi-version concurrency control
  • Nested transactions
  • Referential integrity
  • Point-in-time recovery
  • ACID-compliant
  • Open-Source database

Prerequisites

We have the following requirements for this article.

We can view the [rajendra-VirtualBox] with Ubuntu 16.04.5 OS in the following image:

Ubuntu 16.04.5 OS

  • You can go through the article SQL Server 2019 on Linux with a Docker container on Ubuntu and do the following task:
    • Get the IP address of the Ubuntu virtual machine
    • Install and configure Open SSH terminal so that you can connect to this virtual machine outside the VirtualBox
    • Download the required package and update the repository
    • Download PuTTY for connecting with Ubuntu using the SSH terminal
    • Install Azure Data Studio on Ubuntu

We can view the IP address for the virtual machine is 192.168.225.25 in the following image:

Install Azure Data Studio on Ubuntu

PostgreSQL package installation in Ubuntu

We get the PostgreSQL package in the default repositories of Ubuntu. We can directly install it using the apt package. Let’s connect to Ubuntu using Putty:

connect to Ubuntu using Putty

The following command installs the PostgreSQL along with additional utilities in Ubuntu:

It checks for dependency, prepares a list of a new package that it needs to download and installs. We can see that it needs permissions for downloading 19.7 MB of additional disk space:

Dependency package

Enter Y and it starts downloading the packages:

Enter Y and it stats downloading the packages

We get the following output once the configuration of PostgreSQL is complete:

configuration of PostgreSQL

PostgreSQL authentication

PostgreSQL uses roles for handling authentication and authorization. By default, it creates a user account Postgres with default role Postgres. By default, PostgreSQL does not require a password for authentication. It provides the following authentication methods:

  • Ident authentication: It uses OS identification to verify the user’s credentials. It uses port 113 for this authentication. It is the default authentication model
  • Peer authentication: It uses peer authentication for local connections. In this mode, it checks the logged-in user name with the PostgreSQL database user

We can connect to PostgreSQL using the following command:

It connects and changes the prompt from $ to postgres=#:

connect to PostgreSQL database

Once a connection establishes, we can use the ALTER USER command for changing the password of the Postgres user.

The following command changes the Postgres user password to “Welcome123”. We get the output ALTER ROLE as a result of the ALTER USER command:

postgres # ALTER USER postgres PASSWORD ‘Welcome123’;

Change the password of PostgreS user

PostgreSQL extension for Azure Data Studio

We can extend the functionality of Azure Data Studio using extensions. We have explored a few useful extensions in SQL Shack articles.

We get an extension for connecting PostgreSQL as well. Navigate to the market place and search for it:

PostgreSQL extension for Azure Data Studio

We can connect to PostgreSQL using this extension and query database using the IntelliSense query editor.

Click on Install in the PostgreSQL extension. It downloads the required package from the Github and installs it:

Install in the PostgreSQL extension

Now, we will use this extension for database connection and querying databases. Click on the new connections icon, as shown below:

New connection

It opens the Connection details window. In the connection type, we get the following connection types:

  • Microsoft SQL Server
  • PostgreSQL

Connection Type PostgreSQL

We need to provide the following inputs:

  1. Connection type: PostgreSQL
  2. Server name: Enter localhost if you have Azure Data Studio and PostgreSQL on the same host. We can also connect the PostgreSQL from outside the VM but make sure the firewall is open in Ubuntu for port 5432
  3. User name: Postgres
  4. Password: Password for the Postgres user

Provide connection informations

Click on Connect, and you can see an active connection in the object explorer. We can also see a database Postgres in this window:

Default database dashboard

Now, right-click on the PostgreSQL instance and select New Query:

New query window

It opens a connection to the Postgres database:

Change connection

Let’s create a new database SQLShackDemo using the Create database command. This command is similar to creating a database using T-SQL in Microsoft SQL Server. Click on Run to execute the query in the toolbar:

Create a new database

Now, we can see this database in the drop-down list. Switch to this new database, as shown below:

Switch it to use database

Once you create a user database, you can expand the server in object explorer and view user database, system databases, roles, and tablespaces:

View tablespace and system database

We can see two system databases template0 and template1. We also have pg_default and pg_global tablespaces.

Create tables in the PostgreSQL using Azure Data Studio extension

We can use SQL IntelliSense codes for creating tables for PostgreSQL as well. It helps you create a table with suggestions as you write the query.

Table with primary key constraint

The following query creates a Products table with Productcode as a primary key. We use primary key hint in the table definition for it:

Table with primary key constraint

Table with a 2-dimensional array

We can define a two-dimensional array in Postgre SQL using two square brackets. In this article, we will not cover the usage of the two-dimensional array.

Execute the following query:

Table with a 2-dimensional array

Table with check constraints

We use check constraints for checking the valid values. For example, the following query uses a check constraint on the productid column:

Table with check constraints

Unique constraints in PostgreSQL using Azure Data Studio

We can define a unique constraint similar to SQL Server in PostgreSQL as well. It helps us in maintaining unique values in a column:

Unique constraints in PostgreSQL

Add a column into an existing table

Many times, we want to add a column into an existing table. We can use Alter table command similar to SQL Server in PostgreSQL also.

The following command adds a ProductCategory column in the Productcode table of PostgreSQL:

Add a column into an existing table

Drop a column from an existing table

We should remove unnecessary columns from a table. It helps to reduce space as well as increase performance as well.

The following query drops a column that we added in the previous section:


Drop a column from an existing table

Rename a column in the existing table

We should always name the columns to identify them quickly. Sometimes, we do not know the requirement of a column and create the column with a generic name. We can use the alter table..rename column statement for renaming a column.

The following command renames the column ProductName to ProdName:

Rename a column in the existing table

Get a list of databases in PostgreSQL using Azure Data Studio

We use the pg_database catalog to get a list of an available database in PostgreSQL instance:

This command lists all system and user databases available in the PostgreSQL instance:

  • System database: template1 and template0
  • User database: Postgres and sqlshackdemo

Get a list of a database

Get a list of tables in PostgreSQL

Azure Data Studio provides IntelliSense query support. You get appropriate suggestions based on the words you type in the query.

In the following screenshot, we can see query suggestions as we write the texts in the query editor:

Get a list of tables

You can select the appropriate options and enter to use it in the query. In the following query, we can see all tables for SQLShacDemo tables using the pg_catalog.pg_tables system catalog:

Get a list of tables and table owner details

We can filter the results for specific schema using the schemaname column:

filter the results for specific schema

Conclusion

In this article, we explored PostgreSQL on the Ubuntu OS. We further connected and performed queries in the PostgreSQL database using Azure Data Studio.

Rajendra Gupta
2,315 Views