SQL 101 (6 Part Series)
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.
PgAdmin is browser-based, which is a bit of a change from the application based SQL Server 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.
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:
When using a WHERE clause there is the option of using ILIKE which allows you to match without the case sensitivity.
select * from actor_registration where firstname ilike '%jen%'
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.
select * concat ( lower(first_name), lower(last_name) ) as full_name from actor_registration;
There are some other differences in syntax that mean you may need to refactor a few things. Some changes are small like SELECT TOP 10 * is replaced by adding LIMIT 10 to the end of your query
select top 10 * from actor_registration; select * from actor_registration limit 10;
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'.
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.
When uploading a csv via the UI you will need to create a table first and then use the wizard to import. This is different from the wizard on SSMS that allowed you to load in the file and create the table for you. To then import the data:
Right-click the table > Import/Export > follow the prompts
Make sure you remember to select IMPORT from the default of EXPORT of you may find that the empty table you have just created overwrites your csv.
In SSMS, Stored Procedures are blocks of code that you can run on a schedule to create tables, export csvs or trigger other blocks of code. 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.
What have I missed? What should I be looking out for as I migrate and refactor? I'd love to hear your top tips.
This post first appeared on helenanderson.co.n
Photo by Ylanite Koppens on Pexels