PostgreSQL Create Schema

Last updated on September 7th, 2020 at 09:50 am

Many times you may need to create schema in a PostgreSQL database. Here’s how to create schema in a database using PostgreSQL CREATE SCHEMA statement.

Table of Contents

How to Create Schema in PostgreSQL

Here are the steps to create schema in PostgreSQL using CREATE SCHEMA statement. Here’s the syntax of CREATE SCHEMA command

CREATE SCHEMA [IF NOT EXISTS] schema_name;

In the above statement you need to mention the schema name after CREATE SCHEMA. Please ensure schema name is unique in the database.

You can also provide an optional keyword IF NOT EXISTS above to create PostgreSQL schema only if it doesn’t exist.

Here’s an example to create schema in PostgreSQL

# create schema sample;

Bonus Read : PostgreSQL Create Database

Create Schema for User

If you want to create schema only for a specific user use the AUTHORIZATION clause shown below

# CREATE SCHEMA [IF NOT EXISTS] schema_name
AUTHORIZATION username;

For example, here’s the SQL query to create schema named sample for user db_user

# CREATE SCHEMA [IF NOT EXISTS] sample
AUTHORIZATION db_user;

Bonus Read : PostgreSQL Create Table

List all schemas in database

Here’s the SQL query to show all schemas in your database.

postgres=# SELECT *
           FROM pg_catalog.pg_namespace
           ORDER BY nspname;

      nspname       | nspowner |               nspacl
--------------------+----------+-------------------------------------
 information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
 pg_temp_1          |       10 |
 pg_toast           |       10 |
 pg_toast_temp_1    |       10 |
 public             |       10 | {postgres=UC/postgres,=UC/postgres}

Bonus Read : How to Create Histogram in PostgreSQL

Hopefully, now you can easily create PostgreSQL Schema.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

How to Check PostgreSQL version

How to List All Users in PostgreSQL

How to Compare Two Schemas in PostgreSQL