CREATE TABLE in a Schema Postgres

PostgreSQL is one of the most popular object-relational database systems. It’s free and open-source software that extends the SQL language with additional features to handle the complex data workloads. It can work with various data types, reliable and secure. Learn more about the features of PostgreSQL.

In this guide, we will learn more about creating tables in a schema in PostgreSQL.

Schemas in PostgreSQL

A PostgreSQL database may contain one or more named schemas, each schema containing tables.

The same object name can be assigned across multiple schemas without conflict. For example, the following schema/table tree is valid:

• schema_a
  • table_1
  • table_2
• schema_b
  • table_1
  • table_2

You can think of schemas like directories at the operating system level. The difference is, there can’t be nested schemas. Learn more in-depth about schema on PostgreSQL documentation.

There can be multiple reasons to implement the schemas:

• Multiple users using the same database without conflicting with each other.
• Better organization and management of databases into logical groups.
• Third-party apps can create their unique schema without colliding with the existing schemas or other objects.

Tables in PostgreSQL

Any relational database consists of multiple related tables, each consisting of rows and columns. PostgreSQL comes with several built-in tables that contain various system info. However, we can also create new tables under user-defined databases and schemas.

Pre-requisites:

To perform the steps demonstrated in this guide, you will need the following components:

• A properly-configured Linux system. Learn more about installing Ubuntu in VirtualBox.
• A proper installation of PostgreSQL. Check out installing PostgreSQL on Ubuntu.
• Access to a PostgreSQL user with USAGE permission to a database.

For the purpose of this guide, we will use postgres to perform all the actions in PostgreSQL.

Creating Tables in a Schema

Creating a Demo Database

As mentioned earlier, schemas exist under a database. For demonstration purposes, we create a dummy database, avoiding disruption to any existing database.

Access the PostgreSQL shell as postgres:

Create a new database demo_db:

Verify if the database is created successfully:

Finally, connect to the newly-created database:

The Public Schema

Any new database in PostgreSQL comes with a default schema – public. If you try to create an object without specifying the schema name, the public schema is selected by default.

The following command prints all the available schema in the PostgreSQL database:

Alternatively, we can also use the following SQL query:

Creating a New Schema

To create a new schema under a specific database, the command structure is as follows:

Following the rule, let’s create a new schema demo_schema:

Check the schema list for verification:

Creating a Table in a Schema

Now that we have the target schema created, we can populate it with tables.

The syntax for creating a table is as follows:

Here:

• schema: This field specifies the name of the schema where the table is created. If no value is provided, the table is created under the public schema.

For demonstration, we create a simple table:

Here:

• The field NAME is specified to store a string of 64 characters.
• The field ID contains the integer values. The term “NOT NULL” indicates that ID can’t be empty or null.

We can verify the existence of the table using the following query:

Inserting Data into the Table

With the table in place, we can now insert some values:

Check the content of the table:

Schema Management

Schema Permissions

With the help of schema permission, we can manage what role can perform what action on a specific schema. Out of all the possible privileges, schemas only support CREATE and USAGE.

To update the schema permission for a particular role, the command structure is as follows:

To revoke the schema permission for a particular role, the command structure is as follows:

Verify the change using the following command:

Alter Schema Properties

With the help of the ALTER SCHEMA statement, we can modify the various properties of a schema. For example: ownership, schema name, etc.

To change the schema name, use the following query:

To change the ownership of a schema, use the following query:

Note that to change the ownership, the current user must have the CREATE permission to the schema.

Deleting a Schema

If a schema is no longer necessary, we can delete it using the DROP query:

If the schema contains any object, we need the CASCADE modifier:

Table Management

Table Permissions

Like schema, each table also comes with permission management, defining what action does a role can perform on the table.

To check the permissions of a table, use the following command in psql:

Alter Table Properties

With the help of the ALTER TABLE statement, we can modify the numerous aspects of an already-existing table.

For example, to drop a column, the query looks like this:

To add a new column, we can use the following query:

We can also set the compression for a specific column:

Deleting a Table

To delete a table from a schema, we can use the DROP TABLE query:

Note that unlike schemas, the DROP TABLE query won’t produce an error whether the table is empty or not.

Conclusion

In this guide, we demonstrated how to create the tables in a schema in PostgreSQL. We created a dummy schema within a dummy database and created a table within the schema. This guide also showcased on how to manage the various properties of schemas and tables.

For better data integrity, you can configure a logical PostgreSQL replication. To keep the PostgreSQL database healthy, you can configure the autovacuum to clean the dead tuples that are left by the deleted records.

For more PostgreSQL guides, check out the PostgreSQL sub-category.