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.