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 basic syntax of a Schema is as follows:
CREATE SCHEMA [IF NOT EXISTS] schema_name;
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:
Now, navigate to
http://localhost:8081/ to access phpMyAdmin. Then log in with the username
root and pass
Click the SQL tab and you are ready to go.
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.
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;
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;