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

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'