postgres_SQL.md

Docker

Setup

mkdir docker
mkdir docker/volume
mkdir docker/volume/postgres
touch docker/postgresql.conf

Docker command

docker run --name postgres_container -e POSTGRES_PASSWORD=password -e POSTGRES_USER=user -v ./docker/volume/postgres:/var/lib/postgresql/data -p 5432:5432 -d postgres 

Docker compose

name: postgres

services:
  postgres:
    container_name: postgres_container
    image: postgres
    # If permissions error (your folder but docker does not access it) (id -u : id -g)    
    # user: id:group_id
    ports: 
      - 5432:5432
    volumes:
      - ./docker/volume/postgress:/var/lib/postgresql/data
      - ./docker/postgresql.conf:/etc/postgresql.conf
    environment:
      POSTGRES_DB: db
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password

---

SQL

ENUM

For good design, enums should be in the application and not the DB

CREATE TYPE enum_type_name AS ENUM ('case1', 'case2', 'case3');

CREATE

CREATE TABLE IF NOT EXISTS table (
  id SERIAL PRIMARY KEY,
  col1 INTEGER,
  col2 VARCHAR(255),                          -- max char length, can be less
  col4 TEXT,                                  -- large text (65,535 bytes)
  col3 TIMESTAMP NOT NULL DEFAULT NOW(),      -- TIMESTAMPZ = with timezone
  col4 enum_type_name                         -- Avoid ENUM in DB, just store the data simply
)

INSERT INTO

-- 1 item

-- batch items

INDEX

-- Single column index
CREATE INDEX IF NOT EXISTS idx_columnname ON table (columnname);
-- Mulit column index (composite index) (!! Order is important !!)
CREATE INDEX IF NOT EXISTS index_clumnname1_columnname2 ON table (columnname1, columnname2);

SELECT

Simple

SELECT * FROM table;
users id name age email 1 Alice 25 [email protected] 2 Bob 35 [email protected] 3 Carol 32 [email protected] 4 David 28 [email protected]	id name age email 1 Alice 25 [email protected] 2 Bob 35 [email protected] 3 Carol 32 [email protected] 4 David 28 [email protected]

SELECT col1, col2 FROM table;

SELECT COUNT(id) FROM table;      -- COUNT(*) = lenght*width of the table 

complex

---

SQL Postgis

Docker

Same as normal postgres but replace the image by "postgis/postgis"

Index

For better query efficiency on the geometry column

CREATE INDEX table_column_idx ON table USING GIST (column);
CREATE INDEX contries_geom_idx ON contries USING GIST (geom);

Geojson to geom

SELECT ST_GeomFromGeoJSON('{"coordinates": [6.923589753568848,43.65881928466803],"type": "Point"}') As wkt;
--  wkt => 0101000020E6100000E0FE2983C1B11B40C8CFB83054D44540
SELECT ST_AsText(ST_GeomFromGeoJSON('{"coordinates": [6.923589753568848,43.65881928466803],"type": "Point"}')) As wkt;
--  wkt => POINT(6.923589753568848 43.65881928466803)

Nearest geom

SELECT *, geom <-> CONCAT('SRID=4326;', ST_AsText(ST_GeomFromGeoJSON('{"coordinates": [6.923589753568848,43.65881928466803],"type": "Point"}')))::geometry AS dist
FROM grid ORDER BY dist LIMIT 10;

Distance operator

• <-> —> Returns the 2D distance between A and B
• <#> —> Returns the 2D distance between A and B bounding boxes

---

PSQL

Connect to DB

# Connect to local db (add -W to ask for password)
psql -d db_name -U username
# Connect to remote db
psql -h url_address -p db_port -d db_name -U username -W

List databases

\l
# more info (size)
\l+
# filter by exact db name 
\l db_name

Switch to another database

\c db_name

List tables in current database

\dt
# filter by exact table name
\dt table_name

Describe table (columns + indexes)

\d table_name

List schemas

\dn

List users

\du

Record results of pqsl commands

\o record_filename
# All your commands/queries. The result will not be displayed
\o
# The commands results will be inside the record_filename file

Quit psql

\q

Backup / Restore

https://gist.github.com/ArthurDelannoyazerty/575573a0f09769a49ff4f70fe96b0aeb

pg_dump -f /pg_dump.dump -Fc --dbname DB_NAME -U USERNAME

pg_restore --dbname DB_NAME -c -C -O --no-acl -U USERNAME /pg_dump.dump

---

Python

Load countries geojson into postgis

https://gist.github.com/ArthurDelannoyazerty/6168929b279fbc169241e16ebabd34a9