In this article, we will learn how to query the data as well as database objects hosted in an instance of Azure Database for PostgreSQL using psql and export the results of the query to a file.
Introduction
Azure Database for PostgreSQL is Azure’s offering of PostgreSQL database on Azure cloud. pgAdmin and psql are two of the most common tools that are used to administer and develop database objects on postgreSQL. Querying data and extracting for a local snapshot of the data for use outside the database is one of the most common use cases while working with database objects.
Creating an instance of Azure Database for PostgreSQL
It is assumed that an instance of Azure Database for PostgreSQL is already in place. It is also assumed that the pgAdmin tool is already installed and psql is already invoked using pgAdmin. We covered this subject in the previous article, so those who are new to these tools can refer to the last article to set this up before proceeding with the following exercise.
When we work with the pgAdmin tool, the browser pane would list several objects categories related to postgreSQL. While working with the psql tool, which is a terminal-based tool, we would invoke commands to explore and inspect these database objects. So, before we proceed with the exercise, let’s quickly understand what these objects mean in a concise manner. Below mentioned is a list of database objects categories with a short description of the same.
- Schemas – An Azure Database for PostgreSQL can contain one or multiple schemas, which are logical containers to organize any possible database objects that are explained in the list below. Schemas cannot be nested and database objects across schemas can access each other.
- Aggregates – This section allows to list or search database objects that use aggregate functions.
- Collations – This refers to the list of collations used in the database. Collations are generally used to control the behavior of sort order used by different objects in the database.
- Domains – A domain in postgreSQL can be considered as a custom data type that is formed of basic or primitive data types optionally with constraints attached to it.
- FTS – There are a set of Full Test Search (FTS) related groups like FTS configuration, dictionary, parsers, and templates available in the pgAdmin browser pane. All of these relate to different aspects of Full-Text Search.
- Foreign Tables – These are basically datasets or tables that are present in an external data source that is linked with the instance of postgreSQL. While this works normally in postgreSQL, it may have a few limitations while working with an instance of Azure Database for Postgresql which is a managed service in Azure.
- Functions – Functions mean the same thing in postgreSQL as with any other database. It can have built-in or user-defined functions.
- Materialized Views – For performance reasons, where data access from large tables is not optimal in terms of data processing, the final dataset that is resulting from a variety of tables post-processing, is loaded into a physical table which is known as a materialized view.
- Operators – Operators also mean the same thing as what it means in other databases as well.
- Procedures – Procedures are compiled blocks of logic that are formed of SQL statements to process the data as per the requirements of the business function. These are equivalent to stored procedures in SQL Server.
- Sequences – As the name is self-relevant, sequences generally create auto-incrementing numbers based on a specified criterion.
- Tables – Tables are one of the fundamental blocks of the postgreSQL database and it means the same thing as with other databases.
- Triggers – Triggers are actions that are invoked when a specific table action happens like insert, update, or deletion of records. This is at par with triggers in other databases.
- Types – This section lists the composite types which can be thought of as enumerations or structures or arrays.
- Views – Views provide a level of abstraction from the actual tables which may hold a superset of information than required by the end consumer.
Now that we understand the significance of these database object categories, we can get started with the actual exercise of exploring these database objects and data in them. It is assumed that the psql tool is already open and connected to an instance of Azure Database for PostgreSQL. Tables are one of the primary database objects that almost everyone works with. To list the tables in a database hosted on an instance of Azure Database for PostgreSQL, we can just issue a simple command “\dt” to list all the tables, and the result will look as shown below. Here we can see the name and type of the database object as well as the owner of the same.
Let’s say that we intend to explore all the views in the database instance, as the next logical database object in the category of tables and views. We can issue a command for the same – “\dv” where v stands for view, and the result would look as shown below.
Using the same convention, we can query other database objects like materialized views, indexes, and schemas as well. To list all the indexes, use the command “\di”. To list all the materialized views, use the command “\dm”. And to list all the schemas, use the command “\dn” and the results would look as shown below.
Let’s say that we intend to list a specific table with the exact name or a matching pattern, we can specify the same as well using the standard command for listing tables. As shown below, the command “\dt Employee” will list the employee table only, as this command is using table name as the parameter and filter the resultset based on the parameter.
Before we query the table, we may want to know the schema of the table in question. In that case, we can issue the command “\d Employee”. Here we are asking to explore the schema of the database object which is passed as a parameter to the “\d” command i.e., “Employee”. By default, it would be assumed that that table is in the public schema. If the database object is found, the schema results would be returned in output as shown below. Here we can see the fields, data types, collation, nullable, default values, indexes, and constraints associated with the table.
In case, we intend to explore all the database objects of interest, we can just issue the command “\dS” to list all the objects as shown below.
Now let’s query the table to view the data. Here we already have a small table with a few fields and records in it. We can type the SQL query directly on the psql console, and the result would look as shown below. Keep in view to add a semi-colon after the query. As we can see, the psql console acts as a query terminal for querying the data out of the database instance of Azure Database for PostgreSQL.
Now that we know how to explore the database objects and query the data using the psql tool, now it’s time to export the results of the query to a file locally. We can use the SQL query as it is and append the “\g” argument and specify the file path where we want to result to be exported. In this case, the output of the query would be stored in the file instead of being shown on the terminal window.
Navigate to the file path and open the file, and the results would look as shown below.
In this way, we can use the psql terminal to list database objects, query the data from an instance of Azure Database for PostgreSQL as well as export the results of the query to a local file.
Conclusion
In this article, we learned how to use the psql tool as a query terminal for issuing various queries to explore the database objects in the database instance as well as issue DDL and DML queries on the database instance.
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023
- Overview of the SQL Median function - January 4, 2023