Transcription
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 . 16643. PostgreSQL – Useful Functions . 168PostgreSQL – COUNT Function . 168PostgreSQL – MAX Function . 169PostgreSQL – MIN Function . 171PostgreSQL – AVG Function . 172PostgreSQL – SUM Function . 173PostgreSQL – Array Function . 174PostgreSQL – Numeric Function . 175PostgreSQL – STRING Function . 185POSTGRESQL INTERFACES . 19744. PostgreSQL – C/C Interface . 198Installation . 198C/C Interface APIs . 199Connecting To Database . 200Create a Table . 201INSERT Operation . 202SELECT Operation . 204UPDATE Operation . 206DELETE Operation . 20845. PostgreSQL – JAVA Interface . 211Installation . 211Connecting To Database . 211Create a Table . 212INSERT Operation . 213SELECT Operation . 215UPDATE Operation . 217DELETE Operation . 21946. PostgreSQL – PHP Interface . 222Installation . 222PHP Interface APIs . 222Connecting to Database . 224Create a Table . 225INSERT Operation . 226SELECT Operation . 227UPDATE Operation . 228DELETE Operation . 23047. PostgreSQL – Perl Interface . 233Installation . 233DBI Interface APIs . 234Connecting to Database . 235Create a Table . 235v
PostgreSQLINSERT Operation . 236SELECT Operation . 237UPDATE Operation . 239DELETE Operation . 24048. PostgreSQL – Python Interface . 243Installation . 243Python psycopg2 module APIs . 243Connecting to Database . 245Create a Table . 245INSERT Operation . 246SELECT Operation . 247UPDATE Operation . 248DELETE Operation . 249vi
1. PostgreSQL – OverviewPostgreSQLPostgreSQL is a powerful, open source object-relational database system. It has more than15 years of active development phase and a proven architecture that has earned it a strongreputation for reliability, data integrity, and correctness.This tutorial will give you a quick start with PostgreSQL and make you comfortable withPostgreSQL programming.What is PostgreSQL?PostgreSQL (pronounced as post-gress-Q-L) is an open source relational databasemanagement system (DBMS) developed by a worldwide team of volunteers. PostgreSQL isnot controlled by any corporation or other private entity and the source code is available freeof charge.A Brief History of PostgreSQLPostgreSQL, originally called Postgres, was created at UCB by a computer science professornamed Michael Stonebraker. Stonebraker started Postgres in 1986 as a follow-up project toits 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 19942. 1986-1994: POSTGRES Development of the concepts in INGRES with a focus on object orientation and thequery 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 Team7
PostgreSQLKey Features of Po
PostgreSQL i About the Tutorial PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active developm
