How to Create Schema in PostgreSQL

Joy Idialu Feb 02, 2024
  1. Use the CREATE SCHEMA Statement in an SQL Query to Create a Schema
  2. Use the CREATE SCHEMA Statement in PSQL to Create a Schema
How to Create Schema in PostgreSQL

This tutorial will discuss creating a schema in PostgreSQL using the SQL query or psql.

Use the CREATE SCHEMA Statement in an SQL Query to Create a Schema

To create a new schema, execute the command below.

CREATE SCHEMA test_schema

To view all schema names in the database, run this query:

SELECT schema_name
FROM information_schema.schemata

You can also view all schemas in the database using this query:

SELECT *
FROM pg_catalog.pg_namespace
ORDER BY nspname;

Use the CREATE SCHEMA Statement in PSQL to Create a Schema

With psql, you can carry out more actions such as creating a database, connecting to the database, and finally creating a schema as shown here:

CREATE DATABASE test_db;
\connect test_db;
CREATE SCHEMA test_schema;

To list all schemas in a database using psql, run this command:

\dn

Output:

|    Name     | Owner |
--------------|--------
| public      | user1 |
| test_schema | user1 |