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)
);
CREATE TABLE

2.2. with session

\l
List of databases          
Name Owner Encoding Collate Ctype Access privileges
postgres postgres UTF8 zhCN.UTF-8 zhCN.UTF-8  
si postgres UTF8 zhCN.UTF-8 zhCN.UTF-8  
template0 postgres UTF8 zhCN.UTF-8 zhCN.UTF-8 =c/postgres
postgres=CTc/postgres          
template1 postgres UTF8 zhCN.UTF-8 zhCN.UTF-8 =c/postgres
postgres=CTc/postgres          
test postgres UTF8 zhCN.UTF-8 zhCN.UTF-8   
\d
List of relations      
Schema Name Type Owner
public person table si
public personv table postgres
← Back to Index