Access PostgreSQL databases using the Azure Data Studio extension

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.

Install Oracle VirtualBox on your host and configure an Ubuntu Linux 16.04.5 LTS on it. You can follow the article SQL Server 2019 on Linux with Ubuntu for reference purposes

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

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:

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:

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

1	$ sudo apt-get install postgresql postgresql-contrib

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:

Enter Y and it starts downloading the packages:

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

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:

1	$ sudo -u Postgres psql

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

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’;

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:

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:

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

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

Microsoft SQL Server
PostgreSQL

We need to provide the following inputs:

Connection type: PostgreSQL
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
User name: Postgres
Password: Password for the Postgres user

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

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

It opens a connection to the Postgres database:

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:

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

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

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

1	CREATE TABLE DataArray(Vector INT [][]);

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:

CREATE TABLE Productcode

ProductID INT CHECK(ProductID < 100)

);

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:

CREATE TABLE Productcode

(

ProductID INT,

ProductName Varchar(50) UNIQUE

);

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:

1 2	Alter table Productcode ADD COLUMN ProductCategroy varchar(40);

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:

1	Alter table Productcode DROP COLUMN ProductCategroy

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:

1 2	Alter table Productcode RENAME COLUMN ProductCategroy to ProdName;

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:

1	Select datname from pg_database;

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

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:

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

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.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023