I’ve been doing a bit of work with PostgreSQL as part of my work with Redgate. PostgreSQL is a relational platform that is open source, free to use, available as a supported commercial product from various companies, and has been in active development for over 35 years.
More and more organizations are looking at PostgreSQL for relational data stores as both SQL Server and Oracle are very expensive, and this is a viable alternative.
This post looks at the basics of getting started with PostgreSQL by connecting to the platform with Azure Data Studio.
If you haven’t installed PostgreSQL, or want to work with a container, check out my previous post: Creating a PostgreSQL Docker Container with a Volume on Windows
Azure Data Studio
Azure Data Studio (ADS) is a fork of Visual Studio Code, but specialized for databases. I don’t use it a lot, but I find it useful for some coding. They also have added an extension that allows connections to PostgreSQL.
If you click the Extensions blade and then search for postgresql, you’ll find it. You can see it installed on my system below. Before you do this, the button that says uninstall would say install.
Once installed, the connection is like any connection in ADS.
First, make sure you have PostgreSQL installed. If you want to do this in a container, see my link above.
Connecting
To connect, first open a new Query window.
At the top there is a “Connect” button. Click this.
Now the connection blade opens. As you can see below, there are now two choices for the connection type: SQL Server and PostgreSQL. Choose PostgreSQL.
I choose localhost, as that’s where my instance is running. I also change the authentication type to Login and enter “postgres” as the default user. If you haven’t set up a user, use this. Then enter the password.
One last thing, as I often run databases in containers, I’ll change the port. To do that for PostgreSQL, click the “Advanced” button. I often use 54320, 54321, 54322 for my ports, so you can see I’ve entered that below:
Click Ok if you’re added a port, and you should see these details:
Click Connect, and you should connect immediately. Then you can verify your connection with a query or two. As you can see, the database is defaulted to postgres and my queries have returned the version and tables in this database.
That’s it. Now time to work on some pgsql.