PostgreSQL Create Schema

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.

 

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!

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!