For the last two years, I’ve been happily working away in Microsoft SQL Server Management Studio (SSMS).

However, this month we are moving to a shiny new AWS Aurora database with pgAdmin as the UI.

Now that I’ve had a few weeks to tinker around and get used to its quirks, here are my top 6 things to know about pgAdmin if you are an analyst migrating from SSMS.


1 – Keep it in Chrome<
2 – Case sensitive
3 – Syntax changes
4 – Autocomplete
5 – Right-click for the wizard
6 – Functions are the new procs
Additional Resources

hr />

1 – 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.


2 – 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 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.


3 – Syntax changes

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

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

There are tools to help make this easier. 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’.


4 – Autocomplete

In SSMS 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.


5 – Right-click for the wizard

When uploading a CSV via the UI you will need to create a table first, then use the wizard to import. This is different from the wizard on SSMS that allowed you to load the file and created the table for you. To then import data:

Right-click the table > Import/Export > follow the prompts

 

 

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.


6 – Functions are the new procs

In SSMS, 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 an 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

 


Photo by Ylanite Koppens on Pexels

This post first appeared on dev.to