PgAdmin Guide for SQL Server users


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.


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


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


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

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


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

 


Photo by Ylanite Koppens on Pexels

 

# # #

January 29, 2019

Bitnami