Working with Schemas in Postgres

Intro

Postgresql provides Schemas to organize databases. This allows you to create separation amongst your app and limit access to parts of your instance. In this article, we will learn how to use Schemas in Postgresql.

The Syntax

The basic syntax of a Schema is as follows:

CREATE SCHEMA [IF NOT EXISTS] schema_name;

Getting Setup

We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called docker-compose.yml and add the following.

version: '3'
 
services:
  db:
    image: 'postgres:latest'
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: password
      POSTGRES_DB: default_database
    volumes:
      - psqldata:/var/lib/postgresql

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  psqldata:

Next, run docker-compose up.

Now, navigate to http://localhost:8081/ to access phpMyAdmin. Then log in with the username root and pass root_pass.

Click the SQL tab and you are ready to go.

Creating a Schema

To create a schema, we can use the simple query below.

create schema retail;

You can also use the command so you don't receive an error if the schema already exists.

create schema if not exists retail;

You should now be able to see the schema in your DB UI of choice.

Using the Schema

We can now use the schema by appending the schema name when creating, select, etc. Let's add an order table to our schema.

create table retail.orders (
	order_id serial not null,
	amount decimal not null
);

Let's insert a few rows.

insert into retail.orders (amount)
values (2000), (1000), (3000);

And finally select some data.

select * from retail.orders;

Drop the Schema

If we are done with the schema and want to delete it, we can use either of the following commands to remove it.

drop schema retail;

drop schema if exists retail;

And if we have dependent objects we want to remove, such as the primary key constraint we created, we can use the CASCADE clause.

drop schema if exists retail cascade;