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 alice@example.com 2 Bob 35 bob@example.com 3 Carol 32 carol@example.com 4 David 28 david@example.com	id name age email 1 Alice 25 alice@example.com 2 Bob 35 bob@example.com 3 Carol 32 carol@example.com 4 David 28 david@example.com

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 <--> geom

Insert with python

# With a geojson python dict such as
geom = {
    "type":"Polygon",
    "coordinates":[[[13.861471,37.942081],[15.111086,37.947537],[15.109632,36.95783],[13.87637,36.952565],[13.861471,37.942081]]]
}
# You just need to do
str_geom = json.dumps(geom)
# And put it into the following SQL command 

INSERT INTO table1 ("zone") VALUES (ST_SetSRID(ST_GeomFromGeoJSON(%s), 4326));

---

Extract with python

SELECT ST_AsGeoJSON(zone) FROM table1;

# You receive the string of the geojson
from shapely import shape
geojson = json.loads(response[0][0])
geom = shape(geojson)

---

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

sql size tables (Modify "WHERE table_schema = 'public'" if needed).

SELECT 
	  table_name, 
	  pg_size_pretty( pg_total_relation_size(quote_ident(table_name))), 
	  pg_total_relation_size(quote_ident(table_name))
FROM 
	  information_schema.tables
WHERE 
	  table_schema = 'public'
ORDER BY 
	  pg_total_relation_size(quote_ident(table_name)) DESC

Output Example :

table_name	pg_size_pretty	pg_total_relation_size
subdivision	18 GB	19829039104
benchmark_full_precision	18 GB	18911174656
benchmark_quantized_4bit	422 MB	442843136
metadata	12 MB	12206080
spatial_ref_sys	7144 kB	7315456
product	1120 kB	1146880
process_config	48 kB	49152
subdivision_level	24 kB	24576

sql size column (replace all "table_name" and "column_name" in the query, even the one in double quotes)

SELECT
    pg_size_pretty(SUM(pg_column_size(column_name))) AS total_size,
    pg_size_pretty(AVG(pg_column_size(column_name))) AS average_size,
    SUM(pg_column_size(column_name)) * 100.0 / pg_total_relation_size('table_name') AS percentage
FROM table_name;

Output Example :

total size	average_size	percentage
32 MB	5.0000000000000000 bytes	7.4892952614263846

Percentage of all columns will not add up to 100% due to indexes, dead tuples, tuples headers ...