Watch the video, or load up psql and follow along below!
Connecting to the demo cluster with psql
You can use any recent version of psql to connect to EDB Postgres Advanced Server. If you choose to use the version that ships with Advanced Server, you'll get a few nice SQL*Plus compatibility features (with more availability in EDB*Plus). The queries and commands that we'll examine here will work the same in either version of psql. For convenience, these examples use the version of psql available in Azure's Cloud Shell; you can launch this directly from the Azure portal, or on your desktop using Windows Terminal:
The connection string for the demo Advanced Server cluster looks like this:
demo is the user role we're connecting as. This is a user set up with select privileges on the database.
password is the password for this user.
Passwords in connection strings.
This example illustrates a complete connection URL, including the password. This is fine for a demonstration,
and may also be acceptable for applications configuration if access to the configuration is limited.
Avoid this practice for admin, superuser, or other roles used interactively - psql will prompt for a password
if none is supplied.
p-c64p9a3h5vfavr7tfrjg.qsbilba3hlgp1vqr.biganimal.io is the host name for the Advanced Server cluster on BigAnimal that I'm connecting to.
5432 is the usual PostgreSQL port number.
chinook is the name of the database.
sslmode=require ensures that we establish a secure connection.
With that in hand, we can launch psql:
Let's take a look at the schema:
There's an employee table, let's examine its definition:
This table has a "reportsto" field - that means this is a hierarchical reporting structure, with some employees reporting to
other employees who may in turn report to still other employees.
Demo #1: exposing an organization hierarchy with CONNECT BY
Modern SQL would use a recursive CTE for this, as those are widely supported. But Oracle has, for decades, supported an alternative mechanism for querying hierarchy in the form of CONNECT BY - let's put that into action:
Here, we use CONNECT BY and the LISTAGG function in a subquery to generate the chain of command for each employee: who they report to, who that person reports to, etc.
Now, the LISTAGG() function was introduced in Oracle 11g Release 2. Very few database systems support it. PostgreSQL does support string_agg(), and in the previous example that could be used as a drop-in replacement...
As we saw above, this database has "album" and "track" tables containing metadata on digital recordings. We can use some analytic functions, including LISTAGG, to put together a report on average track storage requirements for albums with "baby" in the title.
Now, this isn't terribly difficult to correct, but it requires restructuring the query to replace the grouping construct - such work can quickly accumulate errors. Fortunately, EDB Postgres Advanced Server
supports LISTAGG AND string_agg,
so this query doesn't need to change when migrating from Oracle.
Compatibility preserves the value of your existing work
In both of the examples shown here, you probably would not use the functions and syntax demonstrated for new work; there are
better, more familiar or at least more widely-available equivalents provided natively by PostgreSQL (and many other databases). But by supporting them, EDB Advanced Server gives you the ability to reuse existing logic with minimal modification, allowing
you to focus your time and expertise on solving new problems.