Rapid POSTGRESQL learning, PART-1 By: ali masudianpour [email protected] Rapid POSTGRESQL learning. 1 

Introduction In this manual, my assumption is that reader knows basic concepts of database topic. Rapid POSTGRESQL learning. 2 

Introduction Postgresql Powerfull OOP relational DBMS Large community Open Source All major Operating Systems such as Linux/ Windows/ Solaris/ Mac and so on… Started in 1995 Latest version at this moment (June 2013) is 9.2 Rapid POSTGRESQL learning. 3 

Features Supports Procedure languages [pl, pgsql, pljava, plphp, plperl] Supports multi-version concurrency control (MCC or MVCC) – More details on: https://en.wikipedia.org/wiki/Multiversion_concurrency_control Supports variety of DATA TYPES including INTEGER, CHARARCTER, BOOLEAN, DATE, INTERVAL, TIMESTAMP Allows users to define their own objects such as: – DATA TYPES – FUNCTIONS Supports data integrity such as PK, FK, Restrictions, Cascading and Constraint Supports binary objects such as pictures, sounds, videos Open source and free Main users: – Yahoo, Skype, imtv.com and so on… Rapid POSTGRESQL learning. 4 

Installation Postgresql is available to download on http://postgresql.org . So you can download it from the mentioned internet address. (the latest one at writing this document is 9.2) On my assumption you have an Ubuntu distribution installed on your computer, so to install Postgresql we can use the following statement: – Sudo apt-get install postgres-9.2 After all processes you can enter to postrgresql shell using the following command: – Sudo –u postgres psql postgres ● Note that postgres user is the super user of our installed postgresql – After above command you are logged in to postgresql command line. Rapid POSTGRESQL learning. 5 

Start Up Commands Basic settings and commands would be like the following lines: CREATE DATABASE – To create Database in postgresql we use the following command ● CREATE DATABASE [database name]; – Example: CREATE DATABASE testName; – Or you may be not in postgresql command line, so creating database will be accomplished with the following statement: ● Sudo –u postgres created [table name] – For example: sudo –u postgres created testName CREATE USER – To create user in postgres, we use the following command: ● CREATE USER [username] WITH PASSWORD [password]; – For instance: CREATE USER masud WITH PASSWORD ‘masudpassword’; – Other way is outside of postgres command line: ● Sudo –u postgres createuser ‘masud’ Rapid POSTGRESQL learning. 6 

Start Up Commands DROP USER – TO drop user the first way would be like below in postgresql command line: ● DROP USER [username]; – For example: DROP USER masud; – The second way is outside of postgres command line: ● Sudo –u postgres dropuser ‘username’ – For example: sudo –u postgres dropuser ‘masud’ DROP DATABASE – To drop a database first way would be like bellow in postgresql command line: ● DROP DATABASE [database name] – for example: DROP DATABASE ‘testName’; – The second way is outside of postgres command line: ● Sudo –u postgres dropdb [db name] – For example: sudo –u postgres dropdb ‘testName’ Rapid POSTGRESQL learning. 7 

Start Up Commands CHANGE PASSWORD OF USER – We can do it by: ● Way1: In psql shell type : \password username – Example: \password masud ● Way2: in psql shell type: ALTER USER [username] WITH PASSWORD [new password] – Example: ALTER USER masud WITH PASSWORD ‘masudsnewPassword’ Rapid POSTGRESQL learning. 8 

Manage POSTGRESQL Service In order to handle postgresql services we can use the following commands to [start/ stop/ restart] postgresql service: – Sudo service postgresql start – Sudo service postgresql stop – Sudo service postgresql restart Rapid POSTGRESQL learning. 9 

Important Commands in POSTGRESQL There are some short commands in postgresql that each one performs an specific task. In continue I will introduce you some of them: – \i ● Imports a dump into database – \l ● List of databases – \d ● List of tables – \dt ● List of tables and relations – \h ● help – \? ● help – \df ● List of functions Rapid POSTGRESQL learning. 10 

Important Commands in POSTGRESQL – \df+ ● Same \df but with source – \timing ● Turn on/off timing – Timing shows execution time of a query – \password ● Change password – \q ● Quite – \c ● Change database connection [switch between databases] – \dp ● Access privileges – \conninfo ● Current Connection Info – \e ● Execute command Rapid POSTGRESQL learning. 11 

Important Commands in POSTGRESQL – \pset format [html/ aligned/ unaligned/ wrapped/ html/ latex] ● Changes the output format – For instance if we change output format to HTML, any result after that time will be shown in HTML format – \pset border [0/1/2] ● Set border of result [ for instance border around result table] – 0: none border – 1: normal border – 2: whole table has a border – \pset null null ● It will write null instead of nothing when found a null value in a query – In order to find out which version of postgres we are using we can use the following command: ● SELECT VERSION(); Rapid POSTGRESQL learning. 12 

Keywords and Identifiers – Keywords are words that are available in SQL, for instance: ● CREATE ● DATABASE ● ORDER – Identifiers are used to identify objects such as: ● TABLE ● COLUMN – Note that keywords can not be used as identifiers – Identifiers can be up to 63 characters – Identifiers and Keywords are case insensitive Rapid POSTGRESQL learning. 13 

Comments – In order to comment one line in postgresql we can use -- (two-dash signs) ● --this is a comment in psql – For multiline commands we use /* */ ● /* This is a multiline command in postgresql postgresql or postgres or psql are same */ Rapid POSTGRESQL learning. 14 

Data Types Data types in postgresql – INTEGER ● Including 3 size variations: – 2bytes (smallint) – 4bytes (integer) – 8bytes(bigint) – SERIAL ● it is used to create unique identifier columns ● It will generate a sequence automatically – NUMERIC ● Stores large numbers that need exact calculations Rapid POSTGRESQL learning. 15 

Data Types – FLOATING POINT ● REAL ● DOUBLE This data types accepts some special non-numeric values such as: Infinity -Infinity NaN(Not a Number) – CHAR – VARCHAR ● Varying character – TEXT ● Stores strings of any length Rapid POSTGRESQL learning. 16 

Data Types – DATE ● Dates must be between single quotes – TIME – TIMESTAMP ● Without time zone ● With time zone – INTERVAL ● POSTGRES SUPPORTS THE FOLLOWING SPECIAL VALUES: – Today – Tomorrow – Yesterday – Infinity – -infinity Rapid POSTGRESQL learning. 17 

Date Style in Postgresql Postgresql supports 3 different DATESTYLE containing: – ISO – SQL – POSTGRES We can change DATESTYLE with the following statements: – SET DATESTYLE TO ISO,MDY ● Which means ISO and MONTH - DAY – YEAR – SET DATESTYLE TO SQL, MDY – SET DATESTYLE TO POSTGRES,MDY Rapid POSTGRESQL learning. 18 

INTERVALS Intervals represent a duration of a time Unites in interval include: – DAY – HOUR – MINUTE – SECOND – YEAR TO MONTH – DAY TO HOUR – DAY TO MINUTE – DAY TO SECOND – HOUR TO MINUTE – HOUR TO SECOND – MINUTE TO SECOND – … Rapid POSTGRESQL learning. 19 

INTERVALS – Example: ● SET INTERVALSTYLE TO sql_standard – Now try to type: SELECT INTERVAL ‘1 14:13:15’; ● RESULT: 1 14:13:15 ● SET INTERVALSTYLE TO postgres; – Now try to type: SELECT INTERVAL ‘1 14:13:15’; ● RESULT: 1 day 14:13:15 ● SET INTERVALSTYLE TO iso_8601 – Now try to type: SELECT INTERVAL ‘1 14:13:15’; ● RESULT: P1 DT 14H13M15S Rapid POSTGRESQL learning. 20 

Boolean Data Type It holds three values – True – False – Unknown – TRUE [can be one of the following values] ● T ● True ● Yes ● Y ● On ● 1 – False [can be one of the following values] ● False ● F ● NO ● Off ● 0 All Values except true and false should be between single quotes. Rapid POSTGRESQL learning. 21 

Enumerated Data Types Enums are not predefined in postgresql, this means that Postgresql does not officially supports mysql’s ENUM data type. In order to achieve an Enumeration data type we must create our own. CREATE ENUM – CREATE TYPE [type name] AS ENUM([data array]) ● For example: CREATE TYPE colors AS ENUM(‘red’,’blue’,’green’); HOW TO USE – Just like another data types ● If an integer is: INTEGER variable name -> it would be: colors color ● [ENUM NAME] [variable name] Notes: – Enums are case sensitive – Sort is important in enums, for instance we must start with smallest values to the largest. For instance it is better to use ENUM(‘red’,’blue’,’green’) instead of ENUM(‘green’,’red’,’blue’) Rapid POSTGRESQL learning. 22 

GEOMETRIC Data Types These data types are used to represent two dimensional objects ● Objects can be point, box, line, polygon, circle, path – POINT ● To define a point (x,y) – Example: (‘2,3’) or (‘(2,3)’) – LINE ● To define a line using 2 points – Example: ((x1,y1),(x2,y2)) – PATH ● Represented by lists of connected points – Example: (‘(2,3,4,5)’) or (‘[2,3,4,5]’) – CIRCLE ● For a circle – Example: (‘2,3,4’) -> in this example 4 is radius – BOX ● To define a rectangle for instance : ((x1,y1),(x2,y2)) Rapid POSTGRESQL learning. 23 

Arrays To define an array we add brackets after data type or we can use from array keyword. – Test INTEGER[] – Test INTEGER[][] – Test INTEGER ARRAY – In insert mode: ● In one-dimension: (‘{1,2,3,4,5,6,7}’) ● In two-dimension: (‘{1,2,3,4},{5,6,7,8}’) Rapid POSTGRESQL learning. 24 

End of part 1 This is the end of part 1 – In the next part following topics will be covered ● Creating tables ● PRIMARY and FORIGN Keys ● Check Constraint ● NOT NULL Constraint ● UNIQUE Constraint ● DEFAULT values ● CASCADE ● CRUD – INSERT – UPDATE – DELETE – DELETE CASCADE – ON DELETE CASCADE – ON UPDATE CASCADE ● TRUNCATE ● QUERIES ● … Rapid POSTGRESQL learning. 25