PostgreSQL

Install

sudo apt-cache search postgresql | grep postgresql
sudo apt -y install postgresql-14

Command

psql -V   //psql (PostgreSQL) 14.5 (Ubuntu 14.5-1.pgdg20.04+1)
systemctl status postgresql

sudo service postgresql stop
sudo service postgresql start

sudo -u postgres psql   //Connect Database
sudo su postgres

psql   // use psql

CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD '12345';   //create user role admin

create user user_login with encrypted password '12345';
grant all privileges on database db_test to user_login;

postgres=# \du          //List User Role
postgres=# SHOW listen_addresses;     //Show listen address

Allow to Connect Remote

postgres=# SHOW listen_addresses;
postgres=# ALTER SYSTEM SET listen_addresses = '*';

postgres=# SHOW wal_level;
postgres=# ALTER SYSTEM SET wal_level = logical;

Data Type

UUID (Universally Unique Identifiers)

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

JSON

row_to_json

SELECT row_to_json(game)
FROM game
WHERE game_id = 1;

json_object_agg

json_agg

Event Triggers

pg_notify()

pg_eventserv เป็นตัวต่อ web socket กับ PostgreSQL LISTEN/NOTIFY

Migation