How to Insert, Update and Delete Data in PostgreSQL on Ubuntu 21.04

To Insert, Update and Delete Data in PostgreSQL on Ubuntu 21.04

Introduction:

PostgreSQL is an open source Object-Relational Database Management System that supports many SQL standards, including foreign keys, triggers, views, and transactions, as well as object-oriented features such as inheritance.

Introduction Procedure:

Step 1: Check the OS version by using the below command

root@linuxhelp:~# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 21.04
Release:	21.04
Codename:	hirsute

Step 2: Login to the PostgreSQL Database by using the below command

root@linuxhelp:~# sudo -u postgres psql 
could not change directory to "/root": Permission denied
psql (14.2 (Ubuntu 14.2-1.pgdg21.04+1))
Type "help" for help.

Step 3: Create a new database called mytest by using the below command

postgres=# create database mytest;
CREATE DATABASE

Step 4: List all databases via \l by using the below command

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 mytest    | postgres | UTF8     | en_IN   | en_IN | 
 postgres  | postgres | UTF8     | en_IN   | en_IN | 
 template0 | postgres | UTF8     | en_IN   | en_IN | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_IN   | en_IN | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

Step 5: Connect to mytest database via \c by using the below command

postgres=# \c mytest
You are now connected to database "mytest" as user "postgres".

Step 6: Use an enumeration (TYPE) in our table by using the below command

mytest=# create type cat_enum AS ENUM ('coffee', 'tea');
CREATE TYPE

Step 7: Display all types via /dT

mytest=# \dT+
                                        List of data types
 Schema |   Name   | Internal name | Size | Elements |  Owner   | Access privileges | Description 
--------+----------+---------------+------+----------+----------+-------------------+-------------
 public | cat_enum | cat_enum      | 4    | coffee  +| postgres |                   | 
        |          |               |      | tea      |          |                   | 
(1 row)

Step 8: Create a new table by using the below command

mytest=# create table if not exists cafe (
  id SERIAL PRIMARY KEY,        -- AUTO_INCREMENT integer, as primary key
  category cat_enum NOT NULL,   -- Use the enum type defined earlier
  name VARCHAR(50) NOT NULL,    -- Variable-length string of up to 50 characters
  price NUMERIC(5,2) NOT NULL,  -- 5 digits total, with 2 decimal places
  last_update DATE              -- 'YYYY-MM-DD'
);
CREATE TABLE

Step 9: Display all tables in the current database by using the below command

mytest=# \dt+
                                   List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+------+-------+----------+-------------+---------------+---------+-------------
 public | cafe | table | postgres | permanent   | heap          | 0 bytes | 
(1 row)

Step 10: View ALL Data in the tables by using the below command

mytest=# select * from cafe;
 id | category | name | price | last_update 
----+----------+------+-------+-------------
(0 rows)

Step 11: Insert Rows to the table by using the below command

mytest=# insert into cafe (category, name, price) VALUES
  ('coffee', 'Espresso', 3.19),
  ('coffee', 'Cappuccino', 3.29),
  ('coffee', 'Caffe Latte', 3.39),
  ('coffee', 'Caffe Mocha', 3.49),
  ('coffee', 'Brewed Coffee', 3.59),
  ('tea', 'Green Tea', 2.99),
  ('tea', 'Wulong Tea', 2.89);
INSERT 0 7

Step 12: View ALL Data in the tables by using the below command

mytest=# select * from cafe;
 id | category |     name      | price | last_update 
----+----------+---------------+-------+-------------
  1 | coffee   | Espresso      |  3.19 | 
  2 | coffee   | Cappuccino    |  3.29 | 
  3 | coffee   | Caffe Latte   |  3.39 | 
  4 | coffee   | Caffe Mocha   |  3.49 | 
  5 | coffee   | Brewed Coffee |  3.59 | 
  6 | tea      | Green Tea     |  2.99 | 
  7 | tea      | Wulong Tea    |  2.89 | 
(7 rows)

Step 13: Update data of the table by using the below command

mytest=# update cafe SET price = price * 1.1 WHERE category = 'tea';
UPDATE 2

Step 14: View ALL Data in the tables by using the below command

mytest=# select * from cafe ;
 id | category |     name      | price | last_update 
----+----------+---------------+-------+-------------
  1 | coffee   | Espresso      |  3.19 | 
  2 | coffee   | Cappuccino    |  3.29 | 
  3 | coffee   | Caffe Latte   |  3.39 | 
  4 | coffee   | Caffe Mocha   |  3.49 | 
  5 | coffee   | Brewed Coffee |  3.59 | 
  6 | tea      | Green Tea     |  3.29 | 
  7 | tea      | Wulong Tea    |  3.18 | 
(7 rows)

Step 15: Delete data of the table by using the below command

mytest=# delete from cafe where id = 6;
DELETE 1

Step 16: View ALL Data in the tables by using the below command

mytest=# select * from cafe ;
 id | category |     name      | price | last_update 
----+----------+---------------+-------+-------------
  1 | coffee   | Espresso      |  3.19 | 
  2 | coffee   | Cappuccino    |  3.29 | 
  3 | coffee   | Caffe Latte   |  3.39 | 
  4 | coffee   | Caffe Mocha   |  3.49 | 
  5 | coffee   | Brewed Coffee |  3.59 | 
  7 | tea      | Wulong Tea    |  3.18 | 
(6 rows)

Step 17: Quit from the Database

mytest=# quit

By this How to Insert, Update and Delete Data in PostgreSQL on Ubuntu 21.04 has been completed.