Ch 5: Creating tables, schemas,

We are using DBeaver as our GUI to interact with our database. We have already created the PostgreSQL dtabase service using Aiven.

The first step is powering on the database service in Aiven. Just head to Aiven, then services, and on the database you created, click on the three dots in the action tab and select 'power on service'.

Once the connection is running, head over to DBeaver. Right-click on your database, select SQL Editor, then open a New SQL script.

This should open a new script where we will write our SQL queries to create the database, schema, and tables.

1.Create the database

Inside the database service we create with Aiven, we can create several databases. So our first step is to create the database, which will host all our schemas and tables.

CREATE DATABASE *database_name*;

Run your script by selecting the script and hitting the 'execute SQL query' button.

Refresh the databases tab, and you should see your database on the tab.

2.Create a schema

In PostgreSQL, data is organized in rows and columns, essentially in tables. You can group these tables in schemas.

A schema defines how different tables connect and keeps them organized.

Before you create a schema, you need to navigate into the database we created otherwise, it will be created in the defaultDB database.

Right-click on the database you created, select SQL Editor, then open a New SQL script

Run this SQL command to ensure you are in the right database;

SELECT current_database();

Once you confirm you are in the correct database, create the schema.

CREATE SCHEMA *schema_name*;

3.Create table

Navigate to the schema you created with this command.

SET search_path to *schema_name*;

This should ensure you create the table inside the schema. Next, we create the table.

CREATE TABLE *table_name* (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

To see the table we created, use the command:

SELECT * FROM *table_name*;

In the next post, I will share some SQL tricks I have learnt in my few days interacting with it.