How to Perform the Psql to use the Schema in PostgreSQL

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

Database clusters typically contain one or more named databases with multiple user sharing access. A schema is a table collection in PostgreSQL that contains specific database objects. These objects can include tables, indexes, sequences, various data types, functions and operators. A search path can be set that includes the schema designed to only access the data specified in the connection request. This tutorial will explains the Psql use schema function and how to execute it to control what can be searched for and what data is returned.

Prerequisites

  • PostgreSQL must be properly installed and configured with access to the command-line interface to use the Psql use schema function

  • A basic working knowledge of database management systems is required to execute the examples discussed in this tutorial.

What is PostgreSQL schema

Schemas are similar to directories at the operating system level, but schemas cannot be nested. The CREATE SCHEMA statement is used in PostgreSQL to create a schema.

Following is the basic command for creating a SCHEMA:

1
CREATE SCHEMA schema_name;

Note: The schema_name is the same as the created schema, so users can name the schema anything they choose.

Using the PostgreSQL Schema

Databases, by nature, typically contain a large number of tables and views. Running each one individually can quickly become both cumbersome and complex. To get around this issue, PostgreSQL came up with the concept of a schema.

Create database in PostgreSQL

Before demonstrating how a schema works, a database must be created. Execute the following CREATE DATABASE statement to create the PostgreSQL database,:

1
CREATE DATABASE mydb;

Note in the above statement thatmydb is the name of the newly created database.

Now execute the \c command followed by the database name to connect to the new database.

Create a schema table in PostgreSQL

The CREATE SCHEMA statement is used to create a schema in PostgreSQL.

Following is the syntax for creating a PostgreSQL schema:

1
CREATE SCHEMA schema_name;

Here is an example using the mydb database from the previous section:

1
2
mydb=# CREATE SCHEMA my_schema;
CREATE SCHEMA

The above CREATE SCHEMA statement, that appears after the syntax, indicates that the schema was successfully created.

Now create a PostgreSQL schema table using the following syntax:

1
2
3
4
CREATE TABLE my_schema.table_name
(COLUMN NAME + DATA TYPE +
COLUMN CONSTRAINT [OPTIONAL])
;

Following is an example of the newly created schema table:

1
2
3
4
5
CREATE TABLE my_schema.employee(id INT PRIMARY KEY,
name VARCHAR(20),
address VARCHAR(10),
salary REAL)
;

Now use the following SELECT STATEMENT clause to verify the above command was successfully executed:

1
SELECT * FROM my_schema.employee;

The results should look like this:

1
2
3
id | name | address | salary
----+------+---------+--------
(0 ROWS)

Note the results correctly show an empty table.

Drop the PostgreSQL schema

Only the database owner or superuser can drop, or delete, a schema. It is important to note that the owner can drop the schema, with all its included objects, even if the database owner does not own some of the objects contained in the schema.

Execute the following DROP SCHEMA statement to drop the schema:

1
DROP SCHEMA my_schema;

Note: The DROP SCHEMA statement can be used to drop a schema even if it is empty.

To drop the schema with all it contents, execute the following command:

1
2
3
mydb=# DROP SCHEMA my_schema CASCADE;
NOTICE: DROP cascades TO TABLE my_schema.employee
DROP SCHEMA

The DROP SCHEMA statement appearing directly below the syntax indicates the schema table was successfully dropped.

Why do you need to use PostgreSQL schema

  • A SCHEMA helps to manage databases and organized its contents into relevant groups.
  • Enables different individual users to work with the same database without impeding each other.
  • A third party can be restricted to only have access to certain schemas. This will prevent users from accessing objects they are not authorized to.

Conclusion

This tutorial explained the Psql use schema function and how to execute it in PostgreSQL. The tutorial also covered how to create a database and a schema table for that database. The article then covered how to drop the PostgreSQL schema and explained the various reasons for using a PostgreSQL schema. Remember that only the database owner or superuser can drop a schema and all its objects, even though the owner may not own all of the objects contained in the schema. It is also important to remember that while schemas are similar to directories, at the operating system level, schemas cannot be nested.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.