PostgreSQL Commands

postgresql.sql

# Run using docker image
docker run --name db --rm -e POSTGRES_PASSWORD="mypasswd" -d postgres:alpine
docker exec -it db bash --> get inside container

# Copy file to docker container
docker cp foo.txt container_id:/foo.txt

docker run 
  --name postgresql 
  -e POSTGRES_USER=myusername 
  -e POSTGRES_PASSWORD=mypassword 
  -p 5432:5432 
  -v /data:/var/lib/postgresql/data 
  -d postgres

psql -U postgres --> open postgresql
psql -h [host] -p [PORT] -U [username] [database-name] --> open database
psql -h localhost -p 5432 -U musleh test --> example

\l --> list all database
\c [database-name] --> connect to specific database
\c test; example
\d --> list all table
\d [table_name] --> describe table

CREATE DATABASE [database-name]; --> create database  command
CREATE DATABASE test; --> example create database

DROP DATABASE [database-name]; --> delete database
DROP DATABASE test; --> example

CREATE TABLE table_name (
  Column name + data type + constraints if any
); --> create table

CREATE TABLE person (
  id BIGSERIAL  NOT NULL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  gender VARCHAR(6) NOT NULL,
  date_of_birth DATE NOT NULL
); --> example

DROP TABLE [table_name]; --> delete table
DROP TABLE person; --> example

INSERT INTO person (first_name, last_name, gender, date_of_birth) VLAUES (
  'Anna', 'Smith', 'FEMALE', '1988-02-25'
); --> insert data into table

\i [location_of_file] --> insert data from file
\i /home/person.sql --> example

SELECT * FROM person ORDER BY country_of_birth ASC;
SELECT * FROM person ORDER BY country_of_birth DESC;
SELECT DISTINCT country_of_birth FROM person ORDER BY country_of_birth DESC; --> remove duplicate

SELECT * FROM person WHERE country_of_birth = 'Bangladesh'; --> use single quote
SELECT * FROM person WHERE country_of_birth = 'Bangladesh' AND gender = 'Female'; --> use single quote
SELECT * FROM person WHERE gender = 'Female' AND (country_of_birth = 'Bangladesh' OR country_of_birth = 'Pakistan'); --> use single quote

SELECT 1 <> 1; --> 1 is not equal 1;

SELECT * FROM person LIMIT 10; --> only show 10 row
SELECT * FROM person OFFSET 5 FETCH FIRST 5 ROW ONLY; --> sql standrad
SELECT * FROM person OFFSET 5 LIMIT 10; --> only show 10 row start from 6

SELECT * FROM person WHERE country_of_birth IN ('Bangladesh', 'Pakistan');

SELECT * FROM person WHERE date_of_birth BETWEEN DATE '2018-01-01' AND '2021-01-01';

SELECT * FROM person WHERE eamil LIKE '%.com'; --> end with .com

SELECT cuntry_of_birth, COUNT(*) FROM person GROUP BY country_of_birth; -->