Kifaru Codes

Postgres is an opensource relational database system that is widely used in database management. In this section I document the major concepts that I have learnt about database management with postgres, and the projects that I have implemented with this technology.

This shall be a continuously developed documentation that I shall by building progressively. It is meant to be a personal notebook, documenting my technical development, as well as a learning resource for anyone interested.

(back to table of content)

Linux (Ubuntu):

If you are not sure, you can check whether you have an installed version of postgres on your system with the below commands:


					dpkg -L postgresql

					# or

					dpkg -s postgresql

If not installed, first Update and upgrade the system:

sudo apt update && sudo apt upgrade

Install the postgresql package:

				
				sudo apt install postgresql

Once installed you can check the version using the pg_config command or the dpkg command as below.

				
				pg_config --version

				# or

				dpkg -l postgresql

You can also check the version from the postgres terminal by entering:

SELECT version();

When postgres is installed, a linux user and a database user called 'postgres' are created. We can therefore connect to the postgres cli using the 'postgres' user as below.

				
				sudo -u postgres psql

				# alternatively, connect to the postgres linux user 
				# then run the psql command

				sudo su - postgres
				psql

Note:

The postgres user has no password, so you can connect to the postgresql cli directly without being prompted for one.

Windows :

- download the installer from the below link and go through the installation process.

https://www.postgresql.org/download/windows/

(back to table of content)

\q : exit
\l : view list of all databases (show databases)
\c database : Connect to database (use db)
\du : view database users ie list all roles
\du+ : view database users along with details.
\d \dt : show tables
\d table : describe table (desc table)
\dt table : show table details

(back to table of content)

granting privileges to db user


					GRANT ALL PRIVILEGES ON DATABASE "database" TO user;

Creating a database

CREATE DATABASE database;

Deleting a database

DROP DATABASE database;

A database will not be dropped/deleted if the database has active sessions connected to it.

To drop a database with active sessions, you can revoke access to the database with the below command:

REVOKE CONNECTION ON DATABASE database_name    FROM PUBLIC;

view database connection information Create a table (eg person)

/conninfo

(back to table of content)

Create table in database

CREATE TABLE person(
					    id int,
					    fname VARCHAR(100),
					    lname VARCHAR(100),
					    date_of_birth TIMESTAMP,
					)

another example:

create table if not exists person(
					    id int primary key,
					    fname varchar(100) not null,
					    lname varchar(100) not null,
					    birthdate date,
					    email varchar(50) unique
					);

Create table from another table:

 create table employee
					 as
					 select * from person

The above will create a table employee with the same attributes as person. It will create the same column names and data types as the person table

You can create a table from another table with specific tables as below:

create table employee2
					as 
					select id, fname from person;

to specity differant names, put the query as below:

create table employee3 (employee_id, employee_fname)
					as
					select id, fname from employee2

to avoid errors during creation, you can add the if not exists to the query

create table if not exists employee3 (employee_id, employee_fname)
					as
					select id, fname from employee2

(back to table of content)

A temporary table is a table that lasts only in the duration of the session. One you log out of the db session, it is deleted.

When you change databases, the temporary table is also deleted.

create temporary table temp_people (
					    id int,
					    fname varchar(10),
					    lname varchar(10),
					    birthdate date
					)

(back to table of content)

--- create/copy new table
					CREATE TABLE new_table_name 
					AS 
					TABLE existing_table;

					--- create copy of table without the data
					CREATE TABLE new_table_name 
					AS
					TABLE existing_table
					[WITH NO DATA];

					-- create new table with conditions
					CREATE TABLE new_table_name 
					AS
					SELECT * FROM existing_table
					WHERE conditions;

example

CREATE TABLE EMPLOYEE 
					AS
					TABLE PERSON;

					CREATE TABLE PERSON_BKP 
					AS
					TABLE PERSON
					WITH NO DATA;

(back to table of content)

The below script deletes a database.

DROP TABLE IF EXISTS employee;

A database will not be deleted if it is being used in another table or views.

eg below, the department table is being referenced by the employee table, you can not frop the table.

CREATE TABLE employee(
						emp_id INT PRIMARY KEY,
						first_name VARCHAR(50) NOT NULL,
						last_name VARCHAR(50) NOT NULL,
						gender CHAR(1),
						dept_id INT NOT NULL,
						    
					    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
					);
						
					-- the department table is being referenced by the employee table using the foreign key
					CREATE TABLE department (
						dept_id INT PRIMARY KEY,
						dept_name VARCHAR(100) NOT NULL
					);

To delete a table that is being referenced, use the CASCADE parameter.

now the department table can be deleted with the script below:

DROP TABLE  department CASCADE;

You can delete multiple tables by seperating them with a comma:

DROP TABLE IF EXISTS table1, table2;

(back to table of content)

Truncate is used to delete data from a table.

TRUNCATE TABLE table_name;

DROP can be used as well, however TRUNCATE is better as it: 1. can delete huge data-sets. 2. is faster than DROP

You can use RESTART IDENTITY to delete all data and reset the sequence linked to the id column

TRUNCATE TABLE table_name RESTART IDENTITY;

If data is linked to another table, the truncate command shall not delete data. You can use CASCADE delete the data from the table and the data linked to the other table.

TRUNCATE TABLE table_name CASCADE;

(back to table of content)

The ALTER TABLE statement changes the structure of a table.

example of the table below:

create table employee (
					    id int primary key not null,
					    first_name character varying(50) not null,
					    last_name character varying(50) not null,
					    gender character(1),
					    birthdate date,
					    salary integer not null
					)

Add a column to a table:

alter table employee add column new_col varchar(50);

Delete column from table:

alter table employee drop column col cascade;

Change data type of a column

alter table employee alter column type int

Rename a column:

alter table table_name rename column old_name to new_name

					--- example
					alter table employee rename column birthdate to dob;

Rename a table

alter table table_name rename to new_table_name;

					-- example
					alter table employee rename to employee_new;

Add a not null constraint to a column:

alter table table_name alter column column_name set not null

					-- example
					alter table employee alter column dob set not null

remove/drop not null constraint

alter table table_name alter column column_name drop not null

					-- example
					alter table employee alter column dob drop not null

add a check constraint to a table and all its children (revisit):

ALTER TABLE employee ADD CONSTRAINT first_name CHECK (char_length(first_name) > 1);

remove a check constraint from a table and all its children (revisit):

ALTER TABLE employee DROP CONSTRAINT first_name;

					ALTER TABLE ONLY employee DROP CONSTRAINT first_name; -- remove check constraint from one table only

Add primary key to table:

ALTER TABLE employee ADD PRIMARY KEY (id);

move a table to a different tablespace:

ALTER TABLE employee SET TABLESPACE emptablespace;

move a table to a different schema:

ALTER TABLE myschema.employee SET SCHEMA yourschema;

(back to table of content)

To insert data to table use the below statement:

insert into table_name (col1, col2, col3)
					values
					("value 1", "value 2", "value3");

					-- example
					insert into employee 
					(id, first_name, second_name, gender, dob, salaries)
					values
					(1, 'Tony', 'Stark', 'm', DATE '1964-03-10', 100000)

note: - use single quotes ' when inserting strings. double quotes " will generate errors - Dates are inserted in the YYYY-MM-DD format ie DATE '1995-03-12' - If the table has a SERIAL column, Postgres will automatically generate a sequence number for the serial column. No need to specify a value for that column in the INSERT statement. - returning returns the inserted data after the the insert quert has been executed

-- returns all values after insertion
					insert into table (col1, col2)
					values
					('col 1', 'col 2')
					returning *;  

					-- return specific column after insertion
					insert into table (col1, col2)
					values
					('col 1', 'col 2')
					returning col1;

(back to table of content)

Upsert is an Insert/update operation, i.e. It inserts values, however if the data already exists, it is updated.

The upsert operation is implemented with the on conflict clause i.e. if there is conflict, do something.

eg you have the below table:

CREATE TABLE employee (
					    emp_id 	INT PRIMARY KEY,
					    first_name VARCHAR(50) NOT NULL,
					    last_name VARCHAR(50) NOT NULL,
					    gender CHAR(1),
					    birthdate DATE,
					    email VARCHAR(100) UNIQUE,
					    salary INT
					);

Insert data

INSERT INTO employee
					VALUES
					(1,'Annie','Smith','F', DATE '1988-01-09', 'ani@email.com',5000)
					returning *;

rerunning the same command above returns an error since we have a column with a primary id, and we can not repeat the same data.

on conflict do nothing

INSERT INTO employee
					VALUES
					(1,'Annie','Smith','F', DATE '1988-01-09', 'ani@email.com',5000)
					on conflict do nothing;

on conflict do update

on conflict where

(back to table of content)

How to Create a Postgres User

PostgreSQL Basics

Overview

Table of content

Installation:

PSQL Commands

SQL commands

Create database

Create table

Create temporary table

Copy a table

Drop table

Truncate Table

Alter Table

Insert Data

Upsert Data

on conflict do nothing

on conflict do update

on conflict where

Update Data

Delete Data

Select Statement

Select Where

Select Group by

References