PostgreSQL About the Tutorial PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. This tutorial will give you quick start with PostgreSQL and make you comfortable with PostgreSQL programming. Audience This tutorial has been prepared for the beginners to help them understand the basic to advanced concepts related to PostgreSQL Database. Prerequisites Before you start practicing with various types of examples given in this reference, I'm making an assumption that you are already aware about what is database, especially RDBMS and what is a computer programming language. Copyright & Disclaimer  Copyright 2017 by Tutorials Point (I) Pvt. Ltd. All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at [email protected] i PostgreSQL Table of Contents About the Tutorial ............................................................................................................................................ i Audience ........................................................................................................................................................... i Prerequisites ..................................................................................................................................................... i Copyright & Disclaimer ..................................................................................................................................... i Table of Contents ............................................................................................................................................ ii 1. PostgreSQL – Overview ............................................................................................................................. 1 Brief History ..................................................................................................................................................... 1 Key Features of PostgreSQL............................................................................................................................. 2 Procedural Languages Support ........................................................................................................................ 2 2. PostgreSQL – Environment Setup .............................................................................................................. 3 Installing PostgreSQL on Linux/Unix ................................................................................................................ 3 Installing PostgreSQL on Windows .................................................................................................................. 4 Installing PostgreSQL on Mac .......................................................................................................................... 7 3. PostgreSQL – Syntax ............................................................................................................................... 11 The SQL Statement ........................................................................................................................................ 11 PostgreSQL SQL commands ........................................................................................................................... 11 4. PostgreSQL – Data Type .......................................................................................................................... 35 Numeric Types ............................................................................................................................................... 35 Monetary Types ............................................................................................................................................. 36 Character Types ............................................................................................................................................. 36 Binary Data Types .......................................................................................................................................... 37 Date/Time Types ........................................................................................................................................... 37 Boolean Type ................................................................................................................................................. 37 Enumerated Type .......................................................................................................................................... 38 Geometric Type ............................................................................................................................................. 38 Network Address Type .................................................................................................................................. 38 Bit String Type ............................................................................................................................................... 39 Text Search Type ........................................................................................................................................... 39 UUID Type ...................................................................................................................................................... 39 XML Type ....................................................................................................................................................... 39 JSON Type ...................................................................................................................................................... 40 Array Type ..................................................................................................................................................... 40 Composite Types ........................................................................................................................................... 41 Range Types ................................................................................................................................................... 42 Object Identifier Types .................................................................................................................................. 43 Pseudo Types ................................................................................................................................................. 43 5. PostgreSQL – CREATE Database .............................................................................................................. 45 Using createdb Command ............................................................................................................................. 45 6. PostgreSQL – SELECT Database ............................................................................................................... 48 Database SQL Prompt .................................................................................................................................... 48 OS Command Prompt .................................................................................................................................... 49 7. PostgreSQL – DROP Database ................................................................................................................. 50 Using dropdb Command ................................................................................................................................ 51 ii PostgreSQL 8. PostgreSQL – CREATE Table .................................................................................................................... 53 9. PostgreSQL – DROP Table ....................................................................................................................... 55 10. PostgreSQL – Schema .............................................................................................................................. 56 Syntax to Create Table in Schema ................................................................................................................. 56 Syntax to Drop Schema ................................................................................................................................. 57 11. PostgreSQL – INSERT Query .................................................................................................................... 58 12. PostgreSQL – SELECT Query .................................................................................................................... 60 13. PostgreSQL – Operators .......................................................................................................................... 62 PostgreSQL Arithmetic Operators ................................................................................................................. 62 PostgreSQL Comparison Operators ............................................................................................................... 64 PostgreSQL Logical Operators ....................................................................................................................... 66 PostgreSQL Bit String Operators .................................................................................................................... 69 14. PostgreSQL – Expressions........................................................................................................................ 71 PostgreSQL – Boolean Expressions ................................................................................................................ 71 PostgreSQL – Numeric Expression ................................................................................................................. 72 PostgreSQL – Date Expressions ..................................................................................................................... 73 15. PostgreSQL – WHERE Clause ................................................................................................................... 74 16. PostgreSQL – AND & OR Conjunctive Operators ..................................................................................... 79 The AND Operator ......................................................................................................................................... 79 The OR Operator ........................................................................................................................................... 80 17. PostgreSQL – UPDATE Query .................................................................................................................. 82 18. PostgreSQL – DELETE Query .................................................................................................................... 84 19. PostgreSQL – LIKE Clause ........................................................................................................................ 86 20. PostgreSQL – LIMIT Clause ...................................................................................................................... 89 21. PostgreSQL – ORDER BY Clause ............................................................................................................... 91 22. PostgreSQL – GROUP BY ......................................................................................................................... 94 23. PostgreSQL – WITH Clause ...................................................................................................................... 97 Recursive WITH ............................................................................................................................................. 97 24. PostgreSQL – HAVING Clause ................................................................................................................ 101 25. PostgreSQL – DISTINCT Keyword ........................................................................................................... 104 ADVANCED POSTGRESQL ........................................................................................................ 107 26. PostgreSQL – CONSTRAINTS .................................................................................................................. 108 NOT NULL Constraint ................................................................................................................................... 108 UNIQUE Constraint ...................................................................................................................................... 109 iii PostgreSQL PRIMARY KEY Constraint ............................................................................................................................. 109 FOREIGN KEY Constraint .............................................................................................................................. 110 CHECK Constraint ........................................................................................................................................ 111 EXCLUSION Constraint ................................................................................................................................. 111 Dropping Constraints ................................................................................................................................... 112 27. PostgreSQL – JOINS ............................................................................................................................... 113 The CROSS JOIN ........................................................................................................................................... 114 The INNER JOIN ........................................................................................................................................... 115 The LEFT OUTER JOIN .................................................................................................................................. 116 The RIGHT OUTER JOIN ............................................................................................................................... 117 The FULL OUTER JOIN .................................................................................................................................. 117 28. PostgreSQL – UNIONS Clause ................................................................................................................ 119 The UNION ALL Clause ................................................................................................................................. 121 29. PostgreSQL – NULL Values .................................................................................................................... 123 30. PostgreSQL – ALIAS Syntax.................................................................................................................... 126 31. PostgreSQL – TRIGGERS ........................................................................................................................ 129 Listing TRIGGERS .......................................................................................................................................... 132 Dropping TRIGGERS ..................................................................................................................................... 132 32. PostgreSQL – INDEXES .......................................................................................................................... 133 Index Types .................................................................................................................................................. 133 The DROP INDEX Command ........................................................................................................................ 135 When Should Indexes be Avoided? ............................................................................................................. 135 33. PostgreSQL – ALTER TABLE Command ................................................................................................... 136 34. PostgreSQL – TRUNCATE TABLE Command ........................................................................................... 139 35. PostgreSQL – VIEWS .............................................................................................................................. 140 Creating Views ............................................................................................................................................. 140 Dropping Views ........................................................................................................................................... 142 36. PostgreSQL – TRANSACTIONS ............................................................................................................... 143 Transaction Control ..................................................................................................................................... 143 The COMMIT Command .............................................................................................................................. 144 The ROLLBACK Command ............................................................................................................................ 144 37. PostgreSQL – LOCKS .............................................................................................................................. 146 DeadLocks .................................................................................................................................................... 146 Advisory Locks ............................................................................................................................................. 147 38. PostgreSQL – Sub Queries ..................................................................................................................... 148 Subqueries with the SELECT Statement ...................................................................................................... 148 Subqueries with the INSERT Statement ...................................................................................................... 149 Subqueries with the UPDATE Statement ..................................................................................................... 150 Subqueries with the DELETE Statement ...................................................................................................... 151 39. PostgreSQL – AUTO INCREMENT ........................................................................................................... 153 iv PostgreSQL 40. PostgreSQL – PRIVILEGES ...................................................................................................................... 155 41. PostgreSQL – DATE/TIME Functions and Operators .............................................................................. 158 42. PostgreSQL – Functions ......................................................................................................................... 166 43. PostgreSQL – Useful Functions .............................................................................................................. 168 PostgreSQL – COUNT Function .................................................................................................................... 168 PostgreSQL – MAX Function ........................................................................................................................ 169 PostgreSQL – MIN Function ......................................................................................................................... 171 PostgreSQL – AVG Function ......................................................................................................................... 172 PostgreSQL – SUM Function ........................................................................................................................ 173 PostgreSQL – Array Function ....................................................................................................................... 174 PostgreSQL – Numeric Function .................................................................................................................. 175 PostgreSQL – STRING Function .................................................................................................................... 185 POSTGRESQL INTERFACES ....................................................................................................... 197 44. PostgreSQL – C/C++ Interface ............................................................................................................... 198 Installation ................................................................................................................................................... 198 C/C++ Interface APIs .................................................................................................................................... 199 Connecting To Database .............................................................................................................................. 200 Create a Table .............................................................................................................................................. 201 INSERT Operation ........................................................................................................................................ 202 SELECT Operation ........................................................................................................................................ 204 UPDATE Operation ...................................................................................................................................... 206 DELETE Operation ........................................................................................................................................ 208 45. PostgreSQL – JAVA Interface ................................................................................................................. 211 Installation ................................................................................................................................................... 211 Connecting To Database .............................................................................................................................. 211 Create a Table .............................................................................................................................................. 212 INSERT Operation ........................................................................................................................................ 213 SELECT Operation ........................................................................................................................................ 215 UPDATE Operation ...................................................................................................................................... 217 DELETE Operation ........................................................................................................................................ 219 46. PostgreSQL – PHP Interface .................................................................................................................. 222 Installation ................................................................................................................................................... 222 PHP Interface APIs ....................................................................................................................................... 222 Connecting to Database .............................................................................................................................. 224 Create a Table .............................................................................................................................................. 225 INSERT Operation ........................................................................................................................................ 226 SELECT Operation ........................................................................................................................................ 227 UPDATE Operation ...................................................................................................................................... 228 DELETE Operation ........................................................................................................................................ 230 47. PostgreSQL – Perl Interface ................................................................................................................... 233 Installation ................................................................................................................................................... 233 DBI Interface APIs ........................................................................................................................................ 234 Connecting to Database .............................................................................................................................. 235 Create a Table .............................................................................................................................................. 235 v PostgreSQL INSERT Operation ........................................................................................................................................ 236 SELECT Operation ........................................................................................................................................ 237 UPDATE Operation ...................................................................................................................................... 239 DELETE Operation ........................................................................................................................................ 240 48. PostgreSQL – Python Interface .............................................................................................................. 243 Installation ................................................................................................................................................... 243 Python psycopg2 module APIs .................................................................................................................... 243 Connecting to Database .............................................................................................................................. 245 Create a Table .............................................................................................................................................. 245 INSERT Operation ........................................................................................................................................ 246 SELECT Operation ........................................................................................................................................ 247 UPDATE Operation ...................................................................................................................................... 248 DELETE Operation ........................................................................................................................................ 249 vi 1. PostgreSQL – Overview PostgreSQL PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development phase and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. This tutorial will give you a quick start with PostgreSQL and make you comfortable with PostgreSQL programming. What is PostgreSQL? PostgreSQL (pronounced as post-gress-Q-L) is an open source relational database management system (DBMS) developed by a worldwide team of volunteers. PostgreSQL is not controlled by any corporation or other private entity and the source code is available free of charge. A Brief History of PostgreSQL PostgreSQL, originally called Postgres, was created at UCB by a computer science professor named Michael Stonebraker. Stonebraker started Postgres in 1986 as a follow-up project to its predecessor, Ingres, now owned by Computer Associates. 1. 1977-1985: A project called INGRES was developed.  Proof-of-concept for relational databases  Established the company Ingres in 1980  Bought by Computer Associates in 1994 2. 1986-1994: POSTGRES  Development of the concepts in INGRES with a focus on object orientation and the query language - Quel  The code base of INGRES was not used as a basis for POSTGRES  Commercialized as Illustra (bought by Informix, bought by IBM) 3. 1994-1995: Postgres95  Support for SQL was added in 1994  Released as Postgres95 in 1995  Re-released as PostgreSQL 6.0 in 1996  Establishment of the PostgreSQL Global Development Team 7 PostgreSQL Key Features of PostgreSQL PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It supports text, images, sounds, and video, and includes programming interfaces for C / C++, Java, Perl, Python, Ruby, Tcl and Open Database Connectivity (ODBC). PostgreSQL supports a large part of the SQL standard and offers many modern features including the following:  Complex SQL queries  SQL Sub-selects  Foreign keys  Trigger  Views  Transactions  Multiversion concurrency control (MVCC)  Streaming Replication (as of 9.0)  Hot Standby (as of 9.0) You can check official documentation of PostgreSQL to understand the above-mentioned features. PostgreSQL can be extended by the user in many ways. For example by adding new:  Data types  Functions  Operators  Aggregate functions  Index methods Procedural Languages Support PostgreSQL supports four standard procedural languages, which allows the users to write their own code in any of the languages and it can be executed by PostgreSQL database server. These procedural languages are - PL/pgSQL, PL/Tcl, PL/Perl and PL/Python. Besides, other non-standard procedural languages like PL/PHP, PL/V8, PL/Ruby, PL/Java, etc., are also supported. 8 2. PostgreSQL – Environment SetupPo stgreSQL To start understanding the PostgreSQL basics, first let us install the PostgreSQL. This chapter explains about installing the PostgreSQL on Linux, Windows and Mac OS platforms. Installing PostgreSQL on Linux/Unix Follow the given steps to install PostgreSQL on your Linux machine. Make sure you are logged in as root before you proceed for the installation.  Pick the version number of PostgreSQL you want and, as exactly as possible, the platform you want from EnterpriseDB  I downloaded postgresql-9.2.4-1-linux-x64.run for my 64-bit CentOS-6 machine. Now, let us execute it as follows: [root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run [root@host]# ./postgresql-9.2.4-1-linux-x64.run ------------------------------------------------------------------------ Welcome to the PostgreSQL Setup Wizard. ------------------------------------------------------------------------ Please specify the directory where PostgreSQL will be installed. Installation Directory [/opt/PostgreSQL/9.2]:  Once you launch the installer, it asks you a few basic questions like location of the installation, password of the user who will use database, port number, etc. So keep all of them at their default values except password, which you can provide password as per your choice. It will install PostgreSQL at your Linux machine and will display the following message: Please wait while Setup installs PostgreSQL on your computer. Installing 0% ______________ 50% ______________ 100% ######################################### ----------------------------------------------------------------------- Setup has finished installing PostgreSQL on your computer.  Follow the following post-installation steps to create your database: 9