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:
1 2 3 4 5 | Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud DATABASE [PostgreSQL]: <your_database_name> Port [5432]: 4149 Username [PostgreSQL]: pguser Password FOR USER orkb: <your_password> |
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:
1 | psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U pguser -d PostgreSQL -p 4149 |
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:
1 2 3 4 5 6 7 8 9 10 | sandbox=> SELECT *FROM pg_namespace; nspname | nspowner | nspacl --------------------+----------+------------------------------------- pg_toast | 10 | pg_temp_1 | 10 | pg_toast_temp_1 | 10 | pg_catalog | 10 | {PostgreSQL=UC/PostgreSQL,=U/PostgreSQL} public | 10 | {PostgreSQL=UC/PostgreSQL,=UC/PostgreSQL} information_schema | 10 | {PostgreSQL=UC/PostgreSQL,=U/PostgreSQL} (6 ROWS) |
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:
1 2 3 4 5 6 | SELECT s.nspname AS table_schema, s.oid AS schema_id, u.usename AS schema_owner FROM pg_catalog.pg_namespace s JOIN pg_catalog.pg_user u ON u.usesysid = s.nspowner ORDER BY table_schema; |
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:
1 2 3 4 5 6 7 8 9 | table_schema | schema_id | schema_owner --------------------+-----------+-------------- information_schema | 12771 | PostgreSQL pg_catalog | 11 | PostgreSQL pg_temp_1 | 11804 | PostgreSQL pg_toast | 99 | PostgreSQL pg_toast_temp_1 | 11805 | PostgreSQL public | 2200 | PostgreSQL (6 rows) |
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:
1 2 3 4 5 6 | PostgreSQL=# \dn List OF schemas Name | Owner --------+---------- public | PostgreSQL (1 ROW) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started