Run the below command in linux or windows or mac machine from the terminal or command-prompt to pull PSQL from docker-hub.
docker run --name postgresql-container -p 5432:5432 -e POSTGRES_PASSWORD=somePassword -d postgres
In the above command replace :
Optional - postgresql-container with a preferable container name if necessary.
somePassword with a password to authenticate and connect to the postgres (in application with connection string as well as the PG-admin viewer).
Install PG-admin using Docker:
Download the pgAdmin-4 browser version from docker-hub using the following command.
sudo docker run --rm -p 5050:5050 thajeztah/pgadmin4
Verify a new container created and running at 0.0.0.0:5432 with the below command.
docker ps -a
$ sudo docker pull postgres
$ sudo docker pull postgres:13.2-alpine
Running the Docker image for first time
$ sudo docker run --name postgresql-container -p 5432:5432 -e POSTGRES_PASSWORD=somePassword -d postgres:13.2-alpine
- Where some-postgresh is the name of the container
- Now if one has psql installed locally and want to
connect to postgresql
- Alternatively,
Connect to the docker container first
$ sudo docker exec -it 35a62e54e14b bash
- Connecting a "postgre" username
$ psql -h localhost -p 5432 -U postgres -W
ENTER the password asked during the setup at line6
postgres=# \du
-Relation \ display tables
# \dt
- Display more data for table
# \d+
- Display schema of table
# \d+ <tablename>
List of All Database
$ \l
$ \list
Creating a user and Database
# create user harshit WITH PASSWORD 'secret';
# create Database people with owner 'harshit';
Now entering datatabase with as the user you created
from the shell
$ psql people harshit
$ <enter ther password>
Creating a Table
CREATE TABLE pg4e_debug (
id SERIAL,
query VARCHAR(4096),
result VARCHAR(4096),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(id)
);
SELECT query, result, created_at FROM pg4e_debug;
CREATE TABLE pg4e_result (
id SERIAL,
link_id INTEGER UNIQUE,
score FLOAT,
title VARCHAR(4096),
note VARCHAR(4096),
debug_log VARCHAR(8192),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP
);
CREATE TABLE ages (
name VARCHAR(128),
age INTEGER
);
Inserting Data into the table
DELETE FROM ages;
INSERT INTO ages (name, age) VALUES ('Corinn', 22);
INSERT INTO ages (name, age) VALUES ('Cully', 22);
INSERT INTO ages (name, age) VALUES ('Ghyll', 14);
INSERT INTO ages (name, age) VALUES ('Karyss', 25);
INSERT INTO ages (name, age) VALUES ('Tracey', 15);
Read the data from Table
SELECT make.name, model.name
FROM model
JOIN make ON model.make_id = make.id
ORDER BY make.name LIMIT 5;
Update the value from Table
Deleting the data from Table
DELETE FROM users where email='abc@gmailcom'
WARNING
DELETE FROM users // will delete all rown
Ordering in Table
LIMIT / OFFSET Clause
Datatypes
Char(n) - allocate the entire space (faster for small string where the lenght is known) (fixing size helps a lot) (fixe to ASCII char set)
Varchar(n) - allocate variables amount of space depending on the data length(less space)
TEXT - pararaph or HTML pages ,generally not used with indexing or sorting and only then limited to prefix
BYTEA(n) up to 255 bytes
SMALLINT (-32768,+32768)
INTEGER (2 Billion)
BIGINT (10**18 ish)
REAL (32 bit) 10**38 with 7 digit of approximation
DOUBLE PRECISION (64 bit) 10**308 with 14 digits of accuracy
NUMERIC (accuracy , decimal)- specified digits if accuracy and digits after decimal point
TIMESTAMP - ' YYYY-MM-DD HH:MM:SS"
DATE -" YYYY- MM- DD "
TIME -" HH: MM :SS "
Built in PostgreSQL functions NOW()
Database keys and Indexes in PostgreSQL
id SERIAL
UNIQUE (logical key)
Auto increment key for as data comes in
Database decides itself what to use for indexing
Trees (B tree)
Hashes (Modulus % like) are used for Indexes
Analysing Query performance
explain analyze select id from employee where id=2000;
Loading Data from SQL
wget https://www.pg4e.com/tools/sql/library.csv
curl -O https://www.pg4e.com/tools/sql/library.csv
CREATE TABLE track_raw
(title TEXT, artist TEXT, album TEXT,
count INTEGER, rating INTEGER, len INTEGER);
\copy track_raw(title,artist,album,count,rating,len) FROM 'library.csv' WITH DELIMITER ',' CSV;
Relational Database
Keys
Primary Key - integer auto increment field
Logical key - What outside world use for lookup
Foreign key - value point to another table
Best Practise
Never use logical key as primary key
logical keys can do change , albeit slowly
Relationships that are based on matching string fields are less efficient then integers
Database Normalization
3 NF
Do not replicate Data , instead reference data , point at data
use integer for keys and for references
Add a special "key" column to each table , which you will make references to .
CREATE TABLE make (
id SERIAL,
name VARCHAR(128) UNIQUE,
PRIMARY KEY(id)
);
CREATE TABLE model (
id SERIAL,
name VARCHAR(128),
make_id INTEGER REFERENCES make(id) ON DELETE CASCADE,
PRIMARY KEY(id)
);
make model
Chevrolet Cruze Limited
Chevrolet Cruze Limited Eco
Chevrolet Cruze Premier
Volvo V70 FWD
Volvo V70 R AWD
SELECT make.name, model.name
FROM model
JOIN make ON model.make_id = make.id
ORDER BY make.name LIMIT 5;
Many to Many relationship
CREATE TABLE student (
id SERIAL,
name VARCHAR(128) UNIQUE,
PRIMARY KEY(id)
);
DROP TABLE course CASCADE;
CREATE TABLE course (
id SERIAL,
title VARCHAR(128) UNIQUE,
PRIMARY KEY(id)
);
DROP TABLE roster CASCADE;
CREATE TABLE roster (
id SERIAL,
student_id INTEGER REFERENCES student(id) ON DELETE CASCADE,
course_id INTEGER REFERENCES course(id) ON DELETE CASCADE,
role INTEGER,
UNIQUE(student_id, course_id),
PRIMARY KEY (id)
);
Rhylee, si106, Instructor
Grant, si106, Learner
Kassie, si106, Learner
Rahma, si106, Learner
Raymond, si106, Learner
Xiong, si110, Instructor
Fatiha, si110, Learner
Neeve, si110, Learner
Shelley, si110, Learner
Yaris, si110, Learner
Tristain, si206, Instructor
Lukas, si206, Learner
Reeva, si206, Learner
Shreeram, si206, Learner
Toby, si206, Learner
SELECT student.name, course.title, roster.role
FROM student
JOIN roster ON student.id = roster.student_id
JOIN course ON roster.course_id = course.id
ORDER BY course.title, roster.role DESC, student.name;
