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

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'