SQLServerCentral Article

PostgreSQL in Azure Data Studio

,

Azure Data Studio (ADS) comes with an extension to access a PostgreSQL database. In this article, we will talk about Postgres, install the Windows version On-premises and then connect with ADS and create some objects like a table, view, and select some data.

About Postgres

This database is one of the top 10 most popular databases in the world. PostgreSQL was developed at the University of California and the name was Postgres as a successor of the Ingress database. And then they changed to PostgreSQL to reflect that the database supports SQL. The database was released in 1996, so it is a lot of time in the market. Safe and secure. It is an open-sourced relational database.

The Elephant logo is named Slonik. It is inspired by the book, Elephants Can Remember, by Agatha Christie.

Installing Postgres

You can download the installer from this link: Download PostgreSQL

First, run the Windows installer and install it on a machine. You will have the Welcome message first. Press Next.

welcome postgresql PostgreSQL installation welcome

Secondly, you can select the directory where Postgres will be installed or use the default values.

Installation directory used directory to install

Also, you need to install the components like the server and pgAdmin. PgAdmin is like SSMS. The Stack Builder is the graphical interface to download and configure modules.

PostgreSQL Components Components to install

In addition, provide a password. The superuser is Postgres. Write a password and a retype password and do not forget it. It will be used to connect to PostgreSQL later.

password and retype password Postgres password

Select the port used by Postgres. The default is 5432.

Port used during Postgres installation PostgreSQL port

In addition, we need to select the locale used by the new database cluster. This is used to set the code page.

Local for new database cluster installation advanced options

Also, after selecting all the options a summary will be displayed.

postgresql installation summary pre-installation summary

You will then have a Welcome to Stack Builder. Select the installation of your PostgreSQL in the Combobox.

Wizard for Stack builder Stack builder installer

Also, select the applications to install.

select components postgresql Applications to install

Finally, press next to finish the next options and write the Postgres password and login when necessary.

Working with PostgreSQL in Azure Data Studio

Azure Data Studio, now includes a new extension to connect to PostgreSQL. If you like Azure Data Studio and you also have a PostgreSQL database, it could be a great idea to use the extension. In Azure Data Studio, go to extensions and look for the PostgreSQL extension.

Add extension to support Postgres Install PostgreSQL extension

Secondly, press the add icon to add the Postgres Server.

Thirdly, in Connection type, select PostgreSQL. Write the name of the Server name. In this example, the server name is the localhost. The user name is postgres and the password is the one used during the installation.

If everything is fine, in the database name, you will be able to see the Postgres database. This database is the default database installed by default. Finally, press the Connect button.

Connect to Postgres in Azure Data Studio PostgreSQL in Azure Data Studio connection

Create Objects with Azure Data Studio

First, let's create a new table. Right-click the database and select new query.

create a new query in ADS

Postgres new query

Additionally, write the following query to create a table and execute it.

CREATE TABLE customer (
   id integer,
    firstname       varchar(40),
    lasttname       varchar(40),
    email           varchar(40)
   
);

Also, if you cannot see the new table right-click the Tables node and select the Refresh option.

Refresh table Refresh data

Thirdly, you can insert, delete or update data using the Edit Data with the right click on the table.

Edit the data of the Postgres table Edit table data of PostgreSQL in Azure Data Studio

You can write new rows, edit or delete them.

Edit the data with the ADS UI postgresql in Azure Data Studio New data to edit PostgreSQL in Azure Data Studio

Also, you can insert data using the traditional T-SQL

insert into customer values(1,'John','Rambo','jrambo@outlook.com')

The Select Top 1000 option allows doing a select to the data table.

Select data in Postgresql view Postgresql in Azure Data Studio select top 1000 rows

In addition, we will create a view based on the customer table.

CREATE VIEW vcustomer AS
    SELECT email
    FROM customer

Also, you can select the view created and right-click and select the Script as Create option to generate the T-SQL script. It will generate the CREATE and the ALTER T-SQL code.

postgresql script to create script created

In addition, you can also generate the DROP T-SQL code by right-clicking the view and selecting the Script as Drop option.

select postgres database Select database

Finally, you can Select Top 1000 rows of the view by right-clicking on it and selecting that option.

postgresql in Azure Data Studio view view select

Conclusion

To conclude, we learned how to install the Windows version of Postgres and then how to connect to it using Azure Data Studio. Finally, we learned how to create a table, a view, and how to select the data.

Rate

5 (2)

Share

Share

Rate

5 (2)