Database Commands

Create, Read, Update, Delete data from tables

Description Command


//Linux
# sudo -u postgres psql


//Windows
C:\Users\user> psql -U postgres
Password for user postgres:

psql (17.3)
WARNING: Console code page (437) differs from Windows code page (1252)
          8-bit characters might not work correctly. See psql reference
          page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

Create database & connect to it


postgres=# create database teachers;
CREATE DATABASE
postgres=# \c teachers;              //Connect to DB
You are now connected to database "teachers" as user "postgres".
teachers-#

List Database
List Tables
Show Table schema


********** Lists all available commands **********
postgres=# \h

********** Lists Databases **********
postgres=# \l                       //It has 3 predefined builtin databases
List of databases
Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | 
teachers  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =Tc/postgres         +
       |          |          |                 |         |         |            |           | postgres=CTc/postgres
template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
       |          |          |                 |         |         |            |           | postgres=CTc/postgres
template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
       |          |          |                 |         |         |            |           | postgres=CTc/postgres

teachers=# \dt
          List of relations
Schema |      Name      | Type  |  Owner   
--------+----------------+-------+----------
public | teachers_login | table | postgres
(1 row)

********** Table Schema **********
teachers=# \d teachers_login
                                   Table "public.teachers_login"
Column     |          Type          | Collation | Nullable |                  Default                   
---------------+------------------------+-----------+----------+--------------------------------------------
id            | integer                |           | not null | nextval('teachers_login_id_seq'::regclass)
email         | character varying(255) |           | not null | 
password_hash | text                   |           | not null | 
password_salt | text                   |           | not null | 
active        | boolean                |           | not null | false

Create table & insert data, list


********** Create Table **********
teachers=# CREATE TABLE teachers_login (
id serial PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash TEXT NOT NULL
);
CREATE TABLE

********** List Table **********
teachers=# \dt

********** Insert into Table **********
teachers=# INSERT INTO teachers_login (email, password_hash)
VALUES ('teacher1@test.com', 12117530842303537118);
INSERT 0 1

********** Show contents of Table **********
teachers=# SELECT * FROM teachers_login;
id |       email       |    password_hash     
----+-------------------+----------------------
1 | teacher1@test.com | 12117530842303537118
(1 row)

Delete


********** Delete database teachers **********
postgres=# DROP DATABASE teachers;
DROP DATABASE

********** Delete table users **********
postgres=# DROP table users;
DROP TABLE

JOIN 2 tables

1. Create 2 tables (Person, PersonFriend)
2. Find friends of Bob from PersonFriend table.
https://onecompiler.com/mysql/42szxs8p5


create table Person(ID int primary key, Person text);
insert into Person values(1, 'alice');
insert into Person values(2, 'Bob');
insert into Person values(99, 'zach');
select * from Person;

create table PersonFriend(PersonID int, FriendID int);
insert into PersonFriend values(1, 2);
insert into PersonFriend values(2, 1);
insert into PersonFriend values(2, 99);
insert into PersonFriend values(99, 1);
select * from PersonFriend;

#1 Person table is aliased as p1. Select Person from Person table
#2 Person table is aliased as p1.
#3 INNER JOIN
#   Person Table(ID) & PersonFriend.FriendID
#4 INNER JOIN
#   PersonTable=p2. 
#5 Filter the records where Person=Bob

SELECT p1.Person                    #1
FROM Person p1                      #2
JOIN PersonFriend ON PersonFriend.FriendID = p1.ID   #3
JOIN Person p2 ON PersonFriend.PersonID = p2.ID   #4
WHERE p2.Person = 'Bob'             #6

Output:
+----+--------+
| ID | Person |
+----+--------+
|  1 | alice  |
|  2 | Bob    |
| 99 | zach   |
+----+--------+
+----------+----------+
| PersonID | FriendID |
+----------+----------+
|        1 |        2 |
|        2 |        1 |
|        2 |       99 |
|       99 |        1 |
+----------+----------+
+--------+
| Person |
+--------+
| alice  |
| zach   |
+--------+