Schema

What is Schema?

Have you ever wondered how to work with your fellows in one database without interfering with each other? Is it possible to organize the database objects into logical groups which do not collide with the other objects’ names?

We can do those things with Schema.

A schema is a collection of tables. A schema also contains views, indexes, sequences, data types, operators, and functions. We support multiple schemas. For example, you can have a database named oxla and have multiple schemas based on your needs, like auth, model, business, etc.

Default Schema in Oxla

By default, the public schema is used in Oxla.

When unqualified table_name is used, that table_name is equivalent to public.table_name. It also applies to CREATE, DROP, and SELECT TABLE statements.

Furthermore, you can create multiple schemas per your needs.

Schema Usage Scenarios

#1 - Create a Schema

The basic syntax of creating a schema is as follows:

CREATE SCHEMA [IF NOT EXISTS] schema_name;

• schema_name is the schema name you are going to create.
• IF NOT EXISTS is an optional parameter to avoid errors if the schema already exists.

#2 - Create a Table in Schema

The syntax to create a table in a specified schema is as follows:

CREATE TABLE schema_name.table_name(
...
);

• schema_name is the schema that you have created.

• table_name is the table name you are going to create.

#3 - Select a Table in Schema

After creating the table and inserting some data, display all rows with the syntax below:

SELECT * FROM schema_name.table_name;

• schema_name is the name of the schema.

• table_name is the name of the table you want to display.

#4 - Drop the Schema

Option 1: To drop an empty schema where no objects remain in it, use the command below:

DROP SCHEMA [IF EXISTS] schema_name;

• schema_name is the schema name you are going to create.
• IF EXISTS is an optional parameter to avoid errors if the schema does not exist.

Option 2: Tables reside in a schema, so it is impossible to drop a schema without also dropping the tables. With the command below, you will also drop the schema with the tables.

DROP SCHEMA schema_name CASCADE;

Examples

Example #1: Creating Schema

1. First, connect to Oxla and create a schema as shown below:

CREATE SCHEMA oxlarefs;

2. Next, create a table in the above schema with the following details:

CREATE TABLE oxlarefs.functions(
  id int,
  function_name string,
  active bool
);

INSERT INTO oxlarefs.functions(id, function_name, active)
VALUES 
('1111', 'Numeric', 'TRUE'),
('2222', 'String', 'TRUE'),
('3333', 'Timestamp', 'TRUE'),
('4444', 'JSON', 'TRUE'),
('5555', 'Boolean', 'TRUE');

3. You can verify and show the table made with the command below:

SELECT * FROM oxlarefs.functions;

4. You will get the following result:

+------+---------------+---------+
| id   | function_name | active  |
+------+---------------+---------+
| 1111 | Numeric       | t       |
| 2222 | String        | t       |
| 3333 | Timestamp     | t       |
| 4444 | JSON          | t       |
| 5555 | Boolean       | t       |
+------+---------------+---------+

Example #2: Creating Schema Using IF NOT EXISTS

To avoid errors when the schema already exists, use the IF NOT EXISTS option. Here is how it works:

Example without IF NOT EXISTS

1. First, create the schema without using the IF NOT EXISTS option.

CREATE SCHEMA oxladb;

Output:

CREATE SCHEMA

2. If you attempt to create the schema again without using IF NOT EXISTS, it will result in an error.

CREATE SCHEMA oxladb;

Output:

ERROR:  Schema: oxladb already exists

Example with IF NOT EXISTS

Now, create the schema using the IF NOT EXISTS option to avoid the error.

CREATE SCHEMA IF NOT EXISTS oxladb;

Using IF NOT EXISTS allows the query to create a schema even if it already exists.

CREATE

Example #3: Dropping Schema

Use the command below to delete the schema and also the tables in it.

DROP SCHEMA oxlarefs CASCADE;

Another case is if there is no table or object created inside the schema, you can use the following command to drop the schema.

DROP SCHEMA oxlarefs;

Example #4: Dropping Schema using IF EXISTS

Example without IF EXISTS

1. First, drop the schema without using the IF EXISTS option.

DROP SCHEMA oxladb;

Output:

DROP

2. If you attempt to drop the schema again without using IF EXISTS, it will result in an error.

DROP SCHEMA oxladb;

Output:

ERROR:  schema "oxladb" does not exist

Example with IF EXISTS

Now, drop the schema using the IF EXISTS option.

DROP SCHEMA IF EXISTS oxladb;

Using IF EXISTS allows the query to succeed even if the schema does not exist.

DROP