Categories
SQL 101

PgAdmin Guide for SQL Server Users

Things to know about pgAdmin if you are an analyst migrating from SQL Server Management Studio

These are my top six things to know about the quirks of the pgAdmin UI if you are an analyst migrating from SQL Server or another database UI tool.


Keep it in Chrome
Case sensitive
Syntax changes
Autocomplete
Right-click for the wizard
Functions are the new procs
Additional Resources


Keep it in Chrome

PgAdmin is browser-based, which is a bit of a change from the application based environment I’ve become accustomed to.

In my experience, it works best in Chrome and isn’t something that can be bookmarked. Each time you connect, you need to do so from pgAdmin.


Case sensitive

PostgreSQL is case sensitive, unlike T-SQL.  So it pays to check what the data looks like in each column you query when it’s likely to contain characters.

Using the dataset from the post on SQL Indexes as an example:

pgadmin1

When using a WHERE clause there is the option of using ILIKE which allows you to match without the case sensitivity.

You can use UPPER and LOWER to make sure you have a match on the same case in a JOIN or if you want to concatenate two character columns.


Syntax changes

There are some other differences in syntax that mean you may need to do some refactoring when using PgAdmin. Some changes are small like SELECT TOP * is replaced by adding LIMIT 10 to the end of your query.

If you aren’t using AWS or just don’t have permissions to use the Schema Conversion Tool via the console, this free tool is a great alternative at giving the ‘best guess’.

Others require a bit more work, like working with dates which is the biggest change I’ve noticed.


Autocomplete

In SQL Server when IntelliSense is switched on it works for all objects and doesn’t need any extra keystrokes. In pgAdmin, there is a keyboard shortcut that you can use on the fly to auto-complete your object names.


Right-click for the wizard

When uploading a CSV via the pgAdmin UI you will need to create a table first, then use the wizard to import.

Make sure you remember to select IMPORT from the default of EXPORT or you may find the empty table you have just created overwrites your CSV.

This is different from the wizard on SQL Server that allowed you to load the file and created the table for you. To then import data:

Right-click table > Import/Export > follow prompts


Functions are the new stored procedures

In SQL Server, Stored Procedures are code blocks you can run on a schedule to create tables, export CSV or trigger other code blocks. In pgAdmin, these are Functions and can be used to do the same thing.

The major difference, other than a little bit of syntax, is that pgAdmin does not have a built-in scheduler like the Job Agent in SSMS. The alternative is to install a pgAdmin Extension called pgAgent that operates the same way.

If you are using AWS Aurora, this is not supported, but alternatives such as Airflow or using Cron jobs are available to you.


Additional Resources

Tool for converting T-SQL to PostgreSQL

Keyboard shortcuts

PGAdmin documentation

Data types

Conditional expressions

Dates and times

Reference guide

Practice exercises

Don’t do this in PostgreSQL


Photo by Ylanite Koppens on Pexels

By Helen Anderson

I’m passionate about technology and building data applications.