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:
First, run the Windows installer and install it on a machine. You will have the Welcome message first. Press Next.
PostgreSQL installation welcome
Secondly, you can select the directory where Postgres will be installed or use the default values.
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.
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.
Select the port used by Postgres. The default is 5432.
In addition, we need to select the locale used by the new database cluster. This is used to set the code page.
installation advanced options
Also, after selecting all the options a summary will be displayed.
You will then have a Welcome to Stack Builder. Select the installation of your PostgreSQL in the Combobox.
Stack builder installer
Also, select the applications to install.
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. Install PostgreSQL extension
Secondly, press the add icon to add the Postgres Server.
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. 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. Postgres new query
Additionally, write the following query to create a table and execute it.
CREATE TABLE customer (
Also, if you cannot see the new table right-click the Tables node and select the
Refresh option. Refresh data
Thirdly, you can insert, delete or update data using the
Edit Data with the right click on the table. Edit table data of PostgreSQL in Azure Data Studio
You can write new rows, edit or delete them.
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','firstname.lastname@example.org')
Select Top 1000 option allows doing a select to the data table. 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
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. 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 database
Finally, you can
Select Top 1000 rows of the view by right-clicking on it and selecting that option. 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.