Send SMS from PostgreSQL (part 2/4)
Create Database Tables

To create a solution to send text messages from PostgreSQL we will need 13 minutes. This article provides directions and the necessary information to help you complete this procedure efficiently. Maybe the greatest benefit of this PostgreSQL solution is that all you have to do is perform some basic configuration and you will be ready to send sms in a short time. This solution is based on Ozeki SMS Gateway, so you can take advantage of that fact that it offers excellent logging capabilities, which means you can track down errors quickly. We assume you are familiar with the basics of how mobile text messaging works. You will be presented some valuable information and well-structured step-by-step instructions. We hope you will enjoy reading it.

Video content
1. Connect to PostgreSQL
2. Create User in PostgreSQL
3. Copy CREATE TABLE statements

How to create the proper database table (Video tutorial)

In this vide, you will learn how to create a correctly working database table using the PostgreSQL GUI called pgadmin. The video will start with opening pgadmin and will end with the working database table. You will learn how to configure the table during creation and how to setup the permissions in pgadmin. The video is only a little longer then 2 minutes and it features all the steps you need to take in a detailed form. You will have no problem following the tutorial.

Video 1 - How to create the proper database table (Video tutorial)

Please scroll down to copy the SQL statements (Figure 2-3) used in the video. If you have created the database in PostgreSQL, you can jump to the next video.

Launch pgadmin

On Figure 1, you can see the icon you need to look for called pgadmin. Click it to launch the app where you can start setting up the database structure and start connecting to the PostgreSQL server.

connect to the postgresql server by using the pgadmin software
Figure 1 - Connect to the PostgreSQL server by using the PgAdmin software

Create Ozeki user and database

The following SQL commands can be used to create a user called 'ozekiuser', with a password of 'ozekipass'. This is a PostgreSQL user account. You can use this user account to connect to the ozeki database.

create ozekiuser user
Figure 2 - Create ozekiuser user

CREATE ROLE ozekiuser WITH
	LOGIN
	SUPERUSER
	CREATEDB
	CREATEROLE
	INHERIT
	NOREPLICATION
	CONNECTION LIMIT -1
	PASSWORD 'ozekipass';
Figure 3 - Copy CREATE ROLE statements

Now please copy the code, you can see on Figure 5. You can see that we have pasted the code, and it has created our database, on Figure 4.

create ozekidb database
Figure 4 - Create ozekidb database

CREATE DATABASE ozekidb
    WITH 
    OWNER = ozekiuser
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;
Figure 5 - Copy CREATE DATABASE statements

CREATE TABLE statements to use on your PostgreSQL server

The code

CREATE TABLE ozekimessagein
(
id serial,
sender varchar(255),
receiver varchar(255),
msg varchar(160),
senttime varchar(100),
receivedtime varchar(100),
operator varchar(100),
msgtype varchar(160),
reference varchar(100)
);
 
CREATE TABLE ozekimessageout
(
id serial,
sender varchar(255),
receiver varchar(255),
msg varchar(160),
senttime varchar(100),
receivedtime varchar(100),
reference varchar(100),
status varchar(20),
operator varchar(100),
errormsg varchar(250),
msgtype varchar(160)
);
Figure 6 - Copy CREATE TABLE statements

Copy the code

On Figure 7, you can see that how you can copy the provided code. This code will build the database so the Ozeki SMS Gateway could work with it. Right-click the highlighted code and choose the copy option.

copy create table statements from figure 2
Figure 7 - Copy CREATE TABLE statements from Figure 2

Paste the code

To build your database, you need to paste the copied code into the query editor section of pgadmin. Paste it and run the code. It will build a usable database that you can work with. You can see the pasted code on Figure 8.

paste create table statements and run them on postgresql
Figure 8 - Paste CREATE TABLE statements and run them on the PostgreSQL database server

More information