Postgresql
Table of Contents
1. basic OP
sudo apt install postgresql-client-common sudo apt install postgresql postgresql-contrib
sudo -i -u postgres psql
swith to psql user (postgres, auto created), and then login in
sudo -u postgres psql
login psql from current user
\password postgres; OR alter user postgres with PASSWORD 'newpassword';
set password
psql -h localhost -U 'username' (with be asked for password)
login with password, for locally distributed or remotely for remote login, please install at least one client version( install postgres-client-12) Had to set listen_addresses='*' in postgresql.conf to allow for incoming connections from any ip / all ip add <hostssl all all 0.0.0.0/0 md5> to allow ssl connection if remotely, please check if the port is open
psql "sslmode=require host=141.5.103.1 port=5432 dbname=postgres" –username=postgres
\l \c database \dt \q
DROP SCHEMA public CASCADE; CREATE SCHEMA public;
drop all table
2. src block
2.1. with header
create table personv ( id int, firstname varchar(50), lastname varchar(50), gender varchar(6), dataofbirth DATE ) ; #+end_psrc #+RESULTS: |---| #+header: :engine postgresql #+header: :dbhost 141.5.103.1 #+header: :dbuser postgres #+header: :dbpassword "xxx" #+header: :database postgres #+begin_src sql CREATE TABLE articles ( id SERIAL PRIMARY KEY, title VARCHAR(255), content TEXT, published_by INT, published_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_articles_users FOREIGN KEY (published_by) REFERENCES users (id) );
2.2. with session
\l
\d