PostgreSQL how-to's

POSTGRESQL STARTER

1. INSTALLATION AND CONFIGURATION

1.1. Install the database server

 echo 'deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main' >> /etc/apt/sources.list.d/pgdg.list

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |   sudo apt-key add -

sudo apt-get update

apt-get install postgresql-9.4

1.1.1. add the repo

to add the repo and update the repositories in Ubuntu run:

 echo 'deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main' >> /etc/apt/sources.list.d/pgdg.list

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |   sudo apt-key add -

sudo apt-get update

apt-get install postgresql-9.4

1.1.2. install and verify

to add the repo and update the repositories in Ubuntu run:

apt-get install postgresql-9.4

su - postgres

/usr/lib/postgresql/9.4/bin/pg_ctl -V
pg_ctl (PostgreSQL) 9.4.8

sh /etc/init.d/postgresql status

1.2. Configuration files

Check the following sources:
http://www.davidpashley.com/articles/postgresql-user-administration/

# main configuration file , check with ps -ef
/etc/postgresql/9.4/main/postgresql.conf

# The host-based authentication is controlled by the pg_hba.conf file and defines which users can connect to which database and how they can connect to i
/etc/postgresql/9.4/main/pg_hba.conf

2. MAINTENANCE AND OPERATION

2.1. Basics

2.2. Start , stop , status

You can start , stop or check the status of the database server by issuing the following commands.

# your mileage might vary
sh /etc/init.d/postgresql status
sh /etc/init.d/postgresql stop
sh /etc/init.d/postgresql start

2.2.1. Change to the postgre user

You can access the postgre client from the postgre Linux user

 su - postgres
psql

2.2.2. Connect to a database

Connect to database. List tables. List views

-- list all the tables in the connected db
postgres=# \dt

-- list all the views in the connected db
postgres=# \dl

2.2.3. how-to run a sql script

#Run a SQL script (command line)
psql -f script.sql databaseName

2.3. Provisioning

Provisioning

2.3.1. Roles

2.3.1.1. How-to create a role in postresql

How-to create a role in postresql

CREATE ROLE role_name WITH LOGIN ENCRYPTED PASSWORD 'pwd' CREATEDB;

2.3.1.2. How-to create role with multiple privileges

How-to create a role in postresql

CREATE ROLE role_name_to_create WITH LOGIN ENCRYPTED PASSWORD  'pwd' CREATEDB CREATEROLE REPLICATION SUPERUSER;

2.3.1.3. How-to alter role in postgresql

How-to create a role in postresql

ALTER ROLE role_name_to_change CREATEROLE CREATEDB REPLICATION SUPERUSER;

2.3.1.4. How-to drop a role in postgresql

How-to drop a role in postgresql

DROP ROLE role_name_to_drop

2.3.2. Users

2.3.2.1. How-to create an user in postresql

Official docs @:
http://www.postgresql.org/docs/9.4/static/sql-createuser.html

CREATE USER user_name WITH PASSWORD 'pwd';

-- and verify by listing all the users
select * from pg_shadow;

2.3.2.2. how-to alter user in PostgreSQL

how-to change user password

ALTER USER colin WITH PASSWORD 'letmein';

2.3.2.3. how-to drop user in PostgreSQL

Just like creating users, there are two ways to remove users, using the command line or SQL. At the command line we would use:

 DROP USER user_to_drop ; 

2.3.3. Groups

Groups are entirely optional in postgresql.

2.3.3.1. how-to view groups in PostgreSQL

We can see group membership by viewing the pg_group system table. In this example I’ve added alice back into the sales group.

select * from pg_group ;

2.3.3.2. How-to create group in postresql

Groups are only used to simplify granting and revoking privileges for the db admin, and users do not need to be a member of any group.

CREATE GROUP group_to_add WITH USER existing_user ;

2.3.3.3. how-to add or remove users from groups

You can add or remove users from groups after they have been created using the ALTER GROUP command:

 ALTER GROUP groupname [ADD|DROP] USER username [, ...  ]

2.4. Db backups and restore

# Backup a database (command line)
pg_dump dbName > dbName.sql

# Backup all databases (command line)
pg_dumpall > pgbackup.sql

2.4.1. Priveledges

2.4.1.1. How-to Grant all priveledges to a role on a db

Grant all priveledges to a role on a db

GRANT ALL PRIVILEGES ON DATABASE db_name TO role_to_be_granted ;

2.4.2. how-to list the current settings

psql- c "SELECT current_setting('datestyle');";

2.5. how-to list the current settings

psql- c "SELECT current_setting('datestyle');";

3. DEVELOPMENT

3.1. Objects creation , DDL

3.1.1. How-to create database

How-to create database

-- CREATE DATABASE
DROP DATABASE IF EXISTS dbName ;
CREATE DATABASE dbName;

CREATE DATABASE utf8db WITH OWNER owner_role ENCODING 'UTF8';

3.1.2. how-to create table

how-to create table
Check the datatypes docs:
http://www.postgresql.org/docs/9.4/static/datatype.html

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

-- Add a primary key
ALTER TABLE TableName ADD PRIMARY KEY (id);

3.1.3. how-to create an index

how-to create an index

-- Create an INDEX
CREATE UNIQUE INDEX indexName ON TableName (columnNames);

-- Create a Function
CREATE OR REPLACE FUNCTION month (timestamp) RETURNS integer 
 AS 'SELECT date_part(''month'', $1)::integer;'
LANGUAGE 'sql';

3.1.4. how-to create a function in PostgreSQL

how-to create a function

-- Create a Function
CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
RETURNS text
AS
$$
 SELECT CASE
        WHEN $3 THEN UPPER($1 || ' ' || $2)
        ELSE LOWER($1 || ' ' || $2)
        END;
$$
LANGUAGE SQL IMMUTABLE STRICT;

-- and call it 
SELECT concat_lower_or_upper('Hello', 'World', true);

3.2. DML

3.2.1. Pagination

Pagination , limit

-- The first N records
SELECT columns FROM table LIMIT 10;
-- Pagination
SELECT cols FROM table LIMIT 10 OFFSET 30;

3.2.2. Regular expressions

Regular expressions

  -- Search using a regular expression
SELECT column FROM table WHERE column ~ 'foo.*';

3.2.3. Prepared statements

Prepared statements

 -- Prepared Statements
PREPARE preparedInsert (int, varchar) AS
  INSERT INTO TableName (intColumn, charColumn) VALUES ($1, $2);
EXECUTE preparedInsert (1,'a');
EXECUTE preparedInsert (2,'b');
DEALLOCATE preparedInsert;