Connect to a PostgreSQL Database Server

Execute the “psql” command along with the hostname and user name to make a connection with the Postgres Database Server:

psql -h localhost -U postgres

Executing the above-stated command will take us to the SQL Shell, where we can execute/run SQL queries and psql commands:

img

Create a PostgreSQL Database

Now we are all set to create a new Postgres database. For this purpose, use/execute the “CREATE DATABASE” command along with the database name. For instance, we are creating a database named “tsl_employee”:

CREATE DATABASE tsl_employee;

img

Confirm Database Creation

Display all the databases to view the newly created database:

\l

img

It can be seen that the “tsl_employee” database has been created successfully.

Establish a Connection With a Database

Type out the “\c” meta-command along with the database name of your choice to establish a connection with it. For instance, we want to connect to the “tsl_employee” database:

\c tsl_employee;

img

A connection has been successfully established with the specified database.

Create a Table in the Database

To make a new table in the selected database, utilize the “CREATE TABLE <table_name>(col_1 <data_type>, col_2 <data_type>, col_3 <data_type>,…, col_N <data_type>);” command. Where table_name represents a table to be created, col_1, col_2, …, col_N are the column names, and data_type represents any valid data type. Here, we are creating a table named “tech_authors”:

CREATE TABLE tech_authors(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, TYPE TEXT NOT NULL, CATEGORY TEXT NOT NULL, ATICLES INT NOT NULL);

Executing the “CREATE TABLE” command will create a new table with the desired columns:

img

Insert Records into a Postgres Table

Use the “INSERT INTO <table_name> VALUES (value_1, value_2, value_3, …);” command for inserting new records into the newly created table. For instance, we have inserted the following values:

INSERT INTO tech_authors VALUES (1, 'Laiba', 'Senior', 'Docker', 50);

img

Fetch Table Data

Write out the provided command to view the specific table’s data:

SELECT * FROM tech_authors;

img

In the above screenshot, the data of the “tech_authors” table can be seen.