PostgreSQL - Tutorialspoint

PostgreSQLAbout the TutorialPostgreSQL is a powerful, open source object-relational database system. It has more than15 years of active development and a proven architecture that has earned it a strongreputation 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 withPostgreSQL programming.AudienceThis tutorial has been prepared for the beginners to help them understand the basic toadvanced concepts related to PostgreSQL Database.PrerequisitesBefore you start practicing with various types of examples given in this reference, I'mmaking an assumption that you are already aware about what is database, especiallyRDBMS 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 republishany contents or a part of contents of this e-book in any manner without written consentof the publisher.We strive to update the contents of our website and tutorials as timely and as precisely aspossible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt.Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of ourwebsite or its contents including this tutorial. If you discover any errors on our website orin this tutorial, please notify us at contact@tutorialspoint.comi

PostgreSQLTable of ContentsAbout the Tutorial . iAudience . iPrerequisites . iCopyright & Disclaimer . iTable of Contents . ii1.PostgreSQL – Overview . 1Brief History . 1Key Features of PostgreSQL. 2Procedural Languages Support . 22.PostgreSQL – Environment Setup . 3Installing PostgreSQL on Linux/Unix . 3Installing PostgreSQL on Windows . 4Installing PostgreSQL on Mac . 73.PostgreSQL – Syntax . 11The SQL Statement . 11PostgreSQL SQL commands . 114.PostgreSQL – Data Type . 35Numeric Types . 35Monetary Types . 36Character Types . 36Binary Data Types . 37Date/Time Types . 37Boolean Type . 37Enumerated Type . 38Geometric Type . 38Network Address Type . 38Bit String Type . 39Text Search Type . 39UUID Type. 39XML Type . 39JSON Type . 40Array Type . 40Composite Types . 41Range Types. 42Object Identifier Types . 43Pseudo Types . 435.PostgreSQL – CREATE Database . 45Using createdb Command . 456.PostgreSQL – SELECT Database . 48Database SQL Prompt . 48OS Command Prompt . 497.PostgreSQL – DROP Database . 50Using dropdb Command . 51ii

PostgreSQL8.PostgreSQL – CREATE Table . 539.PostgreSQL – DROP Table . 5510. PostgreSQL – Schema . 56Syntax to Create Table in Schema . 56Syntax to Drop Schema . 5711. PostgreSQL – INSERT Query . 5812. PostgreSQL – SELECT Query . 6013. PostgreSQL – Operators . 62PostgreSQL Arithmetic Operators . 62PostgreSQL Comparison Operators . 64PostgreSQL Logical Operators . 66PostgreSQL Bit String Operators. 6914. PostgreSQL – Expressions. 71PostgreSQL – Boolean Expressions . 71PostgreSQL – Numeric Expression . 72PostgreSQL – Date Expressions . 7315. PostgreSQL – WHERE Clause . 7416. PostgreSQL – AND & OR Conjunctive Operators . 79The AND Operator . 79The OR Operator . 8017. PostgreSQL – UPDATE Query . 8218. PostgreSQL – DELETE Query . 8419. PostgreSQL – LIKE Clause . 8620. PostgreSQL – LIMIT Clause . 8921. PostgreSQL – ORDER BY Clause . 9122. PostgreSQL – GROUP BY . 9423. PostgreSQL – WITH Clause . 97Recursive WITH . 9724. PostgreSQL – HAVING Clause . 10125. PostgreSQL – DISTINCT Keyword. 104ADVANCED POSTGRESQL . 10726. PostgreSQL – CONSTRAINTS. 108NOT NULL Constraint . 108UNIQUE Constraint . 109iii

PostgreSQLPRIMARY KEY Constraint . 109FOREIGN KEY Constraint . 110CHECK Constraint . 111EXCLUSION Constraint . 111Dropping Constraints . 11227. PostgreSQL – JOINS . 113The CROSS JOIN . 114The INNER JOIN . 115The LEFT OUTER JOIN . 116The RIGHT OUTER JOIN . 117The FULL OUTER JOIN . 11728. PostgreSQL – UNIONS Clause . 119The UNION ALL Clause . 12129. PostgreSQL – NULL Values . 12330. PostgreSQL – ALIAS Syntax. 12631. PostgreSQL – TRIGGERS . 129Listing TRIGGERS. 132Dropping TRIGGERS . 13232. PostgreSQL – INDEXES . 133Index Types . 133The DROP INDEX Command . 135When Should Indexes be Avoided? . 13533. PostgreSQL – ALTER TABLE Command. 13634. PostgreSQL – TRUNCATE TABLE Command . 13935. PostgreSQL – VIEWS . 140Creating Views . 140Dropping Views . 14236. PostgreSQL – TRANSACTIONS . 143Transaction Control . 143The COMMIT Command . 144The ROLLBACK Command . 14437. PostgreSQL – LOCKS . 146DeadLocks . 146Advisory Locks . 14738. PostgreSQL – Sub Queries . 148Subqueries with the SELECT Statement . 148Subqueries with the INSERT Statement . 149Subqueries with the UPDATE Statement . 150Subqueries with the DELETE Statement . 15139. PostgreSQL – AUTO INCREMENT . 153iv

PostgreSQL40. PostgreSQL – PRIVILEGES . 15541. PostgreSQL – DATE/TIME Functions and Operators . 15842. PostgreSQL – Functions .

29. PostgreSQL – NULL Values . Streaming Replication (as of 9.0) Hot Standby (as of 9.0) . 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