Postgresql

Frequently used code for postgresql

Recipes

Installing - Ubuntu

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Installing - Docker

docker run --name pg_docker \
    -p 5432:5432 \
    -e POSTGRES_DB=db-name \
    -e POSTGRES_PASSWORD=pass \
    -d postgres
    
# Access from host
psql -h localhost -p 5432 -d db-name -U postgres --password

Execute commands from current user (without user on db)

echo "\d" | sudo -u postgres psql

Create database

CREATE DATABASE phishiq_db;

Create user

CREATE USER user PASSWORD 'pass';

Grant privilage to such user

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user;
ALTER ROLE user SUPERUSER;
ALTER ROLE user CREATEDB; 
ALTER ROLE user WITH LOGIN;

Create table

CREATE TABLE tbl([col [type]]...)

List db \l

List users \du

List tables

\dt

Change user password

ALTER USER davide WITH PASSWORD 'hu8jmn3';

List constraint for a table

\d+ tablename

Create constraint

ALTER TABLE raw_backup ADD CONSTRAINT raw_backup_pk UNIQUE (ticker, period, date);

Create index

CREATE INDEX idx_date_backup ON raw_backup(date);

Remove constraint

ALTER TABLE affiliations
DROP CONSTRAINT affiliations_organization_id_fkey;

Generating dump file

pg_dump -U postgres --schema-only db_name > file.txt

Loading dump file

psql -U username dbname < dbexport.pgsql

Kill postgresql session

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
;

Useful tools

• Postgrest

• Cheatsheet