How to Create Tables in PostgreSQL

An abstract image depicting the functionalities of tables in postgresql
An abstract image depicting the functionalities of tables in postgresql

PostgreSQL is popular with enterprises across the world and is used in various industries, including manufacturing.

In industrial manufacturing, reliability is key. Production site downtimes can cost companies millions of dollars. This makes PostgreSQL an ideal choice in such situations as it can be configured for full redundancy, automatic failover, and nearly no downtime upgrades.

What is PostgreSQL?

PostgreSQL is an object relational database management system. It is also called “Postgres” and is extendible, enabling one to add custom procedures developed by different programming languages such as Java, C and C++ etc.

Key benefits of PostgreSQL

Some of the applications and software we use and enjoy everyday utilize PostgreSQL in the backend.

Here are a few compelling reasons businesses rely on it so much:

  1. It is secure, as it is constantly being updated and advanced.
  2. It is reliable and can be used in environments that need high availability for maintaining business continuity.
  3. High performance, as compared to MySQL and compatible with most major operating systems. It offers a variety of programming languages.
  4. It is open source and free, allowing it to be used with zero capital cost for projects.
  5. It is easy to download and use.

It is little wonder then that a recent survey revealed that 63.9% developers worldwide ranked PostgreSQL as their preferred database management system, expressing an interest to continue using it for years to come.

Creating Tables in PostgreSQL

Being a relational database, tables are an important feature of PostgreSQL, which consists of multiple related tables. Today, we will look at how to create and manage such tables with a simple example.

By default, PostgreSQL uses PgAdmin(GUI) to interact with Postgres Database.

It provides features to perform several common database operations such as copy tables, schema and tables with data from one database server to another database server.

It also supports all generic functions — Create, rename, update, delete etc.

Before we start working with multiple tables, we need to create tables for that.

For our example, we will create 2 tables:

  1. USERS
  2. STUDENT

Now let us proceed step by step……

1. Creating our tables

  • Our first table — USERS
CREATE TABLE USERS (
id SERIAL NOT NULL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50) NOT NULL DEFAULT ‘abc@gmail.com’,
address VARCHAR(100),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
updated at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
);
  • Our second table — STUDENT
CREATE TABLE STUDENT(
id SERIAL NOT NULL PRIMARY KEY,
iduser INTEGER NOT NULL,
branch VARCHAR(20) NOT NULL DEFAULT ‘CSE’,
batch VARCHAR(10) NOT NULL DEFAULT ‘2020–2021’,
idlibrary VARCHAR(10),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

We have now created both tables successfully.

Now we write a trigger procedure to update the “updated_at” column in both these tables.

2. Creating the Trigger

  • Creating our trigger
CREATE OR REPLACE FUNCTION update_date()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW()
RETURN NEW
END;
$$ LANGUAGE plpgsql
  • Applying the trigger to USERS table(updated_at)
CREATE TRIGGER update_time
BEFORE UPDATE
ON USERS
FOR EACH ROW
EXECUTE PROCEDURE update_date()
  • Applying the trigger to the STUDENT table(updated_at)
CREATE TRIGGER update_time
BEFORE UPDATE
ON STUDENT
FOR EACH ROW
EXECUTE PROCEDURE update_date()

We have successfully created our trigger and applied it to both tables. On to the next step.

3. Inserting data into both tables

Now we insert the data into both tables where student data is dependent on the user’s data, using a single query.

WITH inserted_data AS(
INSERT INTO USERS(first_name, last_name, email, address)
VALUES (‘John’, ‘Mccain’,’john@gmail.com’, ‘Coco solo’)
RETURNING id
)
INSERT INTO STUDENT(iduser,branch,batch,idlibrary) VALUES(
(SELECT id FROM inserted_data), ‘CSE’, ‘2020–2021’, ‘JOHN1234’
);

As shown above, we have now inserted data successfully in both tables and can insert some more data in a similar fashion.

4. Fetching data from our tables

Use the join clause to fetch data from both the tables.

SELECT u.id, u.first_name,u.last_name,u.email,u.address,s.*
FROM USERS u
JOIN STUDENT s
ON u.id=s.iduser;

5. Using the data in our tables

Update query can be used in both ways separately, or combined as well. Here we go with the latter option.

UPDATE STUDENT
SET batch=’2021–2022', branch=’EN’
FROM USERS
WHERE USERS.id=STUDENT.iduser
AND USERS.id=3;

6. Adding new columns in the table

Sometimes we may need to add a new column into an existing table. We can do so with the code below:

ALTER TABLE STUDENT
ADD COLUMN semester INTEGER NOT NULL DEFAULT 1;

7. Renaming a column

Now further we can rename the column names as well-

ALTER TABLE STUDENT
RENAME COLUMN idlibrary TO library_id;

8. Drop all schemas present in the database

In case we want to drop all the tables present in the database, we only need to run the query below:

DO $$ DECLARE
record RECORD
BEGIN
FOR record IN (SELECT table_name FROM pg_table WHERE schema = current_schema())
LOOP
EXECUTE ‘DROP TABLE IF EXISTS’ || quote_ident(record.table_name) ||’ CASCADE’
END LOOP
END $$

Conclusion

The above code will help you create tables in PostgreSQL and is a small part of what is possible. Being open source, extendible, and secure, postgreSQL offers a robust solutions for businesses and developers everywhere.

Author — Ajeet Kumar Maurya, DLT Labs

About Ajeet Kumar Maurya: Ajeet is a software engineer at DLT Labs. He is a part of the DL Certify team and is skilled in Node.js, PSQL, JS, MongoDB, Postgres.

Written by

DLT Labs is a global leader in Distributed Ledger Technology and Enterprise Products. To know more, head over to: https://www.dltlabs.com/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store