Postgres

Introduction

This is a page for keeping track of using postgres

Install

Nice and easy lemon squeezy

sudo apt install postgresql postgresql-contrib

Getting to CLI

Login and envoke the CLI

sudo -i -u postgres
psql

Quiting..

\q

Getting it to Work from Install

Found that by default it does not work. Sigh! Tested with

psql --host=localhost --dbname=test_db --username=test_user

To fix this

sudo apt install locate
locate pg_hba.conf

When you find this change

 local   all             all                                peer

to

 local   all             all                                md5

Restart the service

sudo service postgresql restart

Make sure the public schema has permissions. Connect to datasase as postgres

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to test_user;

Useful commands

Connect as a user

  psql -d mydb -U myuser

/* Show users */
\du

/* Show databases */
\l

/* Show tables */
\dt

/* Set password */
alter user test with encrypted password 'testpass'

/* Create database */
CREATE DATABASE testdb

/* Grant Privileges */
grant all privileges on database testdb to test


/* Use database */
\c testdb

/* Create Table */
CREATE TABLE employees ( 
    id SERIAL PRIMARY KEY, 
    "Name" VARCHAR (50), 
    birthdate DATE CHECK (birthdate > '1900-01-01'), 
    salary numeric CHECK(salary > 0) 
); 

/* Describe the Table */
\d test_table

/* Create Index */
CREATE UNIQUE INDEX name_idx ON employees (name)

/* Command History */
\s

  create user user_name with encrypted password 'mypassword'