Use PSQL to List Schemas of a Postgres Database on an ObjectRocket instance

Introduction

A schema in PostgreSQL is a namespace that holds named database objects. Various situations may exists where schemas can be helpful, such as organizing database objects for enhanced efficiency and enabling multiple users to access the same database without creating conflicts. When accessing a database object of a schema, the name must be qualified with the schema name as a prefix or a search path that includes the schema must be set up. A PostgreSQL database may contain just a single schema have or multiple schemas, however, each schema can only exist in one database. Additionally, two separate schemas can contain different objects that share the same name. This tutorial will explain how to use PSQL to list schemas of a Postgres database on an ObjectRocket instance.

Prerequisites

• PostgreSQL must be correctly installed and working on the local system. The software is available for downloading at: PostgreSQL 11

• A valid instance of Postgres for an active ObjectRocket’s account must be established in the Mission Control panel, shown below, to use PSQL to list schemas of a Postgres database on an ObjectRocket instance.

-img src=”https://t.gyazo.com/teams/500rockets/23354605b337d6a30fd28e6bac855ad6.png” width=”632″ height=”456″-

What Is a PostgreSQL Schema

A schema in Postgres represents a namespace that contains objects in the current database, These objects include, but are not necessarily limited to, data types, functions, indexes, operators, tables and views.

A Postgres database can have one or multiple schemas, and schemas can have various objects with the same name, but a schema can only be defined to a single database.

How to Connect to PostgreSQL Instance

A connection can be established to the PostgreSQL server on ObjectRocket instance using the two methods described below.

Connect via Psql Utility

A connection can be set up using the PSQL utility. This comes included with the PostgreSQL software installation and is shown here:

Connect via Command Line

The second way of establishing a connection to the PostgreSQL server on an ObjectRocket instance is by executing the following command through the terminal:

Listing Schemas in PostgreSQL

With a connection established to the Postgres instance, list the schema via the SELECT statement as shown in the following three examples:

Example 1: The fist method for listing the schema in PostgreSQL uses the following command:

Note that the above syntax selects the schema within the pg_namespace that stores the namespaces that contain the structure of the schema.

Example 2: The second method for listing the schema in PostgreSQL is demonstrated here:

Following is a breakdown of the syntax used in the second method:

• The table_schema is listed as the name of the schema.
• The schema_id is the unique ID of the schema within the database.
• The schema_owner is the name of the owner assigned to the schema.

The output for the second schema listing method should resemble this:

Example 3: The last method for listing the schema in this tutorial uses the psql built-in command, \dn and will display all of the schemas contained in the connected database:

Conclusion

This tutorial explained how to use PSQL to list the schemas in a Postgres database on an ObjectRocket instance. The tutorial first provided an explanation of what a PostgreSQL schema is and how it functions. It then explained two ways of connecting to PostgreSQL instance, one via the PSQL utility and a second via the command line function. The article then explained and provided examples for three different ways of listing a schema in PostgreSQL with a of breakdown of the syntax used for the commands. Bear in mind that a Postgres database can have one or multiple schemas, and schemas can have various objects with the same name, but a schema can only be defined to a single database.