Movatterモバイル変換

PostgreSQLiAbouttheTutorialPostgreSQL 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.com

PostgreSQLiiTableofContentsAbout 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................................................................................................................................51

PostgreSQLiii8. 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......................................................................................................................................109

PostgreSQLivPRIMARY 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...........................................................................................................153

PostgreSQLv40. 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..............................................................................................................................................235

PostgreSQLviINSERT 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........................................................................................................................................249

PostgreSQL1PostgreSQL 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.WhatisPostgreSQL?PostgreSQL (pronounced as post-gress-Q-L) is an open source relational databasemanagement system (DBMS) developed by a worldwide team of volunteers. PostgreSQLis not controlled by any corporation or other private entity and the source code is availablefree of charge.A Brief History of PostgreSQLPostgreSQL, originally called Postgres, was created at UCB by a computer scienceprofessor 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 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 Team1. PostgreSQL – Overview

PostgreSQL2KeyFeaturesofPostgreSQLPostgreSQL 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, andvideo, and includes programming interfaces for C / C++, Java, Perl, Python, Ruby, Tcl andOpen Database Connectivity (ODBC).PostgreSQL supports a large part of the SQL standard and offers many modern featuresincluding 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-mentionedfeatures. PostgreSQL can be extended by the user in many ways. For example by addingnew: Data types Functions Operators Aggregate functions Index methodsProceduralLanguagesSupportPostgreSQL supports four standard procedural languages, which allows the users to writetheir own code in any of the languages and it can be executed by PostgreSQL databaseserver. 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.

PostgreSQL3To start understanding the PostgreSQL basics, first let us install the PostgreSQL. Thischapter explains about installing the PostgreSQL on Linux, Windows and Mac OS platforms.InstallingPostgreSQLonLinux/UnixFollow the given steps to install PostgreSQL on your Linux machine. Make sure you arelogged in as root before you proceed for the installation. Pick the version number of PostgreSQL you want and, as exactly as possible, theplatform you want from EnterpriseDB I downloaded postgresql-9.2.4-1-linux-x64.run for my 64-bit CentOS-6machine. 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 theinstallation, password of the user who will use database, port number, etc. So keepall of them at their default values except password, which you can provide passwordas per your choice. It will install PostgreSQL at your Linux machine and will displaythe following message:Please wait while Setup installs PostgreSQL on your computer.Installing0% ______________ 50% ______________ 100%#########################################-----------------------------------------------------------------------Setup has finished installing PostgreSQL on your computer. Follow the following post-installation steps to create your database:[root@host]# su - postgresPassword:2. PostgreSQL – Environment Setup

PostgreSQL4bash-4.1$ createdb testdbbash-4.1$ psql testdbpsql (8.4.13, server 9.2.4)test=# You can start/restart postgres server in case it is not running, using the followingcommand:[root@host]# service postgresql restartStopping postgresql service: [ OK ]Starting postgresql service: [ OK ] If your installation was correct, you will have PotsgreSQL prompt test=# as shownabove.InstallingPostgreSQLonWindowsFollow the given steps to install PostgreSQL on your Windows machine. Make sure youhave turned Third Party Antivirus off while installing. Pick the version number of PostgreSQL you want and, as exactly as possible, theplatform you want from EnterpriseDB I downloaded postgresql-9.2.4-1-windows.exe for my Windows PC running in 32-bit mode, so let us run postgresql-9.2.4-1-windows.exe as administrator toinstall PostgreSQL. Select the location where you want to install it. By default, it isinstalled within Program Files folder.

PostgreSQL5 The next step of the installation process would be to select the directory whereyour data would be stored. By default, it is stored under the "data" directory. Next, the setup asks for password, so you can use your favorite password.

PostgreSQL6 The next step; keep the port as default. In the next step, when asked for "Locale", I selected "English, United States".

PostgreSQL7 It takes a while to install PostgreSQL on your system. On completion of theinstallation process, you will get the following screen. Uncheck the checkbox andclick the Finish button.After the installation process is completed, you can access pgAdmin III, StackBuilder andPostgreSQL shell from your Program Menu under PostgreSQL 9.2.InstallingPostgreSQLonMacFollow the given steps to install PostgreSQL on your Mac machine. Make sure you arelogged in as administrator before you proceed for the installation. Pick the latest version number of PostgreSQL for Mac OS available at EnterpriseDB I downloaded postgresql-9.2.4-1-osx.dmg for my Mac OS running with OS Xversion 10.8.3. Now, let us open the dmg image in finder and just double click it,which will give you PostgreSQL installer in the following window:

PostgreSQL8 Next, click the postgres-9.2.4-1-osx icon, which will give a warning message.Accept the warning and proceed for further installation. It will ask for theadministrator password as seen in the following window: Enter the password, proceed for the installation, and after this step, restart yourMac machine. If you do not see the following window, start your installation onceagain.

PostgreSQL9 Once you launch the installer, it asks you a few basic questions like location of theinstallation, password of the user who will use database, port number etc.Therefore, keep all of them at their default values except the password, which youcan provide as per your choice. It will install PostgreSQL in your Mac machine inthe Application folder which you can check: Now, you can launch any of the program to start with. Let us start with SQL Shell.When you launch SQL Shell, just use all the default values it displays except, enteryour password, which you had selected at the time of installation. If everything

PostgreSQL10goes fine, then you will be inside postgres database and a postgress# prompt willbe displayed as shown below:Congratulations!!! Now you have your environment ready to start with PostgreSQLdatabase programming.

PostgreSQL11This chapter provides a list of the PostgreSQL SQL commands, followed by the precisesyntax rules for each of these commands. This set of commands is taken from the psqlcommand-line tool. Now that you have Postgres installed, open the psql as:Program Files > PostgreSQL 9.2 > SQL Shell(psql).Using psql, you can generate a complete list of commands by using the help command.For the syntax of a specific command, use the following command:postgres-# help <command_name>TheSQLStatementAn SQL statement is comprised of tokens where each token can represent either akeyword, identifier, quoted identifier, constant, or special character symbol. The tablegiven below uses a simple SELECT statement to illustrate a basic, but complete, SQLstatement and its components.SELECT id, name FROM statesToken Type Keyword Identifiers Keyword IdentifierDescription Command Id and name columns Clause Table namePostgreSQLSQLcommandsABORTAbort the current transaction.ABORT [ WORK | TRANSACTION ]ALTER AGGREGATEChange the definition of an aggregate function.ALTER AGGREGATE name ( type ) RENAME TO new_nameALTER AGGREGATE name ( type ) OWNER TO new_owner3. PostgreSQL – Syntax

PostgreSQL12ALTER CONVERSIONChange the definition of a conversion.ALTER CONVERSION name RENAME TO new_nameALTER CONVERSION name OWNER TO new_ownerALTER DATABASEChange a database specific parameter.ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }ALTER DATABASE name RESET parameterALTER DATABASE name RENAME TO new_nameALTER DATABASE name OWNER TO new_ownerALTER DOMAINChange the definition of a domain specific parameter.ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT }ALTER DOMAIN name { SET | DROP } NOT NULLALTER DOMAIN name ADD domain_constraintALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]ALTER DOMAIN name OWNER TO new_ownerALTER FUNCTIONChange the definition of a function.ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_nameALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_ownerALTER GROUPChange a user group.ALTER GROUP groupname ADD USER username [, ... ]ALTER GROUP groupname DROP USER username [, ... ]ALTER GROUP groupname RENAME TO new_name

PostgreSQL13ALTER INDEXChange the definition of an index.ALTER INDEX name OWNER TO new_ownerALTER INDEX name SET TABLESPACE indexspace_nameALTER INDEX name RENAME TO new_nameALTER LANGUAGEChange the definition of a procedural language.ALTER LANGUAGE name RENAME TO new_nameALTER OPERATORChange the definition of an operator.ALTER OPERATOR name ( { lefttype | NONE } , { righttype | NONE } )OWNER TO new_ownerALTER OPERATOR CLASSChange the definition of an operator class.ALTER OPERATOR CLASS name USING index_method RENAME TO new_nameALTER OPERATOR CLASS name USING index_method OWNER TO new_ownerALTER SCHEMAChange the definition of a schema.ALTER SCHEMA name RENAME TO new_nameALTER SCHEMA name OWNER TO new_ownerALTER SEQUENCEChange the definition of a sequence generator.ALTER SEQUENCE name [ INCREMENT [ BY ] increment ][ MINVALUE minvalue | NO MINVALUE ][ MAXVALUE maxvalue | NO MAXVALUE ][ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

PostgreSQL14ALTER TABLEChange the definition of a table.ALTER TABLE [ ONLY ] name [ * ]action [, ... ]ALTER TABLE [ ONLY ] name [ * ]RENAME [ COLUMN ] column TO new_columnALTER TABLE nameRENAME TO new_nameWhere action is one of the following lines:ADD [ COLUMN ] column_type [ column_constraint [ ... ] ]DROP [ COLUMN ] column [ RESTRICT | CASCADE ]ALTER [ COLUMN ] column TYPE type [ USING expression ]ALTER [ COLUMN ] column SET DEFAULT expressionALTER [ COLUMN ] column DROP DEFAULTALTER [ COLUMN ] column { SET | DROP } NOT NULLALTER [ COLUMN ] column SET STATISTICS integerALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }ADD table_constraintDROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]CLUSTER ON index_nameSET WITHOUT CLUSTERSET WITHOUT OIDSOWNER TO new_ownerSET TABLESPACE tablespace_nameALTER TABLESPACEChange the definition of a tablespace.ALTER TABLESPACE name RENAME TO new_nameALTER TABLESPACE name OWNER TO new_ownerALTER TRIGGERChange the definition of a trigger.ALTER TRIGGER name ON table RENAME TO new_name

PostgreSQL15ALTER TYPEChange the definition of a type.ALTER TYPE name OWNER TO new_ownerALTER USERChange a database user account.ALTER USER name [ [ WITH ] option [ ... ] ]ALTER USER name RENAME TO new_nameALTER USER name SET parameter { TO | = } { value | DEFAULT }ALTER USER name RESET parameterWhere option can be:[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'| CREATEDB | NOCREATEDB| CREATEUSER | NOCREATEUSER| VALID UNTIL 'abstime'ANALYZECollect statistics about a database.ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]BEGINStart a transaction block.BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]Where transaction_mode is one of:ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED| READ UNCOMMITTED }READ WRITE | READ ONLYCHECKPOINTForce a transaction log checkpoint.CHECKPOINT

PostgreSQL16CLOSEClose a cursor.CLOSE nameCLUSTERCluster a table according to an index.CLUSTER index_name ON table_nameCLUSTER table_nameCLUSTERCOMMENTDefine or change the comment of an object.COMMENT ON{TABLE object_name |COLUMN table_name.column_name |AGGREGATE agg_name (agg_type) |CAST (source_type AS target_type) |CONSTRAINT constraint_name ON table_name |CONVERSION object_name |DATABASE object_name |DOMAIN object_name |FUNCTION func_name (arg1_type, arg2_type, ...) |INDEX object_name |LARGE OBJECT large_object_oid |OPERATOR op (left_operand_type, right_operand_type) |OPERATOR CLASS object_name USING index_method |[ PROCEDURAL ] LANGUAGE object_name |RULE rule_name ON table_name |SCHEMA object_name |SEQUENCE object_name |TRIGGER trigger_name ON table_name |TYPE object_name |VIEW object_name} IS 'text'

PostgreSQL17COMMITCommit the current transaction.COMMIT [ WORK | TRANSACTION ]COPYCopy data between a file and a table.COPY table_name [ ( column [, ...] ) ]FROM { 'filename' | STDIN }[ [ WITH ][ BINARY ][ OIDS ][ DELIMITER [ AS ] 'delimiter' ][ NULL [ AS ] 'null string' ][ CSV [ QUOTE [ AS ] 'quote' ][ ESCAPE [ AS ] 'escape' ][ FORCE NOT NULL column [, ...] ]COPY table_name [ ( column [, ...] ) ]TO { 'filename' | STDOUT }[ [ WITH ][ BINARY ][ OIDS ][ DELIMITER [ AS ] 'delimiter' ][ NULL [ AS ] 'null string' ][ CSV [ QUOTE [ AS ] 'quote' ][ ESCAPE [ AS ] 'escape' ][ FORCE QUOTE column [, ...] ]

PostgreSQL18CREATE AGGREGATEDefine a new aggregate function.CREATE AGGREGATE name (BASETYPE = input_data_type,SFUNC = sfunc,STYPE = state_data_type[ , FINALFUNC = ffunc ][ , INITCOND = initial_condition ])CREATE CASTDefine a new cast.CREATE CAST (source_type AS target_type)WITH FUNCTION func_name (arg_types)[ AS ASSIGNMENT | AS IMPLICIT ]CREATE CAST (source_type AS target_type)WITHOUT FUNCTION[ AS ASSIGNMENT | AS IMPLICIT ]CREATE CONSTRAINT TRIGGERDefine a new constraint trigger.CREATE CONSTRAINT TRIGGER nameAFTER events ONtable_name constraint attributesFOR EACH ROW EXECUTE PROCEDURE func_name ( args )CREATE CONVERSIONDefine a new conversion.CREATE [DEFAULT] CONVERSION nameFOR source_encoding TO dest_encoding FROM func_nameCREATE DATABASE

PostgreSQL19Create a new database.CREATE DATABASE name[ [ WITH ] [ OWNER [=] db_owner ][ TEMPLATE [=] template ][ ENCODING [=] encoding ][ TABLESPACE [=] tablespace ] ]CREATE DOMAINDefine a new domain.CREATE DOMAIN name [AS] data_type[ DEFAULT expression ][ constraint [ ... ] ]Where constraint is:[ CONSTRAINT constraint_name ]{ NOT NULL | NULL | CHECK (expression) }CREATE FUNCTIONDefine a new function.CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, ...] ] )RETURNS ret_type{ LANGUAGE lang_name| IMMUTABLE | STABLE | VOLATILE| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER| AS 'definition'| AS 'obj_file', 'link_symbol'} ...[ WITH ( attribute [, ...] ) ]CREATE GROUPDefine a new user group.

PostgreSQL20CREATE GROUP name [ [ WITH ] option [ ... ] ]Where option can be:SYSID gid| USER username [, ...]CREATE INDEXDefine a new index.CREATE [ UNIQUE ] INDEX name ON table [ USING method ]( { column | ( expression ) } [ opclass ] [, ...] )[ TABLESPACE tablespace ][ WHERE predicate ]CREATE LANGUAGEDefine a new procedural language.CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE nameHANDLER call_handler [ VALIDATOR val_function ]CREATE OPERATORDefine a new operator.CREATE OPERATOR name (PROCEDURE = func_name[, LEFTARG = left_type ] [, RIGHTARG = right_type ][, COMMUTATOR = com_op ] [, NEGATOR = neg_op ][, RESTRICT = res_proc ] [, JOIN = join_proc ][, HASHES ] [, MERGES ][, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ][, LTCMP = less_than_op ] [, GTCMP = greater_than_op ])CREATE OPERATOR CLASSDefine a new operator class.

PostgreSQL21CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_typeUSING index_method AS{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ]| FUNCTION support_number func_name ( argument_type [, ...] )| STORAGE storage_type} [, ... ]CREATE RULEDefine a new rewrite rule.CREATE [ OR REPLACE ] RULE name AS ON eventTO table [ WHERE condition ]DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }CREATE SCHEMADefine a new schema.CREATE SCHEMA schema_name[ AUTHORIZATION username ] [ schema_element [ ... ] ]CREATE SCHEMA AUTHORIZATION username[ schema_element [ ... ] ]CREATE SEQUENCEDefine a new sequence generator.CREATE [ TEMPORARY | TEMP ] SEQUENCE name[ INCREMENT [ BY ] increment ][ MINVALUE minvalue | NO MINVALUE ][ MAXVALUE maxvalue | NO MAXVALUE ][ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]CREATE TABLEDefine a new table.CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ({ column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]| table_constraint| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ]

PostgreSQL22)[ INHERITS ( parent_table [, ... ] ) ][ WITH OIDS | WITHOUT OIDS ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ][ TABLESPACE tablespace ]Where column_constraint is:[ CONSTRAINT constraint_name ]{ NOT NULL |NULL |UNIQUE [ USING INDEX TABLESPACE tablespace ] |PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |CHECK (expression) |REFERENCES ref_table [ ( ref_column ) ][ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ][ ON DELETE action ] [ ON UPDATE action ] }[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]And table_constraint is:[ CONSTRAINT constraint_name ]{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |CHECK ( expression ) |FOREIGN KEY ( column_name [, ... ] )REFERENCES ref_table [ ( ref_column [, ... ] ) ][ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ][ ON DELETE action ] [ ON UPDATE action ] }[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]CREATE TABLE ASDefine a new table from the results of a query.

PostgreSQL23CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]AS queryCREATE TABLESPACEDefine a new tablespace.CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'CREATE TRIGGERDefine a new trigger.CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }ON table [ FOR [ EACH ] { ROW | STATEMENT } ]EXECUTE PROCEDURE func_name ( arguments )CREATE TYPEDefine a new data type.CREATE TYPE name AS( attribute_name data_type [, ... ] )CREATE TYPE name (INPUT = input_function,OUTPUT = output_function[ , RECEIVE = receive_function ][ , SEND = send_function ][ , ANALYZE = analyze_function ][ , INTERNALLENGTH = { internal_length | VARIABLE } ][ , PASSEDBYVALUE ][ , ALIGNMENT = alignment ][ , STORAGE = storage ][ , DEFAULT = default ][ , ELEMENT = element ][ , DELIMITER = delimiter ])CREATE USERDefine a new database user account.

PostgreSQL24CREATE USER name [ [ WITH ] option [ ... ] ]Where option can be:SYSID uid| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'| CREATEDB | NOCREATEDB| CREATEUSER | NOCREATEUSER| IN GROUP group_name [, ...]| VALID UNTIL 'abs_time'CREATE VIEWDefine a new view.CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS queryDEALLOCATEDeallocate a prepared statement.DEALLOCATE [ PREPARE ] plan_nameDECLAREDefine a cursor.DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]CURSOR [ { WITH | WITHOUT } HOLD ] FOR query[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]DELETEDelete rows of a table.DELETE FROM [ ONLY ] table [ WHERE condition ]DROP AGGREGATERemove an aggregate function.DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]DROP CASTRemove a cast.

PostgreSQL25DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ]DROP CONVERSIONRemove a conversion.DROP CONVERSION name [ CASCADE | RESTRICT ]DROP DATABASERemove a database.DROP DATABASE nameDROP DOMAINRemove a domain.DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ]DROP FUNCTIONRemove a function.DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]DROP GROUPRemove a user group.DROP GROUP nameDROP INDEXRemove an index.DROP INDEX name [, ...] [ CASCADE | RESTRICT ]DROP LANGUAGERemove a procedural language.DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]DROP OPERATORRemove an operator.

PostgreSQL26DROP OPERATOR name ( { left_type | NONE } , { right_type | NONE } )[ CASCADE | RESTRICT ]DROP OPERATOR CLASSRemove an operator class.DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]DROP RULERemove a rewrite rule.DROP RULE name ON relation [ CASCADE | RESTRICT ]DROP SCHEMARemove a schema.DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]DROP SEQUENCERemove a sequence.DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]DROP TABLERemove a table.DROP TABLE name [, ...] [ CASCADE | RESTRICT ]DROP TABLESPACERemove a tablespace.DROP TABLESPACE tablespace_nameDROP TRIGGERRemove a trigger.DROP TRIGGER name ON table [ CASCADE | RESTRICT ]DROP TYPERemove a data type.

PostgreSQL27DROP TYPE name [, ...] [ CASCADE | RESTRICT ]DROP USERRemove a database user account.DROP USER nameDROP VIEWRemove a view.DROP VIEW name [, ...] [ CASCADE | RESTRICT ]ENDCommit the current transaction.END [ WORK | TRANSACTION ]EXECUTEExecute a prepared statement.EXECUTE plan_name [ (parameter [, ...] ) ]EXPLAINShow the execution plan of a statement.EXPLAIN [ ANALYZE ] [ VERBOSE ] statementFETCHRetrieve rows from a query using a cursor.FETCH [ direction { FROM | IN } ] cursor_nameWhere direction can be empty or one of:NEXTPRIORFIRSTLASTABSOLUTE countRELATIVE countcountALLFORWARDFORWARD count

PostgreSQL28FORWARD ALLBACKWARDBACKWARD countBACKWARD ALLGRANTDefine access privileges.GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }[,...] | ALL [ PRIVILEGES ] }ON [ TABLE ] table_name [, ...]TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }ON DATABASE db_name [, ...]TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { CREATE | ALL [ PRIVILEGES ] }ON TABLESPACE tablespace_name [, ...]TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { EXECUTE | ALL [ PRIVILEGES ] }ON FUNCTION func_name ([type, ...]) [, ...]TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { USAGE | ALL [ PRIVILEGES ] }ON LANGUAGE lang_name [, ...]TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }ON SCHEMA schema_name [, ...]TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]INSERTCreate new rows in a table.INSERT INTO table [ ( column [, ...] ) ]

PostgreSQL29{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }LISTENListen for a notification.LISTEN nameLOADLoad or reload a shared library file.LOAD 'filename'LOCKLock a table.LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ]Where lock_mode is one of:ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVEMOVEPosition a cursor.MOVE [ direction { FROM | IN } ] cursor_nameNOTIFYGenerate a notification.NOTIFY namePREPAREPrepare a statement for execution.PREPARE plan_name [ (data_type [, ...] ) ] AS statementREINDEXRebuild indexes.REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

PostgreSQL30RELEASE SAVEPOINTDestroy a previously defined savepoint.RELEASE [ SAVEPOINT ] savepoint_nameRESETRestore the value of a runtime parameter to the default value.RESET nameRESET ALLREVOKERemove access privileges.REVOKE [ GRANT OPTION FOR ]{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }[,...] | ALL [ PRIVILEGES ] }ON [ TABLE ] table_name [, ...]FROM { username | GROUP group_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }ON DATABASE db_name [, ...]FROM { username | GROUP group_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ CREATE | ALL [ PRIVILEGES ] }ON TABLESPACE tablespace_name [, ...]FROM { username | GROUP group_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ EXECUTE | ALL [ PRIVILEGES ] }ON FUNCTION func_name ([type, ...]) [, ...]FROM { username | GROUP group_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON LANGUAGE lang_name [, ...]

PostgreSQL31FROM { username | GROUP group_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]REVOKE [ GRANT OPTION FOR ]{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }ON SCHEMA schema_name [, ...]FROM { username | GROUP group_name | PUBLIC } [, ...][ CASCADE | RESTRICT ]ROLLBACKAbort the current transaction.ROLLBACK [ WORK | TRANSACTION ]ROLLBACK TO SAVEPOINTRoll back to a savepoint.ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_nameSAVEPOINTDefine a new savepoint within the current transaction.SAVEPOINT savepoint_nameSELECTRetrieve rows from a table or view.SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]* | expression [ AS output_name ] [, ...][ FROM from_item [, ...] ][ WHERE condition ][ GROUP BY expression [, ...] ][ HAVING condition [, ...] ][ { UNION | INTERSECT | EXCEPT } [ ALL ] select ][ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ][ LIMIT { count | ALL } ][ OFFSET start ][ FOR UPDATE [ OF table_name [, ...] ] ]Where from_item can be one of:[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]( select ) [ AS ] alias [ ( column_alias [, ...] ) ]

PostgreSQL32function_name ( [ argument [, ...] ] )[ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )from_item [ NATURAL ] join_type from_item[ ON join_condition | USING ( join_column [, ...] ) ]SELECT INTODefine a new table from the results of a query.SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]* | expression [ AS output_name ] [, ...]INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table[ FROM from_item [, ...] ][ WHERE condition ][ GROUP BY expression [, ...] ][ HAVING condition [, ...] ][ { UNION | INTERSECT | EXCEPT } [ ALL ] select ][ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ][ LIMIT { count | ALL } ][ OFFSET start ][ FOR UPDATE [ OF table_name [, ...] ] ]SETChange a runtime parameter.SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }SET CONSTRAINTSSet constraint checking modes for the current transaction.SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }SET SESSION AUTHORIZATION

PostgreSQL33Set the session user identifier and the current user identifier of the current session.SET [ SESSION | LOCAL ] SESSION AUTHORIZATION usernameSET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULTRESET SESSION AUTHORIZATIONSET TRANSACTIONSet the characteristics of the current transaction.SET TRANSACTION transaction_mode [, ...]SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]Where transaction_mode is one of:ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED| READ UNCOMMITTED }READ WRITE | READ ONLYSHOWShow the value of a runtime parameter.SHOW nameSHOW ALLSTART TRANSACTIONStart a transaction block.START TRANSACTION [ transaction_mode [, ...] ]Where transaction_mode is one of:ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED| READ UNCOMMITTED }READ WRITE | READ ONLYTRUNCATEEmpty a table.TRUNCATE [ TABLE ] nameUNLISTEN

PostgreSQL34Stop listening for a notification.UNLISTEN { name | * }UPDATEUpdate rows of a table.UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...][ FROM from_list ][ WHERE condition ]VACUUMGarbage-collect and optionally analyze a database.VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

PostgreSQL35In this chapter, we will discuss about the data types used in PostgreSQL. While creatingtable, for each column, you specify a data type, i.e., what kind of data you want to storein the table fields.This enables several benefits: Consistency: Operations against columns of same data type give consistentresults and are usually the fastest. Validation: Proper use of data types implies format validation of data and rejectionof data outside the scope of data type. Compactness: As a column can store a single type of value, it is stored in acompact way. Performance: Proper use of data types gives the most efficient storage of data.The values stored can be processed quickly, which enhances the performance.PostgreSQL supports a wide set of Data Types. Besides, users can create their own customdata type using CREATE TYPE SQL command. There are different categories of data typesin PostgreSQL. They are discussed below.NumericTypesNumeric types consist of two-byte, four-byte, and eight-byte integers, four-byte and eight-byte floating-point numbers, and selectable-precision decimals. The following table liststhe available types.NameStorageSizeDescription Rangesmallint 2 bytes small-range integer -32768 to +32767integer 4 bytes typical choice for integer-2147483648 to+2147483647bigint 8 bytes large-range integer-9223372036854775808to9223372036854775807decimal variable user-specified precision,exactup to 131072 digitsbefore the decimalpoint; up to 16383digits after the decimalpoint4. PostgreSQL – Data Type

PostgreSQL36numeric variable user-specified precision,exactup to 131072 digitsbefore the decimalpoint; up to 16383digits after the decimalpointreal 4 bytes variable-precision,inexact6 decimal digitsprecisiondouble precision 8 bytes variable-precision,inexact15 decimal digitsprecisionsmallserial 2 bytes small autoincrementing integer 1 to 32767serial 4 bytes autoincrementing integer 1 to 2147483647bigserial 8 bytes large autoincrementing integer1 to9223372036854775807MonetaryTypesThe money data type stores a currency amount with a fixed fractional precision. Values ofthe numeric, int, and bigint data types can be cast to money. Using Floating point numbersis not recommended to handle money due to the potential for rounding errors.NameStorageSizeDescription Rangemoney 8 bytes currency amount-92233720368547758.08 to+92233720368547758.07CharacterTypesThe table given below lists the general-purpose character types available in PostgreSQL.Name Descriptioncharacter varying(n), varchar(n) variable-length with limitcharacter(n), char(n) fixed-length, blank paddedtext variable unlimited length

PostgreSQL37BinaryDataTypesThe bytea data type allows storage of binary strings as in the table given below.Name Storage Size Descriptionbytea 1 or 4 bytes plus the actual binary string variable-length binary stringDate/TimeTypesPostgreSQL supports a full set of SQL date and time types, as shown in table below. Datesare counted according to the Gregorian calendar. Here, all the types have resolution of 1microsecond / 14 digits except date type, whose resolution is day.Name Storage Size Description Low Value High Valuetimestamp[(p)] [withouttime zone ]8 bytesboth date andtime (no timezone)4713 BC 294276 ADtimestamp[(p) ] withtime zone8 bytesboth date andtime, with timezone4713 BC 294276 ADdate 4 bytesdate (no timeof day)4713 BC 5874897 ADtime [ (p)] [without timezone ]8 bytestime of day (nodate)00:00:00 24:00:00time [ (p)]with timezone12 bytestimes of dayonly, with timezone00:00:00+1459 24:00:00-1459interval[fields ] [(p) ]12 bytes time interval-178000000years178000000 yearsBooleanTypePostgreSQL provides the standard SQL type Boolean. The Boolean data type can have thestates- true, false, and a third state, unknown, which is represented by the SQL null value.Name Storage Size Descriptionboolean 1 byte state of true or false

PostgreSQL38EnumeratedTypeEnumerated (enum) types are data types that comprise a static, ordered set of values.They are equivalent to the enum types supported in a number of programming languages.Unlike other types, Enumerated Types need to be created using CREATE TYPE command.This type is used to store a static, ordered set of values. For example compass directions,i.e., NORTH, SOUTH, EAST, and WEST or days of the week as shown below:CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');Enumerated, once created, can be used like any other types.GeometricTypeGeometric data types represent two-dimensional spatial objects. The most fundamentaltype, the point, forms the basis for all of the other types.Name Storage Size Representation Descriptionpoint 16 bytes Point on a plane (x,y)line 32 bytesInfinite line (not fullyimplemented)((x1,y1),(x2,y2))lseg 32 bytes Finite line segment ((x1,y1),(x2,y2))box 32 bytes Rectangular box ((x1,y1),(x2,y2))path 16+16n bytes Closed path (similar to polygon) ((x1,y1),...)path 16+16n bytes Open path [(x1,y1),...]polygon 40+16n Polygon (similar to closed path) ((x1,y1),...)circle 24 bytes Circle<(x,y),r> (center pointand radius)NetworkAddressTypePostgreSQL offers data types to store IPv4, IPv6, and MAC addresses. It is better to usethese types instead of plain text types to store network addresses because these typesoffer input error checking and specialized operators and functions.Name Storage Size Descriptioncidr 7 or 19 bytes IPv4 and IPv6 networksinet 7 or 19 bytes IPv4 and IPv6 hosts and networks

PostgreSQL39macaddr 6 bytes MAC addressesBitStringTypeBit String Types are used to store bit masks. They are either 0 or 1. There are two SQL bittypes: bit(n) and bit varying(n), where n is a positive integer.TextSearchTypeThis type supports full text search, which is the activity of searching through a collectionof natural-language documents to locate those that best match a query. There are twoData Types for this-Name DescriptiontsvectorThis is a sorted list of distinct words that have been normalized to mergedifferent variants of the same word, called as "lexemes".tsqueryThis stores lexemes that are to be searched for, and combines them honoringthe Boolean operators & (AND), | (OR), and ! (NOT). Parentheses can be usedto enforce grouping of the operators.UUIDTypeA UUID (Universally Unique Identifiers) is written as a sequence of lower-case hexadecimaldigits, in several groups separated by hyphens, specifically a group of eight digits, followedby three groups of four digits, followed by a group of 12 digits, for a total of 32 digitsrepresenting the 128 bits.An example of a UUID is: 550e8400-e29b-41d4-a716-446655440000XMLTypeThe XML data type can be used to store XML data. For storing XML data, first you have tocreate XML values using the function xmlparse as follows:XMLPARSE (DOCUMENT '<?xml version="1.0"?><tutorial><title>PostgreSQL Tutorial </title><topics>...</topics></tutorial>')XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

PostgreSQL40JSONTypeThe json data type can be used to store JSON (JavaScript Object Notation) data. Suchdata can also be stored as text, but the json data type has the advantage of checking thateach stored value is a valid JSON value. There are also related support functions available,which can be used directly to handle JSON data type as follows-Example Example Resultarray_to_json('{{1,5},{99,100}}'::int[]) [[1,5],[99,100]]row_to_json(row(1,'foo')) {"f1":1,"f2":"foo"}ArrayTypePostgreSQL gives the opportunity to define a column of a table as a variable lengthmultidimensional array. Arrays of any built-in or user-defined base type, enum type, orcomposite type can be created.Declaration of ArraysArray type can be declared as-CREATE TABLE monthly_savings (name text,saving_per_quarter integer[],scheme text[][]);or by using the keyword "ARRAY" as-CREATE TABLE monthly_savings (name text,saving_per_quarter integer ARRAY[4],scheme text[][]);Inserting valuesArray values can be inserted as a literal constant, enclosing the element values withincurly braces and separating them by commas. An example is shown below.INSERT INTO monthly_savingsVALUES (‘Manisha’,‘{20000, 14600, 23500, 13250}’,‘{{“FD”, “MF”}, {“FD”, “Property”}}’);

PostgreSQL41Accessing ArraysAn example for accessing Arrays is shown below. The command given below will select thepersons whose savings are more in second quarter than fourth quarter.SELECT name FROM monhly_savings WHERE saving_per_quarter[2] >saving_per_quarter[4];Modifying ArraysAn example of modifying arrays is as shown below.UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'WHERE name = 'Manisha';or using the ARRAY expression syntax:UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]WHERE name = 'Manisha';Searching ArraysAn example of searching arrays is as shown below.SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 ORsaving_per_quarter[2] = 10000 ORsaving_per_quarter[3] = 10000 ORsaving_per_quarter[4] = 10000;If the size of array is known, the search method given above can be used. Else, thefollowing example shows how to search when the size is not known.SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);CompositeTypesThis type represents a list of field names and their data types, i.e., structure of a row orrecord of a table.

PostgreSQL42Declaration of Composite TypesThe following example shows how to declare a composite type-CREATE TYPE inventory_item AS (name text,supplier_id integer,price numeric);This data type can be used in the create tables as below-CREATE TABLE on_hand (item inventory_item,count integer);Composite Value InputComposite values can be inserted as a literal constant, enclosing the field values withinparentheses and separating them by commas. An example is shown below.INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);This is valid for the inventory_item defined above. The ROW keyword is actually optionalas long as you have more than one field in the expression.Accessing Composite TypesTo access a field of a composite column, use a dot followed by the field name, much likeselecting a field from a table name. For example, to select some subfields from ouron_hand example table, the query would be as shown below-SELECT (item).name FROM on_hand WHERE (item).price > 9.99;You can even use the table name as well (for instance in a multitable query), like this-SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;RangeTypesRange types represent data types that uses a range of data. Range type can be discreteranges (e.g., all integer values 1 to 10) or continuous ranges (e.g., any point in timebetween 10:00am and 11:00am).The built-in range types available include the following ranges- int4range - Range of integer

PostgreSQL43 int8range - Range of bigint numrange - Range of numeric tsrange - Range of timestamp without time zone tstzrange - Range of timestamp with time zone daterange - Range of dateCustom range types can be created to make new types of ranges available, such as IPaddress ranges using the inet type as a base, or float ranges using the float data type asa base.Range types support inclusive and exclusive range boundaries using the [ ] and ( )characters, respectively. For example, '[4,9]' represents all the integers starting from andincluding 4 up to but not including 9.ObjectIdentifierTypesObject identifiers (OIDs) are used internally by PostgreSQL as primary keys for varioussystem tables. If WITH OIDS is specified or default_with_oids configuration variable isenabled, only then, in such cases OIDs are added to user-created tables. The followingtable lists several alias types. The OID alias types have no operations of their own exceptfor specialized input and output routines.Name References Description Value Exampleoid any numeric object identifier 564182regproc pg_proc function name sumregprocedure pg_proc function with argument types sum(int4)regoper pg_operator operator name +regoperator pg_operator operator with argument types *(integer,integer) or -(NONE,integer)regclass pg_class relation name pg_typeregtype pg_type data type name integerregconfig pg_ts_config text search configuration Englishregdictionary pg_ts_dict text search dictionary simplePseudoTypesThe PostgreSQL type system contains a number of special-purpose entries that arecollectively called pseudo-types. A pseudo-type cannot be used as a column data type,but it can be used to declare a function's argument or result type.

PostgreSQL44The table given below lists the existing pseudo-types.Name Descriptionany Indicates that a function accepts any input data type.anyelement Indicates that a function accepts any data type.anyarray Indicates that a function accepts any array data type.anynonarray Indicates that a function accepts any non-array data type.anyenum Indicates that a function accepts any enum data type.anyrange Indicates that a function accepts any range data type.cstringIndicates that a function accepts or returns a null-terminated Cstring.internalIndicates that a function accepts or returns a server-internal datatype.language_handlerA procedural language call handler is declared to returnlanguage_handler.fdw_handler A foreign-data wrapper handler is declared to return fdw_handler.record Identifies a function returning an unspecified row type.trigger A trigger function is declared to return trigger.void Indicates that a function returns no value.

PostgreSQL45This chapter discusses about how to create a new database in your PostgreSQL.PostgreSQL provides two ways of creating a new database: Using CREATE DATABASE, an SQL command. Using createdb a command-line executable.Using CREATE DATABASEThis command will create a database from PostgreSQL shell prompt, but you should haveappropriate privilege to create a database. By default, the new database will be createdby cloning the standard system database template1.SyntaxThe basic syntax of CREATE DATABASE statement is as follows-CREATE DATABASE dbname;where dbname is the name of a database to create.ExampleFollowing is a simple example, which will create testdb in your PostgreSQL schema-postgres=# CREATE DATABASE testdb;postgres-#UsingcreatedbCommandPostgreSQL command line executable createdb is a wrapper around the SQLcommand CREATE DATABASE. The only difference between this command and SQLcommand CREATE DATABASE is that the former can be directly run from the commandline and it allows a comment to be added into the database, all in one command.SyntaxThe syntax for createdb is as shown below-createdb [option...] [dbname [description]]5. PostgreSQL – CREATE Database

PostgreSQL46ParametersThe table given below lists the parameters with their descriptions-Parameter Descriptiondbname The name of a database to create.description Specifies a comment to be associated with the newly created database.options command-line arguments, which createdb accepts.OptionsThe following table lists the command line arguments createdb accepts-Option Description-D tablespace Specifies the default tablespace for the database.-e Echo the commands that createdb generates and sends to the server.-E encoding Specifies the character encoding scheme to be used in this database.-l locale Specifies the locale to be used in this database.-T template Specifies the template database from which to build this database.--help Show help about dropdb command line arguments, and exit.-h host Specifies the host name of the machine on which the server is running.-p portSpecifies the TCP port or the local Unix domain socket file extension onwhich the server is listening for connections.-U username User name to connect as.-w Never issue a password prompt.-WForce createdb to prompt for a password before connecting to adatabase.Open the command prompt and go to the directory where PostgreSQL is installed. Go tothe bin directory and execute the following command to create a database.createdb -h localhost -p 5432 -U postgres testdbpassword ******

PostgreSQL47The above given command will prompt you for password of the PostgreSQL admin user,which is postgres, by default. Hence, provide a password and proceed to create your newdatabase.Once a database is created using either of the above-mentioned methods, you can checkit in the list of databases using l, i.e., backslash el command as follows-postgres-# lList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges-----------+----------+----------+---------+-------+-----------------------postgres | postgres | UTF8 | C | C |template0 | postgres | UTF8 | C | C | =c/postgres +| | | | | postgres=CTc/postgrestemplate1 | postgres | UTF8 | C | C | =c/postgres +| | | | | postgres=CTc/postgrestestdb | postgres | UTF8 | C | C |(4 rows)postgres-#

PostgreSQL48This chapter explains various methods of accessing the database. Assume that we havealready created a database in our previous chapter. You can select the database usingeither of the following methods- Database SQL Prompt OS Command PromptDatabaseSQLPromptAssume you have already launched your PostgreSQL client and you have landed at thefollowing SQL prompt-postgres=#You can check the available database list using l, i.e., backslash el command as follows-postgres-# lList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges-----------+----------+----------+---------+-------+-----------------------postgres | postgres | UTF8 | C | C |template0 | postgres | UTF8 | C | C | =c/postgres +| | | | | postgres=CTc/postgrestemplate1 | postgres | UTF8 | C | C | =c/postgres +| | | | | postgres=CTc/postgrestestdb | postgres | UTF8 | C | C |(4 rows)postgres-#Now, type the following command to connect/select a desired database; here, we willconnect to the testdb database.postgres=# c testdb;psql (9.2.4)Type "help" for help.You are now connected to database "testdb" as user "postgres".testdb=#6. PostgreSQL – SELECT Database

PostgreSQL49OSCommandPromptYou can select your database from the command prompt itself at the time when you loginto your database. Following is a simple example-psql -h localhost -p 5432 -U postgress testdbPassword for user postgress: ****psql (9.2.4)Type "help" for help.You are now connected to database "testdb" as user "postgres".testdb=#You are now logged into PostgreSQL testdb and ready to execute your commands insidetestdb. To exit from the database, you can use the command q.

PostgreSQL50In this chapter, we will discuss how to delete the database in PostgreSQL. There are twooptions to delete a database- Using DROP DATABASE, an SQL command. Using dropdb a command-line executable.Be careful before using this operation because deleting an existing database would resultin loss of complete information stored in the database.Using DROP DATABASEThis command drops a database. It removes the catalog entries for the database anddeletes the directory containing the data. It can only be executed by the database owner.This command cannot be executed while you or anyone else is connected to the targetdatabase (connect to postgres or any other database to issue this command).SyntaxThe syntax for DROP DATABASE is given below-DROP DATABASE [ IF EXISTS ] nameParametersThe table lists the parameters with their descriptions.Parameter DescriptionIF EXISTSDo not throw an error if the database does not exist. A notice is issued inthis case.name The name of the database to remove.We cannot drop a database that has any open connections, including our own connectionfrom psql or pgAdmin III. We must switch to another database or template1 if we want todelete the database we are currently connected to. Thus, it might be more convenient touse the program dropdb instead, which is a wrapper around this command.ExampleFollowing is a simple example, which will delete testdb from your PostgreSQL schema-postgres=# DROP DATABASE testdb;postgres-#7. PostgreSQL – DROP Database

PostgreSQL51UsingdropdbCommandPostgresSQL command line executable dropdb is a command-line wrapper around theSQL command DROP DATABASE. There is no effective difference between droppingdatabases via this utility and via other methods for accessing the server. dropdb destroysan existing PostgreSQL database. The user, who executes this command must be adatabase super user or the owner of the database.SyntaxThe syntax for createdb is as shown below-dropdb [option...] dbnameParametersThe following table lists the parameters with their descriptions-Parameter Descriptiondbname The name of a database to be deleted.option command-line arguments, which dropdb accepts.OptionsThe following table lists the command-line arguments dropdb accepts-Option Description-e Shows the commands being sent to the server.-iIssues a verification prompt before doing anythingdestructive.-V Print the dropdb version and exit.--if-existsDo not throw an error if the database does not exist. Anotice is issued in this case.--helpShow help about dropdb command-line arguments, andexit.-h hostSpecifies the host name of the machine on which the serveris running.-p portSpecifies the TCP port or the local UNIX domain socket fileextension on which the server is listening for connections.

PostgreSQL52-U username User name to connect as.-w Never issue a password prompt.-WForce dropdb to prompt for a password before connectingto a database.--maintenance-db=dbnameSpecifies the name of the database to connect to in orderto drop the target database.ExampleThe following example demonstrates deleting a database from OS command prompt-dropdb -h localhost -p 5432 -U postgress testdbPassword for user postgress: ****The above command drops the database testdb. Here, I have used the postgres (foundunder the pg_roles of template1) username to drop the database.

PostgreSQL53The PostgreSQL CREATE TABLE statement is used to create a new table in any of the givendatabase.SyntaxBasic syntax of CREATE TABLE statement is as follows-CREATE TABLE table_name(column1 datatype,column2 datatype,column3 datatype,.....columnN datatype,PRIMARY KEY( one or more columns ));CREATE TABLE is a keyword, telling the database system to create a new table. The uniquename or identifier for the table follows the CREATE TABLE statement. Initially, the emptytable in the current database is owned by the user issuing the command.Then, in brackets, comes the list, defining each column in the table and what sort of datatype it is. The syntax will become clear with an example given below.ExamplesFollowing is an example, which creates a COMPANY table with ID as primary key and NOTNULL are the constraints showing that these fields cannot be NULL while creating recordsin this table-CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);8. PostgreSQL – CREATE Table

PostgreSQL55The PostgreSQL DROP TABLE statement is used to remove a table definition and allassociated data, indexes, rules, triggers, and constraints for that table.You have to be careful while using this command because once a table is deleted then allthe information available in the table would also be lost forever.SyntaxBasic syntax of DROP TABLE statement is as follows-DROP TABLE table_name;ExampleWe had created the tables DEPARTMENT and COMPANY in the previous chapter. First,verify these tables (use d to list the tables):testdb-# dThis would produce the following result-List of relationsSchema | Name | Type | Owner--------+------------+-------+----------public | company | table | postgrespublic | department | table | postgres(2 rows)This means DEPARTMENT and COMPANY tables are present. So let us drop them as follows-testdb=# drop table department, company;This would produce the following result-DROP TABLEtestdb=# drelations found.testdb=#The message returned DROP TABLE indicates that drop command is executed successfully.9. PostgreSQL – DROP Table

PostgreSQL56A schema is a named collection of tables. A schema can also contain views, indexes,sequences, data types, operators, and functions. Schemas are analogous to directories atthe operating system level, except that schemas cannot be nested. PostgreSQL statementCREATE SCHEMA creates a schema.SyntaxThe basic syntax of CREATE SCHEMA is as follows-CREATE SCHEMA name;Where name is the name of the schema.SyntaxtoCreateTableinSchemaThe basic syntax to create table in schema is as follows:CREATE TABLE myschema.mytable (...);ExampleLet us see an example for creating a schema. Connect to the database testdb and createa schema myschema as follows-testdb=# create schema myschema;CREATE SCHEMAThe message "CREATE SCHEMA" signifies that the schema is created successfully.Now, let us create a table in the above schema as follows-testdb=# create table myschema.company(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25) ,SALARY DECIMAL (18, 2),PRIMARY KEY (ID));10. PostgreSQL – Schema

PostgreSQL57This will create an empty table. You can verify the table created with the command givenbelow-testdb=# select * from myschema.company;This would produce the following result-id | name | age | address | salary----+------+-----+---------+--------(0 rows)SyntaxtoDropSchemaTo drop a schema if it is empty (all objects in it have been dropped), use the command-DROP SCHEMA myschema;To drop a schema including all contained objects, use the command-DROP SCHEMA myschema CASCADE;Advantages of using a Schema It allows many users to use one database without interfering with each other. It organizes database objects into logical groups to make them more manageable. Third-party applications can be put into separate schemas so they do not collidewith the names of other objects.

PostgreSQL58The PostgreSQL INSERT INTO statement allows one to insert new rows into a table. Onecan insert a single row at a time or several rows as a result of a query.SyntaxBasic syntax of INSERT INTO statement is as follows-INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)VALUES (value1, value2, value3,...valueN); Here, column1, column2,...columnN are the names of the columns in the table intowhich you want to insert data. The target column names can be listed in any order. The values supplied by theVALUES clause or query are associated with the explicit or implicit column list left-to-right.You may not need to specify the column(s) name in the SQL query if you are adding valuesfor all the columns of the table. However, make sure the order of the values is in the sameorder as the columns in the table. The SQL INSERT INTO syntax would be as follows-INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);OutputThe following table summarizes the output messages and their meaning-OutputMessageDescriptionINSERT oid 1Message returned if only one row was inserted. oid is the numericOID of the inserted row.INSERT 0 #Message returned if more than one rows were inserted. # is thenumber of rows inserted.ExamplesLet us create COMPANY table in testdb as follows-CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,11. PostgreSQL – INSERT Query

PostgreSQL59ADDRESS CHAR(50),SALARY REAL,JOIN_DATE DATE);Following example inserts a row into the COMPANY table-INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul',32, 'California', 20000.00 ,'2001-07-13');Following example is to insert a row; here salary column is omitted and therefore it willhave the default value-INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25,'Texas', '2007-12-13');Following example uses the DEFAULT clause for the ADDRESS columns rather thanspecifying a value-INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy',23, 'Norway', 20000.00, DEFAULT );Following example inserts multiple rows using the multirow VALUES syntax-INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark',25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00 ,'2007-12-13');All the above statements would create the following records in COMPANY table. The nextchapter will teach you how to display all these records from a table.ID NAME AGE ADDRESS SALARY JOIN_DATE---- ---------- ----- ---------- ------- --------1 Paul 32 California 20000.0 2001-07-132 Allen 25 Texas 2007-12-133 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.0 2007-12-135 David 27 Texas 85000.0 2007-12-13

PostgreSQL60PostgreSQL SELECT statement is used to fetch the data from a database table, whichreturns data in the form of result table. These result tables are called result-sets.SyntaxThe basic syntax of SELECT statement is as follows-SELECT column1, column2, columnN FROM table_name;Here, column1, column2...are the fields of a table, whose values you want to fetch. If youwant to fetch all the fields available in the field then you can use the following syntax-SELECT * FROM table_name;ExampleConsider the table COMPANY having records as follows-id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Following is an example, which would fetch ID, Name and Salary fields of the customersavailable in CUSTOMERS table-testdb=# SELECT ID, NAME, SALARY FROM COMPANY ;This would produce the following result-id | name | salary----+-------+--------1 | Paul | 200002 | Allen | 1500012. PostgreSQL – SELECT Query

PostgreSQL613 | Teddy | 200004 | Mark | 650005 | David | 850006 | Kim | 450007 | James | 10000(7 rows)If you want to fetch all the fields of CUSTOMERS table, then use the following query-testdb=# SELECT * FROM COMPANY;This would produce the following result-id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)

PostgreSQL62What is an Operator in PostgreSQL?An operator is a reserved word or a character used primarily in a PostgreSQL statement'sWHERE clause to perform operation(s), such as comparisons and arithmetic operations.Operators are used to specify conditions in a PostgreSQL statement and to serve asconjunctions for multiple conditions in a statement. Arithmetic operators Comparison operators Logical operators Bitwise operatorsPostgreSQLArithmeticOperatorsAssume variable a holds 2 and variable b holds 3, then-ExampleHere are simple examples showing usage of PostgreSQL Arithmetic Operators:testdb=# select 2+3;?column?----------5(1 row)testdb=# select 2*3;?column?----------6(1 row)testdb=# select 10/5;?column?----------2(1 row)testdb=# select 12%5;?column?----------2(1 row)13. PostgreSQL – Operators

PostgreSQL63testdb=# select 2^3;?column?----------8(1 row)testdb=# select |/ 25.0;?column?----------5(1 row)testdb=# select ||/ 27.0;?column?----------3(1 row)testdb=# select 5 !;?column?----------120(1 row)testdb=# select !!5;?column?----------120(1 row)Operator Description Example+ Addition - Adds values on either side of the operator a + b will give 5-Subtraction - Subtracts right hand operand from lefthand operanda - b will give -1*Multiplication - Multiplies values on either side of theoperatora * b will give 6/Division - Divides left hand operand by right handoperandb / a will give 1%Modulus - Divides left hand operand by right handoperand and returns remainderb % a will give 1^Exponentiation - This gives the exponent value ofthe right hand operanda ^ b will give 8

PostgreSQL64|/ square root |/ 25.0 will give 5||/ Cube root||/ 27.0 will give3!/ factorial 5 ! will give 120!! factorial (prefix operator) !! 5 will give 120PostgreSQLComparisonOperatorsAssume variable a holds 10 and variable b holds 20, then-ExampleConsider the table COMPANY having records as follows-testdb=# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Here are simple examples showing the usage of PostgresSQL Comparison Operators.Here, we have used the WHERE clause, which will be explained in a separate chapter, butfor now, you can understand that the WHERE clause is used to put a conditional statementalong with the SELECT statement.The following SELECT statement lists down all the records having SALARY greater than50,000.00-testdb=# SELECT * FROM COMPANY WHERE SALARY > 50000;The above given PostgreSQL statement will produce the following result-id | name | age |address | salary----+-------+-----+-----------+--------4 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 85000(2 rows)Following SELECT statement lists down all the records having SALARY equal to 20,000.00-

PostgreSQL65testdb=# SELECT * FROM COMPANY WHERE SALARY = 20000;Above PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+-------------+--------1 | Paul | 32 | California | 200003 | Teddy | 23 | Norway | 20000(2 rows)Following SELECT statement lists down all the records having SALARY not equal to20,000.00-testdb=# SELECT * FROM COMPANY WHERE SALARY != 20000;Above PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+-------------+--------2 | Allen | 25 | Texas | 150004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 10000(5 rows)Following SELECT statement lists down all the records having SALARY not equal to20,000.00-testdb=# SELECT * FROM COMPANY WHERE SALARY <> 20000;Above PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+------------+--------2 | Allen | 25 | Texas | 150004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 10000(5 rows)Following SELECT statement lists down all the records having SALARY greater than orequal to 65,000.00-testdb=# SELECT * FROM COMPANY WHERE SALARY ≥ 65000;The above given PostgreSQL statement will produce the following result-

PostgreSQL66id | name | age | address | salary----+-------+-----+-----------+--------4 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 85000(2 rows)Operator Description Example=Checks if the values of two operands are equal ornot, if yes then condition becomes true.(a = b) is not true.!= Checks if the values of two operands are equal ornot, if values are not equal then condition becomestrue.(a != b) is true.<>Checks if the values of two operands are equal ornot, if values are not equal then condition becomestrue.(a <> b) is true.>Checks if the value of left operand is greater thanthe value of right operand, if yes then conditionbecomes true.(a > b) is not true.<Checks if the value of left operand is less than thevalue of right operand, if yes then conditionbecomes true.(a < b) is true.>=Checks if the value of left operand is greater thanor equal to the value of right operand, if yes thencondition becomes true.(a >= b) is not true.<=Checks if the value of left operand is less than orequal to the value of right operand, if yes thencondition becomes true.(a <= b) is true.PostgreSQLLogicalOperatorsHere is a list of all the logical operators available in PostgresSQL.Operator DescriptionANDThe AND operator allows the existence of multiple conditions in aPostgresSQL statement's WHERE clause.NOTThe NOT operator reverses the meaning of the logical operator with whichit is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negateoperator.

PostgreSQL67ORThe OR operator is used to combine multiple conditions in a PostgreSQLstatement's WHERE clause.ExampleConsider the table COMPANY having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Here are simple examples showing the usage of PostgreSQL LOGICAL Operators. Thefollowing SELECT statement lists down all the records where AGE is greater than or equalto 25 and salary is greater than or equal to 65000.00.testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+-----------------------------------------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 85000(4 rows)The following SELECT statement lists down all the records where AGE is greater than orequal to 25 OR salary is greater than or equal to 65000.00-testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 6500;

PostgreSQL68The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+-------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 100008 | Paul | 24 | Houston | 200009 | James | 44 | Norway | 500010 | James | 45 | Texas | 5000(10 rows)Following SELECT statement lists down all the records where AGE is not NULL, whichmeans all the records because none of the record is having AGE equal to NULL-testdb=# SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+-------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 100008 | Paul | 24 | Houston | 200009 | James | 44 | Norway | 500010 | James | 45 | Texas | 5000(10 rows)

PostgreSQL69PostgreSQLBitStringOperatorsBitwise operator works on bits and performs bit-by-bit operation. The truth table for & and| is as follows-p q p & q p | q0 0 0 00 1 0 11 1 1 11 0 0 1Assume A = 60; and B = 13; now in binary format they will be as follows-A = 0011 1100B = 0000 1101-----------------A&B = 0000 1100A|B = 0011 1101~A = 1100 0011ExampleHere are simple examples showing the usage of PostgreSQL Bitwise Operators.Assume variable A holds 60 and variable B holds 13 then-testdb=# select 60 | 13;?column?----------61(1 row)testdb=# select 60 & 13;?column?----------12(1 row)testdb=# select (~60);?column?-----------61(1 row)

PostgreSQL70testdb=# select (60 << 2);?column?----------240(1 row)testdb=# select (60 >> 2);?column?----------15(1 row)testdb=# select 60 # 13;?column?----------49(1 row)The Bitwise operators supported by PostgreSQL are listed in the following table-Operator Description Example&Binary AND Operator copies a bit tothe result if it exists in bothoperands.(A & B) will give 12 which is 00001100|Binary OR Operator copies a bit if itexists in either operand.(A | B) will give 61 which is 00111101~Binary Ones Complement Operatoris unary and has the effect of'flipping' bits.(~A ) will give -61 which is 11000011 in 2's complement form due toa signed binary number.<<Binary Left Shift Operator. The leftoperands value is moved left by thenumber of bits specified by the rightoperand.A << 2 will give 240 which is 11110000>>Binary Right Shift Operator. The leftoperands value is moved right by thenumber of bits specified by the rightoperand.A >> 2 will give 15 which is 00001111# bitwise XOR. A # B will give 49 which is 0100 1001

PostgreSQL71An expression is a combination of one or more values, operators, and PostgresSQLfunctions that evaluate to a value.PostgreSQL EXPRESSIONS are like formulas and they are written in query language. Youcan also use to query the database for specific set of data.SyntaxConsider the basic syntax of the SELECT statement as follows-SELECT column1, column2, columnNFROM table_nameWHERE [CONTION | EXPRESSION];There are different types of PostgreSQL expressions, which are mentioned below.PostgreSQL–BooleanExpressionsPostgreSQL Boolean Expressions fetch the data on the basis of matching single value.Following is the syntax-SELECT column1, column2, columnNFROM table_nameWHERE SINGLE VALUE MATCHTING EXPRESSION;Consider the table COMPANY having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)14. PostgreSQL – Expressions

PostgreSQL72Here is the simple example showing usage of PostgreSQL Boolean Expressions-testdb=# SELECT * FROM COMPANY WHERE SALARY = 10000;The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+----------+--------7 | James | 24 | Houston | 10000(1 row)PostgreSQL–NumericExpressionThese expressions are used to perform any mathematical operation in any query. Followingis the syntax-SELECT numerical_expression as OPERATION_NAME[FROM table_name WHERE CONDITION] ;Here numerical_expression is used for mathematical expression or any formula. Followingis a simple example showing usage of SQL Numeric Expressions-testdb=# SELECT (15 + 6) AS ADDITION ;The above given PostgreSQL statement will produce the following result-addition----------21(1 row)There are several built-in functions like avg(), sum(), count() to perform what is knownas aggregate data calculations against a table or a specific table column.testdb=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY;The above given PostgreSQL statement will produce the following result-RECORDS---------7(1 row)

PostgreSQL73PostgreSQL–DateExpressionsDate Expressions return the current system date and time values and these expressionsare used in various data manipulations.testdb=# SELECT CURRENT_TIMESTAMP;Above PostgreSQL statement will produce the following result-now-------------------------------2013-05-06 14:38:28.078+05:30(1 row)

PostgreSQL74The PostgreSQL WHERE clause is used to specify a condition while fetching the data fromsingle table or joining with multiple tables.If the given condition is satisfied, only then it returns specific value from the table. Youcan filter out rows that you do not want included in the result-set by using the WHEREclause.The WHERE clause not only is used in SELECT statement, but it is also used in UPDATE,DELETE statement, etc., which we would examine in subsequent chapters.SyntaxThe basic syntax of SELECT statement with WHERE clause is as follows-SELECT column1, column2, columnNFROM table_nameWHERE [search_condition]You can specify a search_condition using comparison or logical operators.like >, <, =,LIKE, NOT, etc. The following examples would make this concept clear.ExampleConsider the table COMPANY having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)15. PostgreSQL – WHERE Clause

PostgreSQL75Here are simple examples showing usage of PostgreSQL Logical Operators. FollowingSELECT statement will list down all the records where AGE is greater than or equal to25 AND salary is greater than or equal to 65000.00-testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+------------+--------4 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 85000(2 rows)Following SELECT statement lists down all the records where AGE is greater than or equalto 25 OR salary is greater than or equal to 65000.00-testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+-------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 85000(4 rows)Following SELECT statement lists down all the records where AGE is not NULL, whichmeans all the records, because none of the record has AGE equal to NULL-testdb=# SELECT * FROM COMPANY WHERE AGE IS NOT NULL;The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 85000

PostgreSQL77Following SELECT statement lists down all the records where AGE value is in BETWEEN 25AND 27-testdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;Above PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+------------+--------2 | Allen | 25 | Texas | 150004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 85000(3 rows)Following SELECT statement makes use of SQL subquery where subquery finds all therecords with AGE field having SALARY > 65000 and later WHERE clause is being usedalong with EXISTS operator to list down all the records where AGE from the outside queryexists in the result returned by sub-query:testdb=# SELECT AGE FROM COMPANYWHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);The above given PostgreSQL statement will produce the following result-age-----32252325272224(7 rows)Following SELECT statement makes use of SQL subquery where subquery finds all therecords with AGE field having SALARY > 65000 and later WHERE clause is being usedalong with > operator to list down all the records where AGE from outside query is greaterthan the age in the result returned by sub-query-testdb=# SELECT * FROM COMPANYWHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

PostgreSQL79The PostgreSQL AND and OR operators are used to combine multiple conditions to narrowdown selected data in a PostgreSQL statement. These two operators are called conjunctiveoperators.These operators provide a means to make multiple comparisons with different operatorsin the same PostgreSQL statement.TheANDOperatorThe AND operator allows the existence of multiple conditions in a PostgreSQL statement'sWHERE clause. While using AND operator, complete condition will be assumed true whenall the conditions are true. For example [condition1] AND [condition2] will be true onlywhen both condition1 and condition2 are true.SyntaxThe basic syntax of AND operator with WHERE clause is as follows-SELECT column1, column2, columnNFROM table_nameWHERE [condition1] AND [condition2]...AND [conditionN];You can combine N number of conditions using AND operator. For an action to be takenby the PostgreSQL statement, whether it be a transaction or query, all conditionsseparated by the AND must be TRUE.ExampleConsider the table COMPANY, having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)16. PostgreSQL – AND & OR Conjunctive Operators

PostgreSQL80Following SELECT statement lists down all the records where AGE is greater than or equalto 25 AND salary is greater than or equal to 65000.00-testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+------------+--------4 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 85000(2 rows)TheOROperatorThe OR operator is also used to combine multiple conditions in a PostgreSQL statement'sWHERE clause. While using OR operator, complete condition will be assumed true when atleast any of the conditions is true. For example [condition1] OR [condition2] will be true ifeither condition1 or condition2 is true.SyntaxThe basic syntax of OR operator with WHERE clause is as follows-SELECT column1, column2, columnNFROM table_nameWHERE [condition1] OR [condition2]...OR [conditionN]You can combine N number of conditions using OR operator. For an action to be taken bythe PostgreSQL statement, whether it be a transaction or query, only any ONE of theconditions separated by the OR must be TRUE.ExampleConsider the COMPANY table, having the following records-# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 45000

PostgreSQL82The PostgreSQL UPDATE Query is used to modify the existing records in a table. You canuse WHERE clause with UPDATE query to update the selected rows. Otherwise, all the rowswould be updated.SyntaxThe basic syntax of UPDATE query with WHERE clause is as follows-UPDATE table_nameSET column1 = value1, column2 = value2...., columnN = valueNWHERE [condition];You can combine N number of conditions using AND or OR operators.ExampleConsider the table COMPANY, having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Following is an example, which would update ADDRESS for a customer, whose ID is 6-testdb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;Now, COMPANY table would have the following records-id | name | age | address | salary----+-------+-----+------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 1500017. PostgreSQL – UPDATE Query

PostgreSQL834 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 100003 | Teddy | 23 | Norway | 15000(7 rows)If you want to modify all ADDRESS and SALARY column values in COMPANY table, you donot need to use WHERE clause and UPDATE query would be as follows-testdb=# UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;Now, COMPANY table will have the following records-id | name | age | address | salary----+-------+-----+---------+--------1 | Paul | 32 | Texas | 200002 | Allen | 25 | Texas | 200004 | Mark | 25 | Texas | 200005 | David | 27 | Texas | 200006 | Kim | 22 | Texas | 200007 | James | 24 | Texas | 200003 | Teddy | 23 | Texas | 20000(7 rows)

PostgreSQL84The PostgreSQL DELETE Query is used to delete the existing records from a table. Youcan use WHERE clause with DELETE query to delete the selected rows. Otherwise, all therecords would be deleted.SyntaxThe basic syntax of DELETE query with WHERE clause is as follows-DELETE FROM table_nameWHERE [condition];You can combine N number of conditions using AND or OR operators.ExampleConsider the table COMPANY, having records as follows-# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Following is an example, which would DELETE a customer whose ID is 7-testdb=# DELETE FROM COMPANY WHERE ID = 2;Now, COMPANY table will have the following records-id | name | age | address | salary----+-------+-----+-------------+--------1 | Paul | 32 | California | 200003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 6500018. PostgreSQL – DELETE Query

PostgreSQL855 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 10000(6 rows)If you want to DELETE all the records from COMPANY table, you do not need to use WHEREclause with DELETE queries, which would be as follows-testdb=# DELETE FROM COMPANY;Now, COMPANY table does not have any record because all the records have been deletedby the DELETE statement.

PostgreSQL86The PostgreSQL LIKE operator is used to match text values against a pattern usingwildcards. If the search expression can be matched to the pattern expression, the LIKEoperator will return true, which is 1.There are two wildcards used in conjunction with the LIKE operator- The percent sign (%) The underscore (_)The percent sign represents zero, one, or multiple numbers or characters. The underscorerepresents a single number or character. These symbols can be used in combinations.If either of these two signs is not used in conjunction with the LIKE clause, then the LIKEacts like the equals operator.SyntaxThe basic syntax of % and _ is as follows-SELECT FROM table_nameWHERE column LIKE 'XXXX%'orSELECT FROM table_nameWHERE column LIKE '%XXXX%'orSELECT FROM table_nameWHERE column LIKE 'XXXX_'orSELECT FROM table_nameWHERE column LIKE '_XXXX'orSELECT FROM table_nameWHERE column LIKE '_XXXX_'You can combine N number of conditions using AND or OR operators. Here XXXX could beany numeric or string value.19. PostgreSQL – LIKE Clause

PostgreSQL87ExampleHere are number of examples showing WHERE part having different LIKE clause with '%'and '_' operators-Statement DescriptionWHERE SALARY::text LIKE '200%' Finds any values that start with 200WHERE SALARY::text LIKE '%200%' Finds any values that have 200 in any positionWHERE SALARY::text LIKE '_00%'Finds any values that have 00 in the second andthird positionsWHERE SALARY::text LIKE '2_%_%'Finds any values that start with 2 and are at least3 characters in lengthWHERE SALARY::text LIKE '%2' Finds any values that end with 2WHERE SALARY::text LIKE '_2%3'Finds any values that have 2 in the secondposition and end with a 3WHERE SALARY::text LIKE '2___3'Finds any values in a five-digit number that startwith 2 and end with 3Postgres LIKE is String compare only. Hence, we need to explicitly cast the integer columnto string as in the examples above.Let us take a real example, consider the table COMPANY having records as follows-# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)

PostgreSQL88Following is an example, which would display all the records from COMPANY table whereAGE starts with 2:testdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';This would produce the following result:id | name | age | address | salary----+-------+-----+-------------+--------2 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 100008 | Paul | 24 | Houston | 20000(7 rows)Following is an example, which would display all the records from COMPANY table whereADDRESS will have a hyphen (-) inside the text-testdb=# SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%';This would produce the following result-id | name | age | address | salary----+------+-----+-------------------------------------------+--------4 | Mark | 25 | Rich-Mond | 650006 | Kim | 22 | South-Hall | 45000(2 rows)

PostgreSQL89The PostgreSQL LIMIT clause is used to limit the data amount returned by the SELECTstatement.SyntaxThe basic syntax of SELECT statement with LIMIT clause is as follows-SELECT column1, column2, columnNFROM table_nameLIMIT [no of rows]Following is the syntax of LIMIT clause when it is used along with OFFSET clause-SELECT column1, column2, columnNFROM table_nameLIMIT [no of rows] OFFSET [row num]LIMIT and OFFSET allow you to retrieve just a portion of the rows that are generated bythe rest of the query.ExampleConsider the table COMPANY having records as follows-# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Following is an example, which limits the row in the table according to the number of rowsyou want to fetch from table-testdb=# SELECT * FROM COMPANY LIMIT 4;20. PostgreSQL – LIMIT Clause

PostgreSQL91The PostgreSQL ORDER BY clause is used to sort the data in ascending or descendingorder, based on one or more columns.Syntax-The basic syntax of ORDER BY clause is as follows-SELECT column-listFROM table_name[WHERE condition][ORDER BY column1, column2, .. columnN] [ASC | DESC];You can use more than one column in the ORDER BY clause. Make sure whatever columnyou are using to sort, that column should be available in column-list.ExampleConsider the table COMPANY having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Following is an example, which would sort the result in descending order by SALARY-testdb=# SELECT * FROM COMPANY ORDER BY AGE ASC;21. PostgreSQL – ORDER BY Clause

PostgreSQL92This would produce the following result-id | name | age | address | salary----+-------+-----+------------+--------6 | Kim | 22 | South-Hall | 450003 | Teddy | 23 | Norway | 200007 | James | 24 | Houston | 100008 | Paul | 24 | Houston | 200004 | Mark | 25 | Rich-Mond | 650002 | Allen | 25 | Texas | 150005 | David | 27 | Texas | 850001 | Paul | 32 | California | 200009 | James | 44 | Norway | 500010 | James | 45 | Texas | 5000(10 rows)Following is an example, which would sort the result in descending order by NAME andSALARY-testdb=# SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;This would produce the following result-id | name | age | address | salary----+-------+-----+--------------+--------2 | Allen | 25 | Texas | 150005 | David | 27 | Texas | 8500010 | James | 45 | Texas | 50009 | James | 44 | Norway | 50007 | James | 24 | Houston | 100006 | Kim | 22 | South-Hall | 450004 | Mark | 25 | Rich-Mond | 650001 | Paul | 32 | California | 200008 | Paul | 24 | Houston | 200003 | Teddy | 23 | Norway | 20000(10 rows)Following is an example, which would sort the result in descending order by NAME-testdb=# SELECT * FROM COMPANY ORDER BY NAME DESC;This would produce the following result-

PostgreSQL93id | name | age | address | salary----+-------+-----+------------+--------3 | Teddy | 23 | Norway | 200001 | Paul | 32 | California | 200008 | Paul | 24 | Houston | 200004 | Mark | 25 | Rich-Mond | 650006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 100009 | James | 44 | Norway | 500010 | James | 45 | Texas | 50005 | David | 27 | Texas | 850002 | Allen | 25 | Texas | 15000(10 rows)

PostgreSQL94The PostgreSQL GROUP BY clause is used in collaboration with the SELECT statement togroup together those rows in a table that have identical data. This is done to eliminateredundancy in the output and/or compute aggregates that apply to these groups.The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes theORDER BY clause.SyntaxThe basic syntax of GROUP BY clause is given below. The GROUP BY clause must followthe conditions in the WHERE clause and must precede the ORDER BY clause if one is used.SELECT column-listFROM table_nameWHERE [ conditions ]GROUP BY column1, column2....columnNORDER BY column1, column2....columnNYou can use more than one column in the GROUP BY clause. Make sure whatever columnyou are using to group, that column should be available in column-list.ExampleConsider the table COMPANY having records as follows-# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)22. PostgreSQL – GROUP BY

PostgreSQL95If you want to know the total amount of salary of each customer, then GROUP BY querywould be as follows-testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;This would produce the following result-name | sum-------+-------Teddy | 20000Paul | 20000Mark | 65000David | 85000Allen | 15000Kim | 45000James | 10000(7 rows)Now, let us create three more records in COMPANY table using the following INSERTstatements-INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);Now, our table has the following records with duplicate names-id | name | age | address | salary----+-------+-----+--------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 100008 | Paul | 24 | Houston | 200009 | James | 44 | Norway | 500010 | James | 45 | Texas | 5000(10 rows)

PostgreSQL97In PostgreSQL, the WITH query provides a way to write auxiliary statements for use in alarger query. It helps in breaking down complicated and large queries into simpler forms,which are easily readable. These statements often referred to as Common TableExpressions or CTEs, can be thought of as defining temporary tables that exist just for onequery.The WITH query being CTE query, is particularly useful when subquery is executed multipletimes. It is equally helpful in place of temporary tables. It computes the aggregation onceand allows us to reference it by its name (may be multiple times) in the queries.The WITH clause must be defined before it is used in the query.SyntaxThe basic syntax of WITH query is as follows-WITHname_for_summary_data AS (SELECT Statement)SELECT columnsFROM name_for_summary_dataWHERE conditions <=> (SELECT columnFROM name_for_summary_data)[ORDER BY columns]Where name_for_summary_data is the name given to the WITH clause. Thename_for_summary_data can be the same as an existing table name and will takeprecedence.You can use data-modifying statements (INSERT, UPDATE or DELETE) in WITH. This allowsyou to perform several different operations in the same query.RecursiveWITHRecursive WITH or Hierarchical queries, is a form of CTE where a CTE can reference toitself, i.e., a WITH query can refer to its own output, hence the name recursive.23. PostgreSQL – WITH Clause

PostgreSQL98ExampleConsider the table COMPANY having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Now, let us write a query using the WITH clause to select the records from the above table,as follows-With CTE AS(SelectID, NAME, AGE, ADDRESS, SALARYFROM COMPANY )Select * From CTE;The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)

PostgreSQL99Now, let us write a query using the RECURSIVE keyword along with the WITH clause, tofind the sum of the salaries less than 20000, as follows-WITH RECURSIVE t(n) AS (VALUES (0)UNION ALLSELECT SALARY FROM COMPANY WHERE SALARY < 20000)SELECT sum(n) FROM t;The above given PostgreSQL statement will produce the following result-sum-------25000(1 row)Let us write a query using data modifying statements along with the WITH clause, asshown below.First, create a table COMPANY1 similar to the table COMPANY. The query in the exampleeffectively moves rows from COMPANY to COMPANY1. The DELETE in WITH deletes thespecified rows from COMPANY, returning their contents by means of its RETURNINGclause; and then the primary query reads that output and inserts it into COMPANY1 TABLE.CREATE TABLE COMPANY1(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);WITH moved_rows AS (DELETE FROM COMPANYWHERESALARY >= 30000RETURNING *)INSERT INTO COMPANY1 (SELECT * FROM moved_rows);The above given PostgreSQL statement will produce the following result-INSERT 0 3

PostgreSQL100Now, the records in the tables COMPANY and COMPANY1 are as follows-testdb=# SELECT * FROM COMPANY;id | name | age | address | salary----+-------+-----+------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200007 | James | 24 | Houston | 10000(4 rows)testdb=# SELECT * FROM COMPANY1;id | name | age | address | salary----+-------+-----+-------------+--------4 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 45000(3 rows)

PostgreSQL101The HAVING clause allows us to pick out particular rows where the function's result meetssome condition.The WHERE clause places conditions on the selected columns, whereas the HAVING clauseplaces conditions on groups created by the GROUP BY clause.SyntaxThe following is the position of the HAVING clause in a SELECT query-SELECTFROMWHEREGROUP BYHAVINGORDER BYThe HAVING clause must follow the GROUP BY clause in a query and must also precedethe ORDER BY clause if used. The following is the syntax of the SELECT statement,including the HAVING clause-SELECT column1, column2FROM table1, table2WHERE [ conditions ]GROUP BY column1, column2HAVING [ conditions ]ORDER BY column1, column2ExampleConsider the table COMPANY having records as follows-# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 6500024. PostgreSQL – HAVING Clause

PostgreSQL1025 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Following is an example, which would display record for which the name count is less than2-testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;This would produce the following result-name-------TeddyPaulMarkDavidAllenKimJames(7 rows)Now, let us create three more records in COMPANY table using the following INSERTstatements-INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);Now, our table has the following records with duplicate names-id | name | age | address | salary----+-------+-----+--------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 100008 | Paul | 24 | Houston | 20000

PostgreSQL104The PostgreSQL DISTINCT keyword is used in conjunction with SELECT statement toeliminate all the duplicate records and fetching only unique records.There may be a situation when you have multiple duplicate records in a table. Whilefetching such records, it makes more sense to fetch only unique records instead of fetchingduplicate records.SyntaxThe basic syntax of DISTINCT keyword to eliminate duplicate records is as follows-SELECT DISTINCT column1, column2,.....columnNFROM table_nameWHERE [condition]ExampleConsider the table COMPANY having records as follows-# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Let us add two more records to this table as follows-INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (8, 'Paul', 32, 'California', 20000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (9, 'Allen', 25, 'Texas', 15000.00 );25. PostgreSQL – DISTINCT Keyword

PostgreSQL105Now, the records in the COMPANY table would be-id | name | age | address | salary----+-------+-----+------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 100008 | Paul | 32 | California | 200009 | Allen | 25 | Texas | 15000(9 rows)First, let us see how the following SELECT query returns duplicate salary records-testdb=# SELECT name FROM COMPANY;This would produce the following result-name-------PaulAllenTeddyMarkDavidKimJamesPaulAllen(9 rows)Now, let us use DISTINCT keyword with the above SELECT query and see the result-testdb=# SELECT DISTINCT name FROM COMPANY;

PostgreSQL106This would produce the following result where we do not have any duplicate entry-name-------TeddyPaulMarkDavidAllenKimJames(7 rows)

PostgreSQL107Advanced PostgreSQL

PostgreSQL108Constraints are the rules enforced on data columns on table. These are used to preventinvalid data from being entered into the database. This ensures the accuracy and reliabilityof the data in the database.Constraints could be column level or table level. Column level constraints are applied onlyto one column whereas table level constraints are applied to the whole table. Defining adata type for a column is a constraint in itself. For example, a column of type DATEconstrains the column to valid dates.Following are commonly used constraints available in PostgreSQL. NOT NULL Constraint: Ensures that a column cannot have NULL value. UNIQUE Constraint: Ensures that all values in a column are different. PRIMARY Key: Uniquely identifies each row/record in a database table. FOREIGN Key: Constrains data based on columns in other tables. CHECK Constraint: The CHECK constraint ensures that all values in a columnsatisfy certain conditions. EXCLUSION Constraint: The EXCLUDE constraint ensures that if any two rowsare compared on the specified column(s) or expression(s) using the specifiedoperator(s), not all of these comparisons will return TRUE.NOTNULLConstraintBy default, a column can hold NULL values. If you do not want a column to have a NULLvalue, then you need to define such constraint on this column specifying that NULL is nownot allowed for that column. A NOT NULL constraint is always written as a columnconstraint.A NULL is not the same as no data; rather, it represents unknown data.ExampleFor example, the following PostgreSQL statement creates a new table called COMPANY1and adds five columns, three of which, ID and NAME and AGE, specify not to accept NULLvalues-CREATE TABLE COMPANY1(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),26. PostgreSQL – CONSTRAINTS

PostgreSQL109SALARY REAL);UNIQUEConstraintThe UNIQUE Constraint prevents two records from having identical values in a particularcolumn. In the COMPANY table, for example, you might want to prevent two or morepeople from having identical age.ExampleFor example, the following PostgreSQL statement creates a new table called COMPANY3and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have tworecords with same age-CREATE TABLE COMPANY3(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL UNIQUE,ADDRESS CHAR(50),SALARY REAL DEFAULT 50000.00);PRIMARYKEYConstraintThe PRIMARY KEY constraint uniquely identifies each record in a database table. There canbe more UNIQUE columns, but only one primary key in a table. Primary keys are importantwhen designing the database tables. Primary keys are unique ids.We use them to refer to table rows. Primary keys become foreign keys in other tables,when creating relations among tables. Due to a 'longstanding coding oversight', primarykeys can be NULL in SQLite. This is not the case with other databases.A primary key is a field in a table, which uniquely identifies each row/record in a databasetable. Primary keys must contain unique values. A primary key column cannot have NULLvalues.A table can have only one primary key, which may consist of single or multiple fields.When multiple fields are used as a primary key, they are called a composite key.If a table has a primary key defined on any field(s), then you cannot have two recordshaving the same value of that field(s).

PostgreSQL110ExampleYou already have seen various examples above where we have created COMAPNY4 tablewith ID as primary key.CREATE TABLE COMPANY4(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);FOREIGNKEYConstraintA foreign key constraint specifies that the values in a column (or a group of columns) mustmatch the values appearing in some row of another table. We say this maintains thereferential integrity between two related tables. They are called foreign keys because theconstraints are foreign; that is, outside the table. Foreign keys are sometimes called areferencing key.ExampleFor example, the following PostgreSQL statement creates a new table called COMPANY5and adds five columns.CREATE TABLE COMPANY6(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);For example, the following PostgreSQL statement creates a new table calledDEPARTMENT1, which adds three columns. The column EMP_ID is the foreign key andreferences the ID field of the table COMPANY6.CREATE TABLE DEPARTMENT1(ID INT PRIMARY KEY NOT NULL,DEPT CHAR(50) NOT NULL,EMP_ID INT references COMPANY6(ID));

PostgreSQL111CHECKConstraintThe CHECK Constraint enables a condition to check the value being entered into a record.If the condition evaluates to false, the record violates the constraint and is not enteredinto the table.ExampleFor example, the following PostgreSQL statement creates a new table called COMPANY5and adds five columns. Here, we add a CHECK with SALARY column, so that you cannothave any SALARY as Zero.CREATE TABLE COMPANY5(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL CHECK(SALARY > 0));EXCLUSIONConstraintExclusion constraints ensure that if any two rows are compared on the specified columnsor expressions using the specified operators, at least one of these operator comparisonswill return false or null.ExampleFor example, the following PostgreSQL statement creates a new table called COMPANY7and adds five columns. Here, we add an EXCLUDE constraint.CREATE TABLE COMPANY7(ID INT PRIMARY KEY NOT NULL,NAME TEXT ,AGE INT ,ADDRESS CHAR(50),SALARY REAL,EXCLUDE USING gist(NAME WITH =,AGE WITH <>));

PostgreSQL112Here, USING gist is the type of index to build and use for enforcement.You need to execute the command CREATE EXTENSION btree_gist, once per database.This will install the btree_gist extension, which defines the exclusion constraints on plainscalar data types.As we have enforced the age has to be same, let us see this by inserting records to thetable-INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );INSERT INTO COMPANY7 VALUES(3, 'Allen', 42, 'California', 20000.00 );For the first two INSERT statements, the records are added to the COMPANY7 table. Forthe third INSERT statement, the following error is displayed-ERROR: duplicate key value violates unique constraint "company7_pkey"DETAIL: Key (id)=(3) already exists.DroppingConstraintsTo remove a constraint you need to know its name. If the name is known, it is easy todrop. Else, you need to find out the system-generated name. The psql command d tablename can be helpful here. The general syntax is-ALTER TABLE table_name DROP CONSTRAINT some_name;

PostgreSQL113The PostgreSQL Joins clause is used to combine records from two or more tables in adatabase. A JOIN is a means for combining fields from two tables by using values commonto each.Join Types in PostgreSQL are- The CROSS JOIN The INNER JOIN The LEFT OUTER JOIN The RIGHT OUTER JOIN The FULL OUTER JOINBefore we proceed, let us consider two tables, COMPANY and DEPARTMENT. We alreadyhave seen INSERT statements to populate COMPANY table. So just let us assume the listof records available in COMPANY table.id | name | age | address | salary | join_date----+-------+-----+-----------+--------+-----------1 | Paul | 32 | California| 20000 | 2001-07-133 | Teddy | 23 | Norway | 20000 |4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-135 | David | 27 | Texas | 85000 | 2007-12-132 | Allen | 25 | Texas | | 2007-12-138 | Paul | 24 | Houston | 20000 | 2005-07-139 | James | 44 | Norway | 5000 | 2005-07-1310 | James | 45 | Texas | 5000 | 2005-07-13Another table is DEPARTMENT, has the following definition-CREATE TABLE DEPARTMENT(ID INT PRIMARY KEY NOT NULL,DEPT CHAR(50) NOT NULL,EMP_ID INT NOT NULL);27. PostgreSQL – JOINS

PostgreSQL114Here is the list of INSERT statements to populate DEPARTMENT table-INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)VALUES (1, 'IT Billing', 1 );INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)VALUES (2, 'Engineering', 2 );INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)VALUES (3, 'Finance', 7 );Finally, we have the following list of records available in DEPARTMENT table-id | dept | emp_id----+-------------+--------1 | IT Billing | 12 | Engineering | 23 | Finance | 7TheCROSSJOINA CROSS JOIN matches every row of the first table with every row of the second table. Ifthe input tables have x and y columns, respectively, the resulting table will have x+ycolumns. Because CROSS JOINs have the potential to generate extremely large tables,care must be taken to use them only when appropriate.Following is the syntax of CROSS JOIN-SELECT ... FROM table1 CROSS JOIN table2 ...Based on the above tables, we can write a CROSS JOIN as follows-testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;The above given query will produce the following result-emp_id| name | dept------|-------|--------------1 | Paul | IT Billing1 | Teddy | IT Billing1 | Mark | IT Billing1 | David | IT Billing1 | Allen | IT Billing

PostgreSQL116The above given query will produce the following result-emp_id | name | dept--------+-------+------------1 | Paul | IT Billing2 | Allen | EngineeringTheLEFTOUTERJOINThe OUTER JOIN is an extension of the INNER JOIN. SQL standard defines three types ofOUTER JOINs: LEFT, RIGHT, and FULL and PostgreSQL supports all of these.In case of LEFT OUTER JOIN, an inner join is performed first. Then, for each row in tableT1 that does not satisfy the join condition with any row in table T2, a joined row is addedwith null values in columns of T2. Thus, the joined table always has at least one row foreach row in T1.Following is the syntax of LEFT OUTER JOIN-SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...Based on the above tables, we can write an inner join as follows-testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENTON COMPANY.ID = DEPARTMENT.EMP_ID;The above given query will produce the following result-emp_id | name | dept--------+-------+------------1 | Paul | IT Billing2 | Allen | Engineering| James || David || Paul || Mark || Teddy || James |

PostgreSQL117TheRIGHTOUTERJOINFirst, an inner join is performed. Then, for each row in table T2 that does not satisfy thejoin condition with any row in table T1, a joined row is added with null values in columnsof T1. This is the converse of a left join; the result table will always have a row for eachrow in T2.Following is the syntax of LEFT OUTER JOIN-SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...Based on the above tables, we can write an inner join as follows-testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENTON COMPANY.ID = DEPARTMENT.EMP_ID;The above given query will produce the following result-emp_id | name | dept--------+-------+--------1 | Paul | IT Billing2 | Allen | Engineering7 | | FinanceTheFULLOUTERJOINFirst, an inner join is performed. Then, for each row in table T1 that does not satisfy thejoin condition with any row in table T2, a joined row is added with null values in columnsof T2. In addition, for each row of T2 that does not satisfy the join condition with any rowin T1, a joined row with null values in the columns of T1 is added.Following is the syntax of FULL OUTER JOIN-SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...Based on the above tables, we can write an inner join as follows-testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENTON COMPANY.ID = DEPARTMENT.EMP_ID;

PostgreSQL118The above query will produce the following result-emp_id | name | dept--------+-------+---------------1 | Paul | IT Billing2 | Allen | Engineering7 | | Finance| James || David || Paul || Mark || Teddy || James |

PostgreSQL119The PostgreSQL UNION clause/operator is used to combine the results of two or moreSELECT statements without returning any duplicate rows.To use UNION, each SELECT must have the same number of columns selected, the samenumber of column expressions, the same data type, and have them in the same order butthey do not have to be the same length.SyntaxThe basic syntax of UNION is as follows-SELECT column1 [, column2 ]FROM table1 [, table2 ][WHERE condition]UNIONSELECT column1 [, column2 ]FROM table1 [, table2 ][WHERE condition]Here, given condition could be any given expression based on your requirement.ExampleConsider the following two tables, (a) COMPANY table is as follows-testdb=# SELECT * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)28. PostgreSQL – UNIONS Clause

PostgreSQL121TheUNIONALLClauseThe UNION ALL operator is used to combine the results of two SELECT statementsincluding duplicate rows. The same rules that apply to UNION apply to the UNION ALLoperator as well.Syntax:The basic syntax of UNION ALL is as follows-SELECT column1 [, column2 ]FROM table1 [, table2 ][WHERE condition]UNION ALLSELECT column1 [, column2 ]FROM table1 [, table2 ][WHERE condition]Here, given condition could be any given expression based on your requirement.ExampleNow, let us join above-mentioned two tables in our SELECT statement as follows-testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENTON COMPANY.ID = DEPARTMENT.EMP_IDUNION ALLSELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENTON COMPANY.ID = DEPARTMENT.EMP_ID;This would produce the following result-emp_id | name | dept--------+-------+--------------1 | Paul | IT Billing2 | Allen | Engineering7 | James | Finance3 | Teddy | Engineering4 | Mark | Finance5 | David | Engineering6 | Kim | Finance1 | Paul | IT Billing2 | Allen | Engineering

PostgreSQL123The PostgreSQL NULL is the term used to represent a missing value. A NULL value in atable is a value in a field that appears to be blank.A field with a NULL value is a field with no value. It is very important to understand thata NULL value is different from a zero value or a field that contains spaces.SyntaxThe basic syntax of using NULL while creating a table is as follows-CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);Here, NOT NULL signifies that column should always accept an explicit value of the givendata type. There are two columns where we did not use NOT NULL. Hence, this meansthese columns could be NULL.A field with a NULL value is one that has been left blank during record creation.ExampleThe NULL value can cause problems when selecting data because when comparing anunknown value to any other value, the result is always unknown and not included in thefinal results. Consider the following table, COMPANY having the following records-ID NAME AGE ADDRESS SALARY---------- ---------- ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.029. PostgreSQL – NULL Values

PostgreSQL124Let us use the UPDATE statement to set few nullable values as NULL as follows-testdb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);Now, COMPANY table should have the following records-id | name | age | address | salary----+-------+-----+-------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | |7 | James | 24 | |(7 rows)Next, let us see the usage of IS NOT NULL operator to list down all the records whereSALARY is not NULL-testdb=# SELECT ID, NAME, AGE, ADDRESS, SALARYFROM COMPANYWHERE SALARY IS NOT NULL;The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 85000(5 rows)Following is the usage of IS NULL operator, which will list down all the records whereSALARY is NULL-testdb=# SELECT ID, NAME, AGE, ADDRESS, SALARYFROM COMPANYWHERE SALARY IS NULL;

PostgreSQL125The above given PostgreSQL statement will produce the following result-id | name | age | address | salary----+-------+-----+---------+--------6 | Kim | 22 | |7 | James | 24 | |(2 rows)

PostgreSQL126You can rename a table or a column temporarily by giving another name, which is knownas ALIAS. The use of table aliases means to rename a table in a particular PostgreSQLstatement. Renaming is a temporary change and the actual table name does not changein the database.The column aliases are used to rename a table's columns for the purpose of a particularPostgreSQL query.SyntaxThe basic syntax of table alias is as follows-SELECT column1, column2....FROM table_name AS alias_nameWHERE [condition];The basic syntax of column alias is as follows-SELECT column_name AS alias_nameFROM table_nameWHERE [condition];ExampleConsider the following two tables, (a) COMPANY table is as follows-testdb=# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)30. PostgreSQL – ALIAS Syntax

PostgreSQL129PostgreSQL Triggers are database callback functions, which are automaticallyperformed/invoked when a specified database event occurs.Following are important points about PostgreSQL triggers- PostgreSQL trigger can be specified to fire –o Before the operation is attempted on a row (before constraints are checkedand the INSERT, UPDATE or DELETE is attempted)o After the operation has completed (after constraints are checked and theINSERT, UPDATE, or DELETE has completed)o Instead of the operation (in the case of inserts, updates or deletes on aview) A trigger that is marked FOR EACH ROW is called once for every row that theoperation modifies. In contrast, a trigger that is marked FOR EACH STATEMENTonly executes once for any given operation, regardless of how many rows itmodifies. Both, the WHEN clause and the trigger actions, may access elements of the rowbeing inserted, deleted or updated using references of the form NEW.column-name and OLD.column-name, where column-name is the name of a columnfrom the table that the trigger is associated with. If a WHEN clause is supplied, the PostgreSQL statements specified are onlyexecuted for rows for which the WHEN clause is true. If no WHEN clause is supplied,the PostgreSQL statements are executed for all rows. If multiple triggers of the same kind are defined for the same event, they will befired in alphabetical order by name. The BEFORE, AFTER or INSTEAD OF keyword determines when the trigger actionswill be executed relative to the insertion, modification or removal of the associatedrow. Triggers are automatically dropped when the table that they are associated with isdropped. The table to be modified must exist in the same database as the table or view towhich the trigger is attached and one must use just tablename,not database.tablename.31. PostgreSQL – TRIGGERS

PostgreSQL130 A CONSTRAINT option when specified creates a constraint trigger. This is the sameas a regular trigger except that the timing of the trigger firing can be adjustedusing SET CONSTRAINTS. Constraint triggers are expected to raise an exceptionwhen the constraints they implement are violated.SyntaxThe basic syntax of creating a trigger is as follows-CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_nameON table_name[-- Trigger logic goes here....];Here, event_name could be INSERT, DELETE, UPDATE, and TRUNCATE databaseoperation on the mentioned table table_name. You can optionally specify FOR EACH ROWafter table name.Following is the syntax of creating a trigger on an UPDATE operation on one or morespecified columns of a table as follows-CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_nameON table_name[-- Trigger logic goes here....];ExampleLet us consider a case where we want to keep audit trial for every record being insertedin COMPANY table, which we will create newly as follows (Drop COMPANY table if youalready have it).testdb=# CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);

PostgreSQL131To keep audit trial, we will create a new table called AUDIT where log messages will beinserted whenever there is an entry in COMPANY table for a new record:testdb=# CREATE TABLE AUDIT(EMP_ID INT NOT NULL,ENTRY_DATE TEXT NOT NULL);Here, ID is the AUDIT record ID, and EMP_ID is the ID, which will come from COMPANYtable, and DATE will keep timestamp when the record will be created in COMPANY table.So now, let us create a trigger on COMPANY table as follows-testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANYFOR EACH ROW EXECUTE PROCEDURE auditlogfunc();Where auditlogfunc() is a PostgreSQL procedure and has the following definition-CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$BEGININSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID,current_timestamp);RETURN NEW;END;$example_table$ LANGUAGE plpgsql;Now, we will start the actual work. Let us start inserting record in COMPANY table, whichshould result in creating an audit log record in AUDIT table. So let us create one record inCOMPANY table as follows-testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 );This will create one record in COMPANY table, which is as follows-id | name | age | address | salary----+------+-----+--------------+--------1 | Paul | 32 | California | 20000Same time, one record will be created in AUDIT table. This record is the result of a trigger,which we have created on INSERT operation on COMPANY table. Similarly, you can createyour triggers on UPDATE and DELETE operations based on your requirements.emp_id | entry_date--------+-------------------------------1 | 2013-05-05 15:49:59.968+05:30(1 row)

PostgreSQL132ListingTRIGGERSYou can list down all the triggers in the current database from pg_trigger table as follows-testdb=# SELECT * FROM pg_trigger;The above given PostgreSQL statement will list down all triggers.If you want to list the triggers on a particular table, then use AND clause with table nameas follows-testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid ANDrelname='company';The above given PostgreSQL statement will also list down only one entry as follows-tgname-----------------example_trigger(1 row)DroppingTRIGGERSFollowing is the DROP command, which can be used to drop an existing trigger-testdb=# DROP TRIGGER trigger_name;

PostgreSQL133Indexes are special lookup tables that the database search engine can use to speed updata retrieval. Simply put, an index is a pointer to data in a table. An index in a databaseis very similar to an index in the back of a book.For example, if you want to reference all pages in a book that discusses a certain topic,you have to first refer to the index, which lists all topics alphabetically and then refer toone or more specific page numbers.An index helps to speed up SELECT queries and WHERE clauses; however, it slows downdata input, with UPDATE and INSERT statements. Indexes can be created or dropped withno effect on the data.Creating an index involves the CREATE INDEX statement, which allows you to name theindex, to specify the table and which column or columns to index, and to indicate whetherthe index is in ascending or descending order.Indexes can also be unique, similar to the UNIQUE constraint, in that the index preventsduplicate entries in the column or combination of columns on which there's an index.The CREATE INDEX CommandThe basic syntax of CREATE INDEX is as follows-CREATE INDEX index_name ON table_name;IndexTypesPostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST and GIN. EachIndex type uses a different algorithm that is best suited to different types of queries. Bydefault, the CREATE INDEX command creates B-tree indexes, which fit the most commonsituations.Single-Column IndexesA single-column index is one that is created based on only one table column. The basicsyntax is as follows-CREATE INDEX index_nameON table_name (column_name);32. PostgreSQL – INDEXES

PostgreSQL134Multicolumn IndexesA multicolumn index is defined on more than one column of a table. The basic syntax isas follows-CREATE INDEX index_nameON table_name (column1_name, column2_name);Whether to create a single-column index or a multicolumn index, take into considerationthe column(s) that you may use very frequently in a query's WHERE clause as filterconditions.Should there be only one column used, a single-column index should be the choice. Shouldthere be two or more columns that are frequently used in the WHERE clause as filters, themulticolumn index would be the best choice.Unique IndexesUnique indexes are used not only for performance, but also for data integrity. A uniqueindex does not allow any duplicate values to be inserted into the table. The basic syntaxis as follows-CREATE UNIQUE INDEX index_nameon table_name (column_name);Partial IndexesA partial index is an index built over a subset of a table; the subset is defined by aconditional expression (called the predicate of the partial index). The index contains entriesonly for those table rows that satisfy the predicate. The basic syntax is as follows-CREATE INDEX index_nameon table_name (conditional_expression);Implicit IndexesImplicit indexes are indexes that are automatically created by the database server whenan object is created. Indexes are automatically created for primary key constraints andunique constraints.ExampleFollowing is an example where we will create an index on COMPANY table for salarycolumn-# CREATE INDEX salary_index ON COMPANY (salary);

PostgreSQL135Now, let us list down all the indices available on COMPANY table using dcompany command.# d companyThis will produce the following result, where company_pkey is an implicit index, which gotcreated when the table was created.Table "public.company"Column | Type | Modifiers---------+---------------+-----------id | integer | not nullname | text | not nullage | integer | not nulladdress | character(50) |salary | real |Indexes:"company_pkey" PRIMARY KEY, btree (id)"salary_index" btree (salary)You can list down the entire indexes database wide using the di command.TheDROPINDEXCommandAn index can be dropped using PostgreSQL DROP command. Care should be taken whendropping an index because performance may be slowed or improved.The basic syntax is as follows-DROP INDEX index_name;You can use following statement to delete previously created index-# DROP INDEX salary_index;WhenShouldIndexesbeAvoided?Although indexes are intended to enhance a database's performance, there are times whenthey should be avoided. The following guidelines indicate when the use of an index shouldbe reconsidered- Indexes should not be used on small tables. Tables that have frequent, large batch update or insert operations. Indexes should not be used on columns that contain a high number of NULL values. Columns that are frequently manipulated should not be indexed.

PostgreSQL136The PostgreSQL ALTER TABLE command is used to add, delete or modify columns in anexisting table.You would also use ALTER TABLE command to add and drop various constraints on anexisting table.Syntax-The basic syntax of ALTER TABLE to add a new column in an existing table is as follows-ALTER TABLE table_name ADD column_name datatype;The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows-ALTER TABLE table_name DROP COLUMN column_name;The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is asfollows-ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table isas follows-ALTER TABLE table_name MODIFY column_name datatype NOT NULL;The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows-ALTER TABLE table_nameADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);The basic syntax of ALTER TABLE to ADD CHECK CONSTRAINT to a table is as follows-ALTER TABLE table_nameADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is asfollows-ALTER TABLE table_nameADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);33. PostgreSQL – ALTER TABLE Command

PostgreSQL137The basic syntax of ALTER TABLE to DROP CONSTRAINT from a table is as follows-ALTER TABLE table_nameDROP CONSTRAINT MyUniqueConstraint;If you are using MySQL, the code is as follows-ALTER TABLE table_nameDROP INDEX MyUniqueConstraint;The basic syntax of ALTER TABLE to DROP PRIMARY KEY constraint from a table is asfollows-ALTER TABLE table_nameDROP CONSTRAINT MyPrimaryKey;If you are using MySQL, the code is as follows-ALTER TABLE table_nameDROP PRIMARY KEY;ExampleConsider our COMPANY table has the following records-id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000Following is the example to ADD a new column in an existing table-testdb=# ALTER TABLE COMPANY ADD GENDER char(1);Now, COMPANY table is changed and the following would be the output from SELECTstatement-id | name | age | address | salary | gender----+-------+-----+-------------+--------+--------1 | Paul | 32 | California | 20000 |

PostgreSQL1382 | Allen | 25 | Texas | 15000 |3 | Teddy | 23 | Norway | 20000 |4 | Mark | 25 | Rich-Mond | 65000 |5 | David | 27 | Texas | 85000 |6 | Kim | 22 | South-Hall | 45000 |7 | James | 24 | Houston | 10000 |(7 rows)Following is the example to DROP gender column from existing table-testdb=# ALTER TABLE COMPANY DROP GENDER;Now, COMPANY table is changed and following would be output from SELECT statement-id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000

PostgreSQL139The PostgreSQL TRUNCATE TABLE command is used to delete complete data from anexisting table. You can also use DROP TABLE command to delete complete table but itwould remove complete table structure from the database and you would need to re-createthis table once again if you wish to store some data.It has the same effect as DELETE on each table, but since it does not actually scan thetables, it is faster. Furthermore, it reclaims disk space immediately, rather than requiringa subsequent VACUUM operation. This is most useful on large tables.SyntaxThe basic syntax of TRUNCATE TABLE is as follows-TRUNCATE TABLE table_name;ExampleConsider the COMPANY table has the following records-id | name | age | address | salary----+-------+-----+------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 10000(7 rows)Following is the example to truncate-testdb=# TRUNCATE TABLE COMPANY;Now, COMPANY table is truncated and the following would be the output of SELECTstatement-testdb=# SELECT * FROM CUSTOMERS;id | name | age | address | salary----+------+-----+---------+--------(0 rows)34. PostgreSQL – TRUNCATE TABLE Command

PostgreSQL140Views are pseudo-tables. That is, they are not real tables; nevertheless appear as ordinarytables to SELECT. A view can represent a subset of a real table, selecting certain columnsor certain rows from an ordinary table. A view can even represent joined tables. Becauseviews are assigned separate permissions, you can use them to restrict table access so thatthe users see only specific rows or columns of a table.A view can contain all rows of a table or selected rows from one or more tables. A viewcan be created from one or many tables, which depends on the written PostgreSQL queryto create a view.Views, which are kind of virtual tables, allow users to do the following- Structure data in a way that users or classes of users find natural or intuitive. Restrict access to the data such that a user can only see limited data instead ofcomplete table. Summarize data from various tables, which can be used to generate reports.Since views are not ordinary tables, you may not be able to execute a DELETE, INSERT,or UPDATE statement on a view. However, you can create a RULE to correct this problemof using DELETE, INSERT or UPDATE on a view.CreatingViewsThe PostgreSQL views are created using the CREATE VIEW statement. The PostgreSQLviews can be created from a single table, multiple tables, or another view.The basic CREATE VIEW syntax is as follows-CREATE [TEMP | TEMPORARY] VIEW view_name ASSELECT column1, column2.....FROM table_nameWHERE [condition];You can include multiple tables in your SELECT statement in very similar way as you usethem in normal PostgreSQL SELECT query. If the optional TEMP or TEMPORARY keywordis present, the view will be created in the temporary space. Temporary views areautomatically dropped at the end of the current session.35. PostgreSQL – VIEWS

PostgreSQL141ExampleConsider, the COMPANY table is having the following records-id | name | age | address | salary----+-------+-----+------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 10000Now, following is an example to create a view from COMPANY table. This view would beused to have only few columns from COMPANY table.testdb=# CREATE VIEW COMPANY_VIEW ASSELECT ID, NAME, AGEFROM COMPANY;Now, you can query COMPANY_VIEW in a similar way as you query an actual table.Following is the example-testdb=# SELECT * FROM COMPANY_VIEW;This would produce the following result-id | name | age----+-------+-----1 | Paul | 322 | Allen | 253 | Teddy | 234 | Mark | 255 | David | 276 | Kim | 227 | James | 24(7 rows)

PostgreSQL142DroppingViewsTo drop a view, simply use the DROP VIEW statement with the view_name. The basicDROP VIEW syntax is as follows-testdb=# DROP VIEW view_name;Following command will delete COMPANY_VIEW view, which we created in the last section-testdb=# DROP VIEW COMPANY_VIEW;

PostgreSQL143A transaction is a unit of work that is performed against a database. Transactions are unitsor sequences of work accomplished in a logical order, whether in a manual fashion by auser or automatically by some sort of a database program.A transaction is the propagation of one or more changes to the database. For example, ifyou are creating a record, updating a record, or deleting a record from the table, then youare performing transaction on the table. It is important to control transactions to ensuredata integrity and to handle database errors.Practically, you will club many PostgreSQL queries into a group and you will execute all ofthem together as a part of a transaction.Properties of TransactionsTransactions have the following four standard properties, usually referred to by theacronym ACID- Atomicity: Ensures that all operations within the work unit are completedsuccessfully; otherwise, the transaction is aborted at the point of failure andprevious operations are rolled back to their former state. Consistency: Ensures that the database properly changes states upon asuccessfully committed transaction. Isolation: Enables transactions to operate independently of and transparent toeach other. Durability: Ensures that the result or effect of a committed transaction persists incase of a system failure.TransactionControlThe following commands are used to control transactions- BEGIN TRANSACTION: To start a transaction. COMMIT: To save the changes, alternatively you can use ENDTRANSACTION command. ROLLBACK: To rollback the changes.Transactional control commands are only used with the DML commands INSERT, UPDATEand DELETE only. They cannot be used while creating tables or dropping them becausethese operations are automatically committed in the database.36. PostgreSQL – TRANSACTIONS

PostgreSQL144The BEGIN TRANSACTION CommandTransactions can be started using BEGIN TRANSACTION or simply BEGIN command. Suchtransactions usually persist until the next COMMIT or ROLLBACK command is encountered.But a transaction will also ROLLBACK if the database is closed or if an error occurs.Following is the simple syntax to start a transaction:BEGIN;orBEGIN TRANSACTION;TheCOMMITCommandThe COMMIT command is the transactional command used to save changes invoked by atransaction to the database.The COMMIT command saves all transactions to the database since the last COMMIT orROLLBACK command.The syntax for COMMIT command is as follows-COMMIT;orEND TRANSACTION;TheROLLBACKCommandThe ROLLBACK command is the transactional command used to undo transactions thathave not already been saved to the database.The ROLLBACK command can only be used to undo transactions since the last COMMIT orROLLBACK command was issued.The syntax for ROLLBACK command is as follows-ROLLBACK;ExampleConsider the COMPANY table, having the following records-id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 65000

PostgreSQL1455 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000Now, let us start a transaction and delete records from the table having age = 25 andfinally we use ROLLBACK command to undo all the changes.testdb=# BEGIN;DELETE FROM COMPANY WHERE AGE = 25;ROLLBACK;If you will check COMPANY table is still having the following records-id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000Now, let us start another transaction and delete records from the table having age = 25and finally we use COMMIT command to commit all the changes.testdb=# BEGIN;DELETE FROM COMPANY WHERE AGE = 25;COMMIT;If you will check the COMPANY table, it still has the following records-id | name | age | address | salary----+-------+-----+------------+--------1 | Paul | 32 | California | 200003 | Teddy | 23 | Norway | 200005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 10000(5 rows)

PostgreSQL146Locks or Exclusive Locks or Write Locks prevent users from modifying a row or an entiretable. Rows modified by UPDATE and DELETE are then exclusively locked automatically forthe duration of the transaction. This prevents other users from changing the row until thetransaction is either committed or rolled back.The only time when users must wait for other users is when they are trying to modify thesame row. If they modify different rows, no waiting is necessary. SELECT queries neverhave to wait.The database performs locking automatically. In certain cases, however, locking must becontrolled manually. Manual locking can be done by using the LOCK command. It allowsspecification of a transaction's lock type and scope.Syntax for LOCK commandThe basic syntax for LOCK command is as follows-LOCK [ TABLE ]nameINlock_mode name: The name (optionally schema-qualified) of an existing table to lock. If ONLYis specified before the table name, only that table is locked. If ONLY is not specified,the table and all its descendant tables (if any) are locked. lock_mode: The lock mode specifies which locks this lock conflicts with. If no lockmode is specified, then ACCESS EXCLUSIVE, the most restrictive mode, is used.Possible values are: ACCESS SHARE, ROW SHARE , ROW EXCLUSIVE, SHAREUPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESSEXCLUSIVE.Once obtained, the lock is held for the remainder of the current transaction. There is noUNLOCK TABLE command; locks are always released at the transaction end.DeadLocksDeadlocks can occur when two transactions are waiting for each other to finish theiroperations. While PostgreSQL can detect them and end them with a ROLLBACK, deadlockscan still be inconvenient. To prevent your applications from running into this problem,make sure to design them in such a way that they will lock objects in the same order.37. PostgreSQL – LOCKS

PostgreSQL147AdvisoryLocksPostgreSQL provides means for creating locks that have application-defined meanings.These are called advisory locks. As the system does not enforce their use, it is up to theapplication to use them correctly. Advisory locks can be useful for locking strategies thatare an awkward fit for the MVCC model.For example, a common use of advisory locks is to emulate pessimistic locking strategiestypical of the so-called "flat file" data management systems. While a flag stored in a tablecould be used for the same purpose, advisory locks are faster, avoid table bloat, and areautomatically cleaned up by the server at the end of the session.ExampleConsider the table COMPANY having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)The following example locks the COMPANY table within the testdb database in ACCESSEXCLUSIVE mode. The LOCK statement works only in a transaction mode.testdb=#BEGIN;LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;The above given PostgreSQL statement will produce the following result-LOCK TABLEThe above message indicates that the table is locked until the transaction ends and tofinish the transaction you will have to either rollback or commit the transaction.

PostgreSQL148A subquery or Inner query or Nested query is a query within another PostgreSQL queryand embedded within the WHERE clause.A subquery is used to return data that will be used in the main query as a condition tofurther restrict the data to be retrieved.Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements alongwith the operators like =, <, >, >=, <=, IN, etc.There are a few rules that subqueries must follow. Subqueries must be enclosed within parentheses. A subquery can have only one column in the SELECT clause, unless multiplecolumns are in the main query for the subquery to compare its selected columns. An ORDER BY cannot be used in a subquery, although the main query can use anORDER BY. The GROUP BY can be used to perform the same function as the ORDERBY in a subquery. Subqueries that return more than one row can only be used with multiple valueoperators, such as the IN, EXISTS, NOT IN, ANY/SOME, ALL operator. The BETWEEN operator cannot be used with a subquery; however, the BETWEENcan be used within the subquery.SubquerieswiththeSELECTStatementSubqueries are most frequently used with the SELECT statement. The basic syntax is asfollows-SELECT column_name [, column_name ]FROM table1 [, table2 ]WHERE column_name OPERATOR(SELECT column_name [, column_name ]FROM table1 [, table2 ][WHERE])38. PostgreSQL – Sub Queries

PostgreSQL149ExampleConsider the COMPANY table having the following records-id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Now, let us check the following sub-query with SELECT statement-testdb=# SELECT *FROM COMPANYWHERE ID IN (SELECT IDFROM COMPANYWHERE SALARY > 45000) ;This would produce the following result-id | name | age | address | salary----+-------+-----+-------------+--------4 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 85000(2 rows)SubquerieswiththeINSERTStatementSubqueries also can be used with INSERT statements. The INSERT statement uses thedata returned from the subquery to insert into another table. The selected data in thesubquery can be modified with any of the character, date, or number functions.The basic syntax is as follows-INSERT INTO table_name [ (column1 [, column2 ]) ]SELECT [ *|column1 [, column2 ]FROM table1 [, table2 ][ WHERE VALUE OPERATOR ]

PostgreSQL150ExampleConsider a table COMPANY_BKP, with similar structure as COMPANY table and can becreated using the same CREATE TABLE using COMPANY_BKP as the table name. Now, tocopy complete COMPANY table into COMPANY_BKP, following is the syntax-testdb=# INSERT INTO COMPANY_BKPSELECT * FROM COMPANYWHERE ID IN (SELECT IDFROM COMPANY) ;SubquerieswiththeUPDATEStatementThe subquery can be used in conjunction with the UPDATE statement. Either single ormultiple columns in a table can be updated when using a subquery with the UPDATEstatement.The basic syntax is as follows-UPDATE tableSET column_name = new_value[ WHERE OPERATOR [ VALUE ](SELECT COLUMN_NAMEFROM TABLE_NAME)[ WHERE) ]ExampleAssuming, we have COMPANY_BKP table available, which is backup of the COMPANY table.The following example updates SALARY by 0.50 times in the COMPANY table for all thecustomers, whose AGE is greater than or equal to 27-testdb=# UPDATE COMPANYSET SALARY = SALARY * 0.50WHERE AGE IN (SELECT AGE FROM COMPANY_BKPWHERE AGE >= 27 );

PostgreSQL151This would affect two rows and finally the COMPANY table would have the followingrecords-id | name | age | address | salary----+-------+-----+-------------+--------2 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 100001 | Paul | 32 | California | 100005 | David | 27 | Texas | 42500(7 rows)SubquerieswiththeDELETEStatementThe subquery can be used in conjunction with the DELETE statement like with any otherstatements mentioned above.The basic syntax is as follows-DELETE FROM TABLE_NAME[ WHERE OPERATOR [ VALUE ](SELECT COLUMN_NAMEFROM TABLE_NAME)[ WHERE) ]ExampleAssuming, we have COMPANY_BKP table available, which is a backup of the COMPANYtable.The following example deletes records from the COMPANY table for all the customers,whose AGE is greater than or equal to 27-testdb=# DELETE FROM COMPANYWHERE AGE IN (SELECT AGE FROM COMPANY_BKPWHERE AGE > 27 );

PostgreSQL152This would affect two rows and finally the COMPANY table would have the followingrecords-id | name | age | address | salary----+-------+-----+-------------+--------2 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 100005 | David | 27 | Texas | 42500(6 rows)

PostgreSQL153PostgreSQL has the data types – smallserial, serial and bigserial; these are not true types,but merely a notational convenience for creating unique identifier columns. These aresimilar to AUTO_INCREMENT property supported by some other databases.If you wish a serial column to have a unique constraint or be a primary key, it must nowbe specified, just like any other data type.The type name serial creates an integer columns. The type name bigserial creates a bigintcolumn. Bigserial should be used if you anticipate the use of more than 231 identifiersover the lifetime of the table. The type name smallserial creates a smallint column.SyntaxThe basic usage of SERIAL dataype is as follows-CREATE TABLE tablename (colname SERIAL);ExampleConsider the COMPANY table to be created as follows-testdb=# CREATE TABLE COMPANY(ID SERIAL PRIMARY KEY,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);Now, insert the following records into table COMPANY-INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( 'Paul', 32, 'California', 20000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ('Allen', 25, 'Texas', 15000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)39. PostgreSQL – AUTO INCREMENT

PostgreSQL154VALUES ('Teddy', 23, 'Norway', 20000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( 'David', 27, 'Texas', 85000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( 'James', 24, 'Houston', 10000.00 );This will insert seven tuples into the table COMPANY and COMPANY will have the followingrecords-id | name | age | address | salary----+-------+-----+------------+--------1 | Paul | 32 | California | 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall | 450007 | James | 24 | Houston | 10000

PostgreSQL155Whenever an object is created in a database, an owner is assigned to it. The owner isusually the one who executed the creation statement. For most kinds of objects, the initialstate is that only the owner (or a superuser) can modify or delete the object. To allowother roles or users to use it, privileges or permission must be granted.Different kinds of privileges in PostgreSQL are- SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGEDepending on the type of the object (table, function, etc.,), privileges are applied to theobject. To assign privileges to the users, the GRANT command is used.Syntax for GRANTBasic syntax for GRANT command is as follows-GRANT privilege [, ...]ON object [, ...]TO { PUBLIC | GROUP group | username } privilege values could be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL. object: The name of an object to which to grant access. The possible objects are:table, view, sequence PUBLIC: A short form representing all users. GROUP group: A group to whom to grant privileges.40. PostgreSQL – PRIVILEGES

PostgreSQL156 username: The name of a user to whom to grant privileges. PUBLIC is a shortform representing all users.The privileges can be revoked using the REVOKE command.Syntax for REVOKEBasic syntax for REVOKE command is as follows-REVOKE privilege [, ...]ON object [, ...]FROM { PUBLIC | GROUP groupname | username } privilege values could be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL. object: The name of an object to which to grant access. The possible objects are:table, view, sequence PUBLIC: A short form representing all users. GROUP group: A group to whom to grant privileges. username: The name of a user to whom to grant privileges. PUBLIC is a shortform representing all users.ExampleTo understand the privileges, let us first create a USER as follows-testdb=# CREATE USER manisha WITH PASSWORD 'password';CREATE ROLEThe message CREATE ROLE indicates that the USER "manisha" is created.Consider the table COMPANY having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)

PostgreSQL157Next, let us grant all privileges on a table COMPANY to the user "manisha" as follows-testdb=# GRANT ALL ON COMPANY TO manisha;GRANTThe message GRANT indicates that all privileges are assigned to the USER.Next, let us revoke the privileges from the USER "manisha" as follows-testdb=# REVOKE ALL ON COMPANY FROM manisha;REVOKEThe message REVOKE indicates that all privileges are revoked from the USER.You can even delete the user as follows-testdb=# DROP USER manisha;DROP ROLEThe message DROP ROLE indicates USER ‘Manisha’ is deleted from the database.

PostgreSQL158We had discussed about the Date/Time data types in the chapter Data Types. Now, let ussee the Date/Time operators and Functions.The following table lists the behaviors of the basic arithmetic operators-Operator Example Result+ date '2001-09-28' + integer '7' date '2001-10-05'+ date '2001-09-28' + interval '1 hour'timestamp '2001-09-2801:00:00'+ date '2001-09-28' + time '03:00'timestamp '2001-09-2803:00:00'+ interval '1 day' + interval '1 hour' interval '1 day 01:00:00'+timestamp '2001-09-28 01:00' + interval '23hours'timestamp '2001-09-2900:00:00'+ time '01:00' + interval '3 hours' time '04:00:00'- - interval '23 hours' interval '-23:00:00'- date '2001-10-01' - date '2001-09-28' integer '3' (days)- date '2001-10-01' - integer '7' date '2001-09-24'- date '2001-09-28' - interval '1 hour'timestamp '2001-09-2723:00:00'- time '05:00' - time '03:00' interval '02:00:00'- time '05:00' - interval '2 hours' time '03:00:00'-timestamp '2001-09-28 23:00' - interval '23hours'timestamp '2001-09-2800:00:00'- interval '1 day' - interval '1 hour' interval '1 day -01:00:00'-timestamp '2001-09-29 03:00' - timestamp'2001-09-27 12:00'interval '1 day 15:00:00'* 900 * interval '1 second' interval '00:15:00'41. PostgreSQL – DATE/TIME Functions andOperators

PostgreSQL159* 21 * interval '1 day' interval '21 days'* double precision '3.5' * interval '1 hour' interval '03:30:00'/ interval '1 hour' / double precision '1.5' interval '00:40:00'Following is the list of all important Date and Time related functions available-Function DescriptionAGE() Subtract argumentsCURRENT DATE/TIME() Current date and timeDATE_PART() Get subfield (equivalent to extract)EXTRACT() Get subfieldISFINITE() Test for finite date, time and interval (not +/-infinity)JUSTIFY Adjust intervalAGE(timestamp, timestamp), AGE(timestamp)Function DescriptionAGE(timestamp,timestamp)When invoked with the TIMESTAMP form of the second argument,AGE() subtract arguments, producing a "symbolic" result thatuses years and months and is of type INTERVAL.AGE(timestamp)When invoked with only the TIMESTAMP as argument, AGE()subtracts from the current_date (at midnight).Example of the function AGE(timestamp, timestamp)-testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13');The above given PostgreSQL statement will produce the following result-age-------------------------43 years 9 mons 27 daysExample of the function AGE(timestamp)-testdb=# select age(timestamp '1957-06-13');

PostgreSQL160The above given PostgreSQL statement will produce the following result-age--------------------------55 years 10 mons 22 daysCURRENT DATE/TIME()PostgreSQL provides a number of functions that return values related to the current dateand time. Following are some functions-Function DescriptionCURRENT_DATE Delivers current date.CURRENT_TIME Delivers values with time zone.CURRENT_TIMESTAMP Delivers values with time zone.CURRENT_TIME(precision)Optionally takes a precision parameter, whichcauses the result to be rounded to that manyfractional digits in the seconds field.CURRENT_TIMESTAMP(precision)Optionally takes a precision parameter, whichcauses the result to be rounded to that manyfractional digits in the seconds field.LOCALTIME Delivers values without time zone.LOCALTIMESTAMP Delivers values without time zone.LOCALTIME(precision)Optionally takes a precision parameter, whichcauses the result to be rounded to that manyfractional digits in the seconds field.LOCALTIMESTAMP(precision)Optionally takes a precision parameter, whichcauses the result to be rounded to that manyfractional digits in the seconds field.Examples using the functions from the table above-testdb=# SELECT CURRENT_TIME;timetz--------------------08:01:34.656+05:30(1 row)testdb=# SELECT CURRENT_DATE;

PostgreSQL161date------------2013-05-05(1 row)testdb=# SELECT CURRENT_TIMESTAMP;now-------------------------------2013-05-05 08:01:45.375+05:30(1 row)testdb=# SELECT CURRENT_TIMESTAMP(2);timestamptz------------------------------2013-05-05 08:01:50.89+05:30(1 row)testdb=# SELECT LOCALTIMESTAMP;timestamp------------------------2013-05-05 08:01:55.75(1 row)PostgreSQL also provides functions that return the start time of the current statement, aswell as the actual current time at the instant the function is called. These functions are-Function Descriptiontransaction_timestamp()It is equivalent to CURRENT_TIMESTAMP, but is named toclearly reflect what it returns.statement_timestamp() It returns the start time of the current statement.clock_timestamp()It returns the actual current time, and therefore its valuechanges even within a single SQL command.timeofday()It returns the actual current time, but as a formatted textstring rather than a timestamp with time zone value.now()It is a traditional PostgreSQL equivalent totransaction_timestamp().

PostgreSQL162DATE_PART(text, timestamp), DATE_PART(text, interval),DATE_TRUNC(text, timestamp)Function DescriptionDATE_PART('field', source)These functions get the subfields. The field parameterneeds to be a string value, not a name.The valid field names are: century, day, decade, dow,doy, epoch, hour, isodow, isoyear, microseconds,millennium, milliseconds, minute, month, quarter,second, timezone, timezone_hour, timezone_minute,week, year.DATE_TRUNC('field', source)This function is conceptually similar tothe trunc function for numbers. source is a valueexpression of type timestamp or interval. field selectsto which precision to truncate the input value. Thereturn value is of type timestamp or interval.The valid values for field are : microseconds,milliseconds, second, minute, hour, day, week, month,quarter, year, decade, century, millenniumFollowing are examples of DATE_PART('field', source) functions-testdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');date_part-----------16(1 row)testdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');date_part-----------4(1 row)Following are examples for DATE_TRUNC('field', source) functions:testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');date_trunc---------------------2001-02-16 20:00:00(1 row)

PostgreSQL163testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');date_trunc---------------------2001-01-01 00:00:00(1 row)EXTRACT(field from timestamp), EXTRACT(field from interval)The EXTRACT(field FROM source) function retrieves subfields such as year or hour fromdate/time values. The source must be a value expression of type timestamp, time, orinterval. The field is an identifier or string that selects what field to extract from the sourcevalue. The EXTRACT function returns values of type double precision.The following are valid field names (similar to DATE_PART function field names):century,day, decade, dow, doy, epoch, hour, isodow, isoyear, microseconds, millennium,milliseconds, minute, month, quarter, second, timezone, timezone_hour,timezone_minute, week, year.Following are examples of EXTRACT('field', source) functions-testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');date_part-----------20(1 row)testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');date_part-----------16(1 row)ISFINITE(date), ISFINITE(timestamp), ISFINITE(interval)Function DescriptionISFINITE(date) Tests for finite date.ISFINITE(timestamp) Tests for finite time stamp.ISFINITE(interval) Tests for finite interval.

PostgreSQL164Following are the examples of the ISFINITE() functions-testdb=# SELECT isfinite(date '2001-02-16');isfinite----------t(1 row)testdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30');isfinite----------t(1 row)testdb=# SELECT isfinite(interval '4 hours');isfinite----------t(1 row)JUSTIFY_DAYS(interval), JUSTIFY_HOURS(interval),JUSTIFY_INTERVAL(interval)Function DescriptionJUSTIFY_DAYS(interval)Adjusts interval so 30-day time periods are representedas months. Return the interval typeJUSTIFY_HOURS(interval)Adjusts interval so 24-hour time periods arerepresented as days. Return the interval typeJUSTIFY_INTERVAL(interval)Adjusts interval using JUSTIFY_DAYS andJUSTIFY_HOURS, with additional sign adjustments.Return the interval type

PostgreSQL165Following are the examples of the ISFINITE() functions-testdb=# SELECT justify_days(interval '35 days');justify_days--------------1 mon 5 days(1 row)testdb=# SELECT justify_hours(interval '27 hours');justify_hours----------------1 day 03:00:00(1 row)testdb=# SELECT justify_interval(interval '1 mon -1 hour');justify_interval------------------29 days 23:00:00(1 row)

PostgreSQL166PostgreSQL functions, also known as Stored Procedures, allow you to carry outoperations that would normally take several queries and round trips in a single functionwithin the database. Functions allow database reuse as other applications can interactdirectly with your stored procedures instead of a middle-tier or duplicating code.Functions can be created in a language of your choice like SQL, PL/pgSQL, C, Python, etc.SyntaxThe basic syntax to create a function is as follows-CREATE [OR REPLACE] FUNCTION function_name (arguments)RETURNS return_datatype AS $variable_name$DECLAREdeclaration;[...]BEGIN< function_body >[...]RETURN { variable_name | value }END; LANGUAGE plpgsql;Where, function-name specifies the name of the function. [OR REPLACE] option allows modifying an existing function. The function must contain a return statement. RETURN clause specifies that data type you are going to return from the function.The return_datatype can be a base, composite, or domain type, or can referencethe type of a table column. function-body contains the executable part. The AS keyword is used for creating a standalone function. plpgsql is the name of the language that the function is implemented in. Here, weuse this option for PostgreSQL, it Can be SQL, C, internal, or the name of a user-defined procedural language. For backward compatibility, the name can beenclosed by single quotes.42. PostgreSQL – Functions

PostgreSQL167ExampleThe following example illustrates creating and calling a standalone function. This functionreturns the total number of records in the COMPANY table. We will use the COMPANY table,which has the following records-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Function totalRecords() is as follows-CREATE OR REPLACE FUNCTION totalRecords ()RETURNS integer AS $total$declaretotal integer;BEGINSELECT count(*) into total FROM COMPANY;RETURN total;END;$total$ LANGUAGE plpgsql;When the above query is executed, the result would be-testdb# CREATE FUNCTIONNow, let us execute a call to this function and check the records in the COMPANY table.testdb=# select totalRecords();When the above query is executed, the result would be-totalrecords--------------7(1 row)

PostgreSQL168PostgreSQL built-in functions, also called as Aggregate functions, are used for performingprocessing on string or numeric data.Following is the list of all general-purpose PostgreSQL built-in functions- PostgreSQL COUNT Function - The PostgreSQL COUNT aggregate function is usedto count the number of rows in a database table. PostgreSQL MAX Function - The PostgreSQL MAX aggregate function allows us toselect the highest (maximum) value for a certain column. PostgreSQL MIN Function - The PostgreSQL MIN aggregate function allows us toselect the lowest (minimum) value for a certain column. PostgreSQL AVG Function - The PostgreSQL AVG aggregate function selects theaverage value for certain table column. PostgreSQL SUM Function - The PostgreSQL SUM aggregate function allowsselecting the total for a numeric column. PostgreSQL ARRAY Functions - The PostgreSQL ARRAY aggregate function putsinput values, including nulls, concatenated into an array. PostgreSQL Numeric Functions - Complete list of PostgreSQL functions required tomanipulate numbers in SQL. PostgreSQL String Functions - Complete list of PostgreSQL functions required tomanipulate strings in PostgreSQL.PostgreSQL–COUNTFunctionPostgreSQL COUNT function is the simplest function and very useful in counting thenumber of records, which are expected to be returned by a SELECT statement.To understand the COUNT function, consider the table COMPANY having records asfollows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 1000043. PostgreSQL – Useful Functions

PostgreSQL169(7 rows)Now, based on the above table, suppose you want to count the total number of rows inthis table, then you can do it as follows-testdb=# SELECT COUNT(*) FROM COMPANY ;The above given PostgreSQL statement will produce the following result-count-------7(1 row)Similarly, you want to count the number of records for Paul then it can be done as follows-testdb=# SELECT COUNT(*) FROM COMPANY WHERE name='Paul';count-------1(1 row)PostgreSQL–MAXFunctionPostgreSQL MAX function is used to find out the record with maximum value among arecord set.To understand MAX function, consider the table COMPANY having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Now, based on the above table, suppose you want to fetch maximum value of SALARY,then you can do so by simply using the following command-testdb=# SELECT MAX(salary) FROM COMPANY;

PostgreSQL170The above given PostgreSQL statement will produce the following result-max-------85000(1 row)You can find all the records with maximum value for each name using the GROUPBY clause as follows-testdb=# SELECT id, name, MAX(salary) FROM COMPANY GROUP BY id, name;The above given PostgreSQL statement will produce the following result-id | name | max----+-------+-------4 | Mark | 650007 | James | 100006 | Kim | 450003 | Teddy | 200002 | Allen | 150005 | David | 850001 | Paul | 20000You can use the MIN Function along with the MAX function to find out minimum value aswell. Try out the following example-testdb=# SELECT MIN(salary), MAX(salary) max FROM company;The above given PostgreSQL statement will produce the following result-min | max-------+-------10000 | 85000(1 row)

PostgreSQL171PostgreSQL–MINFunctionPostgreSQL MIN function is used to find out the record with minimum value among arecord set.To understand the MIN function, consider the table COMPANY having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Now, based on the above table, suppose you want to fetch the minimum value of salary,then you can do so by simply using the following command-testdb=# SELECT MIN(salary) FROM company;The above given PostgreSQL statement will produce the following result-min-------10000(1 row)You can find all the records with the minimum value for each name using the GROUPBY clause as follows-testdb=# SELECT id, name, MIN(salary) FROM company GROUP BY id, name;The above given PostgreSQL statement will produce the following result-id | name | min----+-------+-------4 | Mark | 650007 | James | 100006 | Kim | 450003 | Teddy | 20000

PostgreSQL1722 | Allen | 150005 | David | 850001 | Paul | 20000(7 rows)You can use the MIN Function along with the MAX function to find out the minimum valueas well. Try out the following example-testdb=# SELECT MIN(salary), MAX(salary) max FROM company;The above given PostgreSQL statement will produce the following result-min | max-------+-------10000 | 85000(1 row)PostgreSQL–AVGFunctionPostgreSQL AVG function is used to find out the average of a field in various records.To understand the AVG function, consider the table COMPANY having records as follows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 150003 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Now, based on the above table, suppose you want to calculate the average of all theSALARY, then you can do so by using the following command-testdb=# SELECT AVG(SALARY) FROM COMPANY;

PostgreSQL174Now, based on the above table, suppose you want to calculate the total of all the salary,then you can do so by using the following command-testdb# SELECT SUM(salary) FROM company;The above given PostgreSQL statement will produce the following result-sum--------260000(1 row)You can take the sum of various records set using the GROUP BY clause. The followingexample will sum up all the records related to a single person and you will have salary foreach person.testdb# SELECT name, SUM(salary) FROM company GROUP BY name;The above given PostgreSQL statement will produce the following result-name | sum-------+-------Teddy | 20000Paul | 20000Mark | 65000David | 85000Allen | 15000Kim | 45000James | 10000(7 rows)PostgreSQL–ArrayFunctionPostgreSQL ARRAY_AGG function is used to concatenate the input values including nullinto an array.To understand the ARRAY_AGG function, consider the table COMPANY having records asfollows-testdb# select * from COMPANY;id | name | age | address | salary----+-------+-----+-----------+--------1 | Paul | 32 | California| 200002 | Allen | 25 | Texas | 15000

PostgreSQL1753 | Teddy | 23 | Norway | 200004 | Mark | 25 | Rich-Mond | 650005 | David | 27 | Texas | 850006 | Kim | 22 | South-Hall| 450007 | James | 24 | Houston | 10000(7 rows)Now, based on the above table, suppose you want to use the ARRAY_AGG, you can do soby using the following command-testdb=# SELECT ARRAY_AGG(SALARY) FROM COMPANY;The above given PostgreSQL statement will produce the following result-array_agg---------------------------------------------{20000,15000,20000,65000,85000,45000,10000}PostgreSQL–NumericFunctionPostgreSQL numeric functions are used primarily for numeric manipulation and/ormathematical calculations. The following table details the numeric functions-Name DescriptionABS() Returns the absolute value of numeric expression.ACOS()Returns the arccosine of numeric expression. Returns NULL ifthe value is not in the range -1 to 1.ASIN()Returns the arcsine of numeric expression. Returns NULL ifvalue is not in the range -1 to 1ATAN() Returns the arctangent of numeric expression.ATAN2() Returns the arctangent of the two variables passed to it.CEIL()Returns the smallest integer value that is not less thanpassed numeric expressionCEILING()Returns the smallest integer value that is not less thanpassed numeric expressionCOS()Returns the cosine of passed numeric expression. Thenumeric expression should be expressed in radians.COT() Returns the cotangent of passed numeric expression.

PostgreSQL176DEGREES()Returns numeric expression converted from radians todegrees.EXP()Returns the base of the natural logarithm (e) raised to thepower of passed numeric expression.FLOOR()Returns the largest integer value that is not greater thanpassed numeric expression.GREATEST() Returns the largest value of the input expressions.LEAST() Returns the minimum-valued input when given two or more.LOG()Returns the natural logarithm of the passed numericexpression.MOD()Returns the remainder of one expression by diving by anotherexpression.PI() Returns the value of piPOW()Returns the value of one expression raised to the power ofanother expressionPOWER()Returns the value of one expression raised to the power ofanother expressionRADIANS()Returns the value of passed expression converted fromdegrees to radians.ROUND()Returns numeric expression rounded to an integer. Can beused to round an expression to a number of decimal pointsSIN() Returns the sine of numeric expression given in radians.SQRT() Returns the non-negative square root of numeric expression.TAN()Returns the tangent of numeric expression expressed inradians.ABS(X)The ABS() function returns the absolute value of X. Consider the following example-testdb=# SELECT ABS(2);+---------------------------------------------------------+| ABS(2) |+---------------------------------------------------------+| 2 |+---------------------------------------------------------+1 row in set (0.00 sec)

PostgreSQL177testdb=# SELECT ABS(-2);+---------------------------------------------------------+| ABS(2) |+---------------------------------------------------------+| 2 |+---------------------------------------------------------+1 row in set (0.00 sec)ACOS(X)This function returns the arccosine of X. The value of X must range between -1 and 1 orNULL will be returned. Consider the following example-testdb=# SELECT ACOS(1);+---------------------------------------------------------+| ACOS(1) |+---------------------------------------------------------+| 0.000000 |+---------------------------------------------------------+1 row in set (0.00 sec)ASIN(X)The ASIN() function returns the arcsine of X. The value of X must be in the range of -1 to1 or NULL is returned.testdb=# SELECT ASIN(1);+---------------------------------------------------------+| ASIN(1) |+---------------------------------------------------------+| 1.5707963267949 |+---------------------------------------------------------+1 row in set (0.00 sec)ATAN(X)

PostgreSQL178This function returns the arctangent of X.testdb=# SELECT ATAN(1);+---------------------------------------------------------+| ATAN(1) |+---------------------------------------------------------+| 0.78539816339745 |+---------------------------------------------------------+1 row in set (0.00 sec)ATAN2(Y,X)This function returns the arctangent of the two arguments: X and Y. It is similar to thearctangent of Y/X, except that the signs of both are used to find the quadrant of the result.testdb=# SELECT ATAN2(3,6);+---------------------------------------------------------+| ATAN2(3,6) |+---------------------------------------------------------+| 0.46364760900081 |+---------------------------------------------------------+1 row in set (0.00 sec)CEIL(X) / CEILING(X)These functions return the smallest integer value that is not smaller than X. Consider thefollowing example-testdb=# SELECT CEILING(3.46);+---------------------------------------------------------+| CEILING(3.46) |+---------------------------------------------------------+| 4 |+---------------------------------------------------------+1 row in set (0.00 sec)testdb=# SELECT CEIL(-6.43);+---------------------------------------------------------+| CEIL(-6.43) |+---------------------------------------------------------+| -6 |

PostgreSQL179+---------------------------------------------------------+1 row in set (0.00 sec)COS(X)This function returns the cosine of X. The value of X is given in radians.testdb=#SELECT COS(90);+---------------------------------------------------------+| COS(90) |+---------------------------------------------------------+| -0.44807361612917 |+---------------------------------------------------------+1 row in set (0.00 sec)COT(X)This function returns the cotangent of X. Consider the following example-testdb=#SELECT COT(1);+---------------------------------------------------------+| COT(1) |+---------------------------------------------------------+| 0.64209261593433 |+---------------------------------------------------------+1 row in set (0.00 sec)DEGREES(X)This function returns the value of X converted from radians to degrees.testdb=#SELECT DEGREES(PI());+---------------------------------------------------------+| DEGREES(PI()) |+---------------------------------------------------------+| 180.000000 |+---------------------------------------------------------+1 row in set (0.00 sec)EXP(X)

PostgreSQL180This function returns the value of e (the base of the natural logarithm) raised to the powerof X.testdb=#SELECT EXP(3);+---------------------------------------------------------+| EXP(3) |+---------------------------------------------------------+| 20.085537 |+---------------------------------------------------------+1 row in set (0.00 sec)FLOOR(X)This function returns the largest integer value that is not greater than X.testdb=#SELECT FLOOR(7.55);+---------------------------------------------------------+| FLOOR(7.55) |+---------------------------------------------------------+| 7 |+---------------------------------------------------------+1 row in set (0.00 sec)GREATEST(n1,n2,n3,..........)The GREATEST() function returns the greatest value in the set of input parameters (n1,n2, n3, and so on). The following example uses the GREATEST() function to return thelargest number from a set of numeric values-testdb=#SELECT GREATEST(3,5,1,8,33,99,34,55,67,43);+---------------------------------------------------------+| GREATEST(3,5,1,8,33,99,34,55,67,43) |+---------------------------------------------------------+| 99 |+---------------------------------------------------------+1 row in set (0.00 sec)LEAST(N1,N2,N3,N4,......)

PostgreSQL181The LEAST() function is the opposite of the GREATEST() function. Its purpose is to returnthe least-valued item from the value list (N1, N2, N3, and so on). The following exampleshows the proper usage and output for the LEAST() function-testdb=#SELECT LEAST(3,5,1,8,33,99,34,55,67,43);+---------------------------------------------------------+| LEAST(3,5,1,8,33,99,34,55,67,43) |+---------------------------------------------------------+| 1 |+---------------------------------------------------------+1 row in set (0.00 sec)LOG(X) / LOG(B,X)The single argument version of the function will return the natural logarithm of X. If it iscalled with two arguments, it returns the logarithm of X for an arbitrary base B. Considerthe following example-testdb=#SELECT LOG(45);+---------------------------------------------------------+| LOG(45) |+---------------------------------------------------------+| 1.65321251377534 |+---------------------------------------------------------+1 row in set (0.00 sec)testdb=#SELECT LOG(2,65536);+---------------------------------------------------------+| LOG(2,65536) |+---------------------------------------------------------+| 16.000000 |+---------------------------------------------------------+1 row in set (0.00 sec)MOD(N,M)This function returns the remainder of N divided by M. Consider the following example-

PostgreSQL182testdb=#SELECT MOD(29,3);+---------------------------------------------------------+| MOD(29,3) |+---------------------------------------------------------+| 2 |+---------------------------------------------------------+1 row in set (0.00 sec)PI()This function simply returns the value of pi. SQL internally stores the full double-precisionvalue of pi.testdb=#SELECT PI();+---------------------------------------------------------+| PI() |+---------------------------------------------------------+| 3.141593 |+---------------------------------------------------------+1 row in set (0.00 sec)POW(X,Y) / POWER(X,Y)These two functions return the value of X raised to the power of Y.testdb=# SELECT POWER(3,3);+---------------------------------------------------------+| POWER(3,3) |+---------------------------------------------------------+| 27 |+---------------------------------------------------------+1 row in set (0.00 sec)RADIANS(X)This function returns the value of X, converted from degrees to radians.

PostgreSQL183testdb=#SELECT RADIANS(90);+---------------------------------------------------------+| RADIANS(90) |+---------------------------------------------------------+|1.570796 |+---------------------------------------------------------+1 row in set (0.00 sec)ROUND(X) / ROUND(X,D)This function returns X rounded to the nearest integer. If a second argument, D, issupplied, then the function returns X rounded to D decimal places. D must be positive orall digits to the right of the decimal point will be removed. Consider the following example-testdb=#SELECT ROUND(5.693893);+---------------------------------------------------------+| ROUND(5.693893) |+---------------------------------------------------------+| 6 |+---------------------------------------------------------+1 row in set (0.00 sec)testdb=#SELECT ROUND(5.693893,2);+---------------------------------------------------------+| ROUND(5.693893,2) |+---------------------------------------------------------+| 5.69 |+---------------------------------------------------------+1 row in set (0.00 sec)SIGN(X)This function returns the sign of X (negative, zero, or positive) as -1, 0, or 1.

PostgreSQL184testdb=#SELECT SIGN(-4.65);+---------------------------------------------------------+| SIGN(-4.65) |+---------------------------------------------------------+| -1 |+---------------------------------------------------------+1 row in set (0.00 sec)testdb=#SELECT SIGN(0);+---------------------------------------------------------+| SIGN(0) |+---------------------------------------------------------+| 0 |+---------------------------------------------------------+1 row in set (0.00 sec)testdb=#SELECT SIGN(4.65);+---------------------------------------------------------+| SIGN(4.65) |+---------------------------------------------------------+| 1 |+---------------------------------------------------------+1 row in set (0.00 sec)SIN(X)This function returns the sine of X. Consider the following example-testdb=#SELECT SIN(90);+---------------------------------------------------------+| SIN(90) |+---------------------------------------------------------+| 0.893997 |+---------------------------------------------------------+1 row in set (0.00 sec)SQRT(X)This function returns the non-negative square root of X. Consider the following example-

PostgreSQL185testdb=#SELECT SQRT(49);+---------------------------------------------------------+| SQRT(49) |+---------------------------------------------------------+| 7 |+---------------------------------------------------------+1 row in set (0.00 sec)TAN(X)This function returns the tangent of the argument X, which is expressed in radians-testdb=#SELECT TAN(45);+---------------------------------------------------------+| TAN(45) |+---------------------------------------------------------+| 1.619775 |+---------------------------------------------------------+1 row in set (0.00 sec)PostgreSQL–STRINGFunctionPostgreSQL string functions are used primarily for string manipulation. The following tabledetails the important string functions-Name DescriptionASCII() Returns numeric value of left-most characterBIT_LENGTH() Returns length of argument in bitsCHAR_LENGTH() Returns number of characters in argumentCHARACTER_LENGTH() A synonym for CHAR_LENGTH()CONCAT_WS() Returns concatenate with separatorCONCAT() Returns concatenated stringLCASE() Synonym for LOWER()LEFT() Returns the leftmost number of characters as specifiedLENGTH() Returns the length of a string in bytes

PostgreSQL186LOWER() Returns the argument in lowercaseLPAD() Returns the string argument, left-padded with the specifiedstringLTRIM() Removes leading spacesMID() Returns a substring starting from the specified positionPOSITION() A synonym for LOCATE()QUOTE() Escapes the argument for use in an SQL statementREGEXP Pattern matching using regular expressionsREPEAT() Repeats a string the specified number of timesREPLACE() Replaces occurrences of a specified stringREVERSE() Reverse the characters in a stringRIGHT() Returns the specified rightmost number of charactersRPAD() Appends string the specified number of timesRTRIM() Removes trailing spacesSUBSTRING(),SUBSTR()Returns the substring as specifiedTRIM() Removes leading and trailing spacesUCASE() Synonym for UPPER()UPPER() Converts to uppercaseASCII(str)Returns the numeric value of the leftmost character of the string str. Returns 0 if str is anempty string. Returns NULL if str is NULL. ASCII() works for characters with numeric valuesfrom 0 to 255.testdb=# SELECT ASCII('2');+---------------------------------------------------------+| ASCII('2') |

PostgreSQL187+---------------------------------------------------------+| 50 |+---------------------------------------------------------+1 row in set (0.00 sec)testdb=# SELECT ASCII('dx');+---------------------------------------------------------+| ASCII('dx') |+---------------------------------------------------------+| 100 |+---------------------------------------------------------+1 row in set (0.00 sec)BIT_LENGTH(str)Returns the length of the string str in bits.testdb=# SELECT BIT_LENGTH('text');+---------------------------------------------------------+| BIT_LENGTH('text') |+---------------------------------------------------------+| 32 |+---------------------------------------------------------+1 row in set (0.00 sec)CHAR_LENGTH(str)Returns the length of the string str, measured in characters. A multi-byte character countsas a single character. This means that for a string containing five two-byte characters,LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

PostgreSQL188testdb=# SELECT CHAR_LENGTH('text');+---------------------------------------------------------+| CHAR_LENGTH('text') |+---------------------------------------------------------+| 4 |+---------------------------------------------------------+1 row in set (0.00 sec)CHARACTER_LENGTH(str)CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().CONCAT(str1,str2,...)Returns the string that results from concatenating the arguments. It may have one ormore arguments. If all arguments are non-binary strings, the result is a non-binary string.If the arguments include any binary strings, the result is a binary string. A numericargument is converted to its equivalent binary string form; if you want to avoid that, youcan use an explicit type cast, as in this example.testdb=# SELECT CONCAT('My', 'S', 'QL');+---------------------------------------------------------+| CONCAT('My', 'S', 'QL') |+---------------------------------------------------------+| MySQL |+---------------------------------------------------------+1 row in set (0.00 sec)CONCAT_WS(separator,str1,str2,...)CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT().The first argument is the separator for the rest of the arguments. The separator is addedbetween the strings to be concatenated. The separator can be a string, as can the rest ofthe arguments. If the separator is NULL, the result is NULL.

PostgreSQL189testdb=# SELECT CONCAT_WS(',','First name','Last Name' );+---------------------------------------------------------+| CONCAT_WS(',','First name','Last Name' ) |+---------------------------------------------------------+| First name, Last Name |+---------------------------------------------------------+1 row in set (0.00 sec)LCASE(str)LCASE() is a synonym for LOWER().LEFT(str,len)Returns the leftmost len characters from the string str, or NULL if any argument is NULL.testdb=# SELECT LEFT('foobarbar', 5);+---------------------------------------------------------+| LEFT('foobarbar', 5) |+---------------------------------------------------------+| fooba |+---------------------------------------------------------+1 row in set (0.00 sec)LENGTH(str)Returns the length of the string str, measured in bytes. A multi-byte character counts asmultiple bytes. This means that for a string containing five two-byte characters, LENGTH()returns 10, whereas CHAR_LENGTH() returns 5.testdb=# SELECT LENGTH('text');+---------------------------------------------------------+| LENGTH('text') |+---------------------------------------------------------+| 4 |+---------------------------------------------------------+1 row in set (0.00 sec)LOWER(str)Returns the string str with all characters changed to lowercase according to the currentcharacter set mapping.testdb=# SELECT LOWER('QUADRATICALLY');

PostgreSQL190+---------------------------------------------------------+| LOWER('QUADRATICALLY') |+---------------------------------------------------------+| quadratically |+---------------------------------------------------------+1 row in set (0.00 sec)LPAD(str,len,padstr)Returns the string str, left-padded with the string padstr to a length of len characters. Ifstr is longer than len, the return value is shortened to len characters.testdb=# SELECT LPAD('hi',4,'??');+---------------------------------------------------------+| LPAD('hi',4,'??') |+---------------------------------------------------------+| ??hi |+---------------------------------------------------------+1 row in set (0.00 sec)LTRIM(str)Returns the string str with leading space characters removed.testdb=# SELECT LTRIM(' barbar');+---------------------------------------------------------+| LTRIM(' barbar') |+---------------------------------------------------------+| barbar |+---------------------------------------------------------+1 row in set (0.00 sec)MID(str,pos,len)MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).POSITION(substr IN str)

PostgreSQL191POSITION(substr IN str) is a synonym for LOCATE(substr,str).QUOTE_IDENT(string text), QUOTE_LITERAL(string text),QUOTE_LITERAL(value any element), QUOTE_NULLABLE(value anyelement)All these functions return the given string suitably quoted to be used as an identifier in anSQL statement string. In the function QUOTE_IDENT, Quotes are added only if necessary.In the function QUOTE_LITERAL, embedded single-quotes and backslashes are properlydoubled. If a value is passed, coerce the given value to text and then quote it as a literal.The function QUOTE_NULLABLE, coerces the given value to text and then quotes it as aliteral; or, if the argument is null, returns NULL.Following are the examples for all these functions-testdb=# SELECT QUOTE_IDENT('Foo bar');quote_ident-------------"Foo bar"(1 row)testdb=# SELECT QUOTE_LITERAL(E'O'Reilly');quote_literal---------------'O''Reilly'(1 row)testdb=# SELECT QUOTE_LITERAL(42.5);quote_literal---------------'42.5'(1 row)testdb=# SELECT QUOTE_NULLABLE(42.5);quote_nullable----------------'42.5'

PostgreSQL192(1 row)expr REGEXP patternREGEXP_MATCHES(string text, pattern text [, flags text]) function performs a patternmatch of expr against pattern. Returns 1 if expr matches pat; otherwise it returns 0. Ifeither expr or pat is NULL, the result is NULL. REGEXP_MATCHES is not case sensitive,except when used with binary strings.REGEXP_REPLACE(string text, pattern text, replacement text [, flags text]) functionreplaces substring(s) matching a POSIX regular expression.REGEXP_SPLIT_TO_ARRAY(string text, pattern text [, flags text ]), Split string using aPOSIX regular expression as the delimiter.REGEXP_SPLIT_TO_TABLE(string text, pattern text [, flags text]), splits string using aPOSIX regular expression as the delimiter.Following are the examples for all these functions-testdb=# SELECT REGEXP_MATCHES('ABCDEF' ,'A%C%%');regexp_matches----------------(0 rows)testdb=# SELECT REGEXP_REPLACE('Thomas', '.[mN]a.', 'M');regexp_replace----------------ThM(1 row)testdb=# SELECT REGEXP_SPLIT_TO_ARRAY('hello world', E's+');regexp_split_to_array-----------------------{hello,world}(1 row)testdb=# SELECT REGEXP_SPLIT_TO_TABLE('hello world', E's+');regexp_split_to_table-----------------------helloworld

PostgreSQL193(2 rows)REPEAT(str,count)Returns a string consisting of the string str repeated count times. If count is less than 1,returns an empty string. Returns NULL if str or count are NULL.testdb=# SELECT REPEAT('SQL', 3);repeat-----------SQLSQLSQL(1 row)REPLACE(str,from_str,to_str)Returns the string str with all occurrences of the string from_str replaced by the stringto_str. REPLACE() performs a case-sensitive match when searching for from_str.testdb=# SELECT REPLACE('www.mysql.com', 'w', 'Ww');replace------------------WwWwWw.mysql.com(1 row)REVERSE(str)Returns the string str with the order of the characters reversed.testdb=# SELECT REVERSE('abcd');reverse---------dcba(1 row)RIGHT(str,len)Returns the rightmost len characters from the string str, or NULL if any argument is NULL.testdb=# SELECT RIGHT('foobarbar', 4);right-------

PostgreSQL194rbar(1 row)RPAD(str,len,padstr)Returns the string str, right-padded with the string padstr to a length of len characters. Ifstr is longer than len, the return value is shortened to len characters.testdb=# SELECT RPAD('hi',5,'?');rpad-------hi???(1 row)RTRIM(str)Returns the string str with trailing space characters removed.testdb=# SELECT RTRIM('barbar ');rtrim--------barbar(1 row)SUBSTRING(str,pos)SUBSTRING(str FROM pos)SUBSTRING(str,pos,len)SUBSTRING(str FROM pos FOR len)The forms without a len argument return a substring from string str starting at positionpos. The forms with a len argument return a substring len characters long from string str,starting at position pos. The forms that use FROM are standard SQL syntax. It is alsopossible to use a negative value for pos. In this case, the beginning of the substring is poscharacters from the end of the string, rather than the beginning. A negative value may beused for pos in any of the forms of this function.testdb=# SELECT SUBSTRING('Quadratically',5);substring-----------ratically

PostgreSQL195(1 row)testdb=# SELECT SUBSTRING('foobarbar' FROM 4);substring-----------barbar(1 row)testdb=# SELECT SUBSTRING('Quadratically',5,6);substring-----------ratica(1 row)TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)TRIM([remstr FROM] str)Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiersBOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if notspecified, spaces are removed.testdb=# SELECT TRIM(' bar ');btrim-------bar(1 row)testdb=# SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');ltrim--------barxxx(1 row)testdb=# SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');btrim-------bar(1 row)

PostgreSQL196testdb=# SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');rtrim-------bar(1 row)UCASE(str)UCASE() is a synonym for UPPER().UPPER(str)Returns the string str with all characters changed to uppercase according to the currentcharacter set mapping.testdb=# SELECT UPPER('manisha');upper---------MANISHA(1 row)

PostgreSQL197PostgreSQL Interfaces

PostgreSQL198This tutorial is going to use libpqxx library, which is the official C++ client API forPostgreSQL. The source code for libpqxx is available under the BSD license, so you're freeto download it, pass it on to others, change it, sell it, include it in your own code, andshare your changes with anyone you choose.InstallationThe the latest version of libpqxx is available to be downloaded from the link DownloadLibpqxx. So download the latest version and follow the following steps-wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gztar xvfz libpqxx-4.0.tar.gzcd libpqxx-4.0./configuremakemake installBefore you start using C/C++ PostgreSQL interface, find the pg_hba.conf file in yourPostgreSQL installation directory and add the following line-# IPv4 local connections:host all all 127.0.0.1/32 md5You can start/restart postgres server in case it is not running using the followingcommand-[root@host]# service postgresql restartStopping postgresql service: [ OK ]Starting postgresql service: [ OK ]44. PostgreSQL – C/C++ Interface

PostgreSQL199C/C++InterfaceAPIsFollowing are important interface routines which can sufice your requirement to work withPostgreSQL database from your C/C++ program. If you are looking for a moresophisticated application then you can look into the libpqxx official documentation, or youcan use commercially available APIs.S. No. API & Description1pqxx::connection C( const std::string & dbstring )This is a typedef which will be used to connect to the database. Here, dbstringprovides required parameters to connect to the datbase, forexample dbname=testdb user=postgres password=pass123hostaddr=127.0.0.1 port=5432.If connection is setup successfully then it creates C with connection objectwhich provides various useful function public function.2C.is_open()The method is_open() is a public method of connection object and returnsboolean value. If connection is active, then this method returns true otherwiseit returns false.3C.disconnect()This method is used to disconnect an opened database connection.4pqxx::work W( C )This is a typedef which will be used to create a transactional object usingconnection C, which ultimately will be used to execute SQL statements intransactional mode.If transaction object gets created successfully, then it is assigned to variableW which will be used to access public methods related to transactional object.5W.exec(const std::string & sql)This public method from transactional object will be used to execute SQLstatement.6W.commit()This public method from transactional object will be used to commit thetransaction.7W.abort()This public method from transactional object will be used to rollback thetransaction.8pqxx::nontransaction N( C )This is a typedef which will be used to create a non-transactional object usingconnection C, which ultimately will be used to execute SQL statements in non-transactional mode.If transaction object gets created successfully, then it is assigned to variable Nwhich will be used to access public methods related to non-transactionalobject.

PostgreSQL2009N.exec(const std::string & sql)This public method from non-transactional object will be used to execute SQLstatement and returns a result object which is actually an interator holding allthe returned records.ConnectingToDatabaseThe following C code segment shows how to connect to an existing database running onlocal machine at port 5432. Here, I used backslash for line continuation.#include <iostream>#include <pqxx/pqxx>using namespace std;using namespace pqxx;int main(int argc, char* argv[]){try{connection C("dbname=testdb user=postgres password=cohondob hostaddr=127.0.0.1 port=5432");if (C.is_open()) {cout << "Opened database successfully: " << C.dbname() << endl;} else {cout << "Can't open database" << endl;return 1;}C.disconnect ();}catch (const std::exception &e){cerr << e.what() << std::endl;return 1;}}Now, let us compile and run the above program to connect to our database testdb, whichis already available in your schema and can be accessed using user postgres andpassword pass123.You can use the user ID and password based on your database setting. Remember to keepthe -lpqxx and -lpq in the given order! Otherwise, the linker will complain bitterly aboutthe missing functions with names starting with "PQ."

PostgreSQL201$g++ test.cpp -lpqxx -lpq$./a.outOpened database successfully: testdbCreateaTableThe following C code segment will be used to create a table in previously created database-#include <iostream>#include <pqxx/pqxx>using namespace std;using namespace pqxx;int main(int argc, char* argv[]){char * sql;try{connection C("dbname=testdb user=postgres password=cohondob hostaddr=127.0.0.1 port=5432");if (C.is_open()) {cout << "Opened database successfully: " << C.dbname() << endl;} else {cout << "Can't open database" << endl;return 1;}/* Create SQL statement */sql = "CREATE TABLE COMPANY(" "ID INT PRIMARY KEY NOT NULL," "NAME TEXT NOT NULL," "AGE INT NOT NULL," "ADDRESS CHAR(50)," "SALARY REAL );";/* Create a transactional object. */work W(C);

PostgreSQL202/* Execute SQL query */W.exec( sql );W.commit();cout << "Table created successfully" << endl;C.disconnect ();}catch (const std::exception &e){cerr << e.what() << std::endl;return 1;}return 0;}When the above given program is compiled and executed, it will create COMPANY table inyour testdb database and will display the following statements-Opened database successfully: testdbTable created successfullyINSERTOperationThe following C code segment shows how we can create records in our COMPANY tablecreated in above example-#include <iostream>#include <pqxx/pqxx>using namespace std;using namespace pqxx;int main(int argc, char* argv[]){char * sql;try{connection C("dbname=testdb user=postgres password=cohondob hostaddr=127.0.0.1 port=5432");if (C.is_open()) {cout << "Opened database successfully: " << C.dbname() << endl;} else {cout << "Can't open database" << endl;

PostgreSQL203return 1;}/* Create SQL statement */sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); " "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";/* Create a transactional object. */work W(C);/* Execute SQL query */W.exec( sql );W.commit();cout << "Records created successfully" << endl;C.disconnect ();}catch (const std::exception &e){cerr << e.what() << std::endl;return 1;}return 0;}When the above given program is compiled and executed, it will create given records inCOMPANY table and will display the following two lines-Opened database successfully: testdbRecords created successfully

PostgreSQL204SELECTOperationThe following C code segment shows how we can fetch and display records from ourCOMPANY table created in above example-#include <iostream>#include <pqxx/pqxx>using namespace std;using namespace pqxx;int main(int argc, char* argv[]){char * sql;try{connection C("dbname=testdb user=postgres password=cohondob hostaddr=127.0.0.1 port=5432");if (C.is_open()) {cout << "Opened database successfully: " << C.dbname() << endl;} else {cout << "Can't open database" << endl;return 1;}/* Create SQL statement */sql = "SELECT * from COMPANY";/* Create a non-transactional object. */nontransaction N(C);/* Execute SQL query */result R( N.exec( sql ));/* List down all the records */for (result::const_iterator c = R.begin(); c != R.end(); ++c) {cout << "ID = " << c[0].as<int>() << endl;cout << "Name = " << c[1].as<string>() << endl;cout << "Age = " << c[2].as<int>() << endl;cout << "Address = " << c[3].as<string>() << endl;

PostgreSQL205cout << "Salary = " << c[4].as<float>() << endl;}cout << "Operation done successfully" << endl;C.disconnect ();}catch (const std::exception &e){cerr << e.what() << std::endl;return 1;}return 0;}When the above given program is compiled and executed, it will produce the followingresult-Opened database successfully: testdbID = 1Name = PaulAge = 32Address = CaliforniaSalary = 20000ID = 2Name = AllenAge = 25Address = TexasSalary = 15000ID = 3Name = TeddyAge = 23Address = NorwaySalary = 20000ID = 4Name = MarkAge = 25Address = Rich-MondSalary = 65000Operation done successfully

PostgreSQL206UPDATEOperationThe following C code segment shows how we can use the UPDATE statement to updateany record and then fetch and display updated records from our COMPANY table-#include <iostream>#include <pqxx/pqxx>using namespace std;using namespace pqxx;int main(int argc, char* argv[]){char * sql;try{connection C("dbname=testdb user=postgres password=cohondob hostaddr=127.0.0.1 port=5432");if (C.is_open()) {cout << "Opened database successfully: " << C.dbname() << endl;} else {cout << "Can't open database" << endl;return 1;}/* Create a transactional object. */work W(C);/* Create SQL UPDATE statement */sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1";/* Execute SQL query */W.exec( sql );W.commit();cout << "Records updated successfully" << endl;/* Create SQL SELECT statement */sql = "SELECT * from COMPANY";/* Create a non-transactional object. */nontransaction N(C);

PostgreSQL207/* Execute SQL query */result R( N.exec( sql ));/* List down all the records */for (result::const_iterator c = R.begin(); c != R.end(); ++c) {cout << "ID = " << c[0].as<int>() << endl;cout << "Name = " << c[1].as<string>() << endl;cout << "Age = " << c[2].as<int>() << endl;cout << "Address = " << c[3].as<string>() << endl;cout << "Salary = " << c[4].as<float>() << endl;}cout << "Operation done successfully" << endl;C.disconnect ();}catch (const std::exception &e){cerr << e.what() << std::endl;return 1;}return 0;}When the above given program is compiled and executed, it will produce the followingresult-Opened database successfully: testdbRecords updated successfullyID = 2Name = AllenAge = 25Address = TexasSalary = 15000ID = 3Name = TeddyAge = 23Address = NorwaySalary = 20000ID = 4Name = Mark

PostgreSQL208Age = 25Address = Rich-MondSalary = 65000ID = 1Name = PaulAge = 32Address = CaliforniaSalary = 25000Operation done successfullyDELETEOperationThe following C code segment shows how we can use the DELETE statement to delete anyrecord and then fetch and display remaining records from our COMPANY table-#include <iostream>#include <pqxx/pqxx>using namespace std;using namespace pqxx;int main(int argc, char* argv[]){char * sql;try{connection C("dbname=testdb user=postgres password=cohondob hostaddr=127.0.0.1 port=5432");if (C.is_open()) {cout << "Opened database successfully: " << C.dbname() << endl;} else {cout << "Can't open database" << endl;return 1;}/* Create a transactional object. */work W(C);/* Create SQL DELETE statement */sql = "DELETE from COMPANY where ID = 2";

PostgreSQL209/* Execute SQL query */W.exec( sql );W.commit();cout << "Records deleted successfully" << endl;/* Create SQL SELECT statement */sql = "SELECT * from COMPANY";/* Create a non-transactional object. */nontransaction N(C);/* Execute SQL query */result R( N.exec( sql ));/* List down all the records */for (result::const_iterator c = R.begin(); c != R.end(); ++c) {cout << "ID = " << c[0].as<int>() << endl;cout << "Name = " << c[1].as<string>() << endl;cout << "Age = " << c[2].as<int>() << endl;cout << "Address = " << c[3].as<string>() << endl;cout << "Salary = " << c[4].as<float>() << endl;}cout << "Operation done successfully" << endl;C.disconnect ();}catch (const std::exception &e){cerr << e.what() << std::endl;return 1;}return 0;}When the above given program is compiled and executed, it will produce the followingresult-Opened database successfully: testdb

PostgreSQL210Records deleted successfullyID = 3Name = TeddyAge = 23Address = NorwaySalary = 20000ID = 4Name = MarkAge = 25Address = Rich-MondSalary = 65000ID = 1Name = PaulAge = 32Address = CaliforniaSalary = 25000Operation done successfully

PostgreSQL211InstallationBefore we start using PostgreSQL in our Java programs, we need to make sure that wehave PostgreSQL JDBC and Java set up on the machine. You can check Java tutorial forJava installation on your machine. Now let us check how to set up PostgreSQL JDBC driver. Download the latest version of postgresql-(VERSION).jdbc.jar from postgresql-jdbc repository. Add downloaded jar file postgresql-(VERSION).jdbc.jar in your class path, or youcan use it along with -classpath option as explained below in the examples.The following section assumes you have little knowledge about Java JDBC concepts. If youdo not have, then it is suggested to spent half and hour with JDBC Tutorial to becomecomfortable with concepts explained below.ConnectingToDatabaseThe following Java code shows how to connect to an existing database. If the databasedoes not exist, then it will be created and finally a database object will be returned.import java.sql.Connection;import java.sql.DriverManager;public class PostgreSQLJDBC {public static void main(String args[]) {Connection c = null;try {Class.forName("org.postgresql.Driver");c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/testdb","postgres", "123");} catch (Exception e) {e.printStackTrace();System.err.println(e.getClass().getName()+": "+e.getMessage());System.exit(0);}System.out.println("Opened database successfully");}}45. PostgreSQL – JAVA Interface

PostgreSQL212Before you compile and run above program, find pg_hba.conf file in your PostgreSQLinstallation directory and add the following line-# IPv4 local connections:host all all 127.0.0.1/32 md5You can start/restart the postgres server, in case it is not running, using the followingcommand-[root@host]# service postgresql restartStopping postgresql service: [ OK ]Starting postgresql service: [ OK ]Now, let us compile and run the above program to connect with testdb. Here, we areusing postgres as user ID and 123 as password to access the database. You can changethis as per your database configuration and setup. We are also assuming current versionof JDBC driver postgresql-9.2-1002.jdbc3.jar is available in the current path.C:JavaPostgresIntegration>javac PostgreSQLJDBC.javaC:JavaPostgresIntegration>java -cp c:toolspostgresql-9.2-1002.jdbc3.jar;C:JavaPostgresIntegration PostgreSQLJDBCOpen database successfullyCreateaTableThe following Java program will be used to create a table in previously opened database.Make sure you do not have this table already in your target database.import java.sql.*;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;public class PostgreSQLJDBC {public static void main( String args[] ){Connection c = null;Statement stmt = null;try {Class.forName("org.postgresql.Driver");c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/testdb","manisha", "123");

PostgreSQL213System.out.println("Opened database successfully");stmt = c.createStatement();String sql = "CREATE TABLE COMPANY " +"(ID INT PRIMARY KEY NOT NULL," +" NAME TEXT NOT NULL, " +" AGE INT NOT NULL, " +" ADDRESS CHAR(50), " +" SALARY REAL)";stmt.executeUpdate(sql);stmt.close();c.close();} catch ( Exception e ) {System.err.println( e.getClass().getName()+": "+ e.getMessage() );System.exit(0);}System.out.println("Table created successfully");}}When a program is compiled and executed, it will create the COMPANY tablein testdb database and will display the following two lines-Opened database successfullyTable created successfullyINSERTOperationThe following Java program shows how we can create records in our COMPANY tablecreated in above example-import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;public class PostgreSQLJDBC {public static void main(String args[]) {Connection c = null;Statement stmt = null;try {Class.forName("org.postgresql.Driver");c = DriverManager

PostgreSQL214.getConnection("jdbc:postgresql://localhost:5432/testdb","manisha", "123");c.setAutoCommit(false);System.out.println("Opened database successfully");stmt = c.createStatement();String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "+ "VALUES (1, 'Paul', 32, 'California', 20000.00 );";stmt.executeUpdate(sql);sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "+ "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";stmt.executeUpdate(sql);sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "+ "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";stmt.executeUpdate(sql);sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "+ "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";stmt.executeUpdate(sql);stmt.close();c.commit();c.close();} catch (Exception e) {System.err.println( e.getClass().getName()+": "+ e.getMessage() );System.exit(0);}System.out.println("Records created successfully");}}When the above program is compiled and executed, it will create given records inCOMPANY table and will display the following two lines-Opened database successfully

PostgreSQL215Records created successfullySELECTOperationThe following Java program shows how we can fetch and display records from ourCOMPANY table created in above example-import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class PostgreSQLJDBC {public static void main( String args[] ){Connection c = null;Statement stmt = null;try {Class.forName("org.postgresql.Driver");c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/testdb","manisha", "123");c.setAutoCommit(false);System.out.println("Opened database successfully");stmt = c.createStatement();ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );while ( rs.next() ) {int id = rs.getInt("id");String name = rs.getString("name");int age = rs.getInt("age");String address = rs.getString("address");float salary = rs.getFloat("salary");System.out.println( "ID = " + id );System.out.println( "NAME = " + name );System.out.println( "AGE = " + age );System.out.println( "ADDRESS = " + address );System.out.println( "SALARY = " + salary );System.out.println();}

PostgreSQL216rs.close();stmt.close();c.close();} catch ( Exception e ) {System.err.println( e.getClass().getName()+": "+ e.getMessage() );System.exit(0);}System.out.println("Operation done successfully");}}When the program is compiled and executed, it will produce the following result-Opened database successfullyID = 1NAME = PaulAGE = 32ADDRESS = CaliforniaSALARY = 20000.0ID = 2NAME = AllenAGE = 25ADDRESS = TexasSALARY = 15000.0ID = 3NAME = TeddyAGE = 23ADDRESS = NorwaySALARY = 20000.0ID = 4NAME = MarkAGE = 25ADDRESS = Rich-MondSALARY = 65000.0Operation done successfully

PostgreSQL217UPDATEOperationThe following Java code shows how we can use the UPDATE statement to update anyrecord and then fetch and display updated records from our COMPANY table-import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class PostgreSQLJDBC {public static void main( String args[] ){Connection c = null;Statement stmt = null;try {Class.forName("org.postgresql.Driver");c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/testdb","manisha", "123");c.setAutoCommit(false);System.out.println("Opened database successfully");stmt = c.createStatement();String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";stmt.executeUpdate(sql);c.commit();ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );while ( rs.next() ) {int id = rs.getInt("id");String name = rs.getString("name");int age = rs.getInt("age");String address = rs.getString("address");float salary = rs.getFloat("salary");System.out.println( "ID = " + id );System.out.println( "NAME = " + name );System.out.println( "AGE = " + age );System.out.println( "ADDRESS = " + address );System.out.println( "SALARY = " + salary );

PostgreSQL218System.out.println();}rs.close();stmt.close();c.close();} catch ( Exception e ) {System.err.println( e.getClass().getName()+": "+ e.getMessage() );System.exit(0);}System.out.println("Operation done successfully");}}When the program is compiled and executed, it will produce the following result-Opened database successfullyID = 2NAME = AllenAGE = 25ADDRESS = TexasSALARY = 15000.0ID = 3NAME = TeddyAGE = 23ADDRESS = NorwaySALARY = 20000.0ID = 4NAME = MarkAGE = 25ADDRESS = Rich-MondSALARY = 65000.0ID = 1NAME = PaulAGE = 32ADDRESS = CaliforniaSALARY = 25000.0

PostgreSQL219Operation done successfullyDELETEOperationThe following Java code shows how we can use the DELETE statement to delete any recordand then fetch and display remaining records from our COMPANY table-import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class PostgreSQLJDBC6 {public static void main( String args[] ){Connection c = null;Statement stmt = null;try {Class.forName("org.postgresql.Driver");c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/testdb","manisha", "123");c.setAutoCommit(false);System.out.println("Opened database successfully");stmt = c.createStatement();String sql = "DELETE from COMPANY where ID=2;";stmt.executeUpdate(sql);c.commit();ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );while ( rs.next() ) {int id = rs.getInt("id");String name = rs.getString("name");int age = rs.getInt("age");String address = rs.getString("address");float salary = rs.getFloat("salary");System.out.println( "ID = " + id );System.out.println( "NAME = " + name );System.out.println( "AGE = " + age );

PostgreSQL220System.out.println( "ADDRESS = " + address );System.out.println( "SALARY = " + salary );System.out.println();}rs.close();stmt.close();c.close();} catch ( Exception e ) {System.err.println( e.getClass().getName()+": "+ e.getMessage() );System.exit(0);}System.out.println("Operation done successfully");}}When the program is compiled and executed, it will produce the following result-Opened database successfullyID = 3NAME = TeddyAGE = 23ADDRESS = NorwaySALARY = 20000.0

PostgreSQL221ID = 4NAME = MarkAGE = 25ADDRESS = Rich-MondSALARY = 65000.0ID = 1NAME = PaulAGE = 32ADDRESS = CaliforniaSALARY = 25000.0Operation done successfully

PostgreSQL222InstallationThe PostgreSQL extension is enabled by default in the latest releases of PHP 5.3.x. It ispossible to disable it by using --without-pgsql at compile time. Still you can use yumcommand to install PHP -PostgreSQL interface-yum install php-pgsqlBefore you start using the PHP PostgreSQL interface, find the pg_hba.conf file in yourPostgreSQL installation directory and add the following line-# IPv4 local connections:host all all 127.0.0.1/32 md5You can the start/restart postgres server, in case it is not running, using the followingcommand-[root@host]# service postgresql restartStopping postgresql service: [ OK ]Starting postgresql service: [ OK ]Windows users must enable php_pgsql.dll in order to use this extension. This DLL isincluded with Windows distributions in the latest releases of PHP 5.3.xFor detailed installation instructions, kindly check our PHP tutorial and its official website.PHPInterfaceAPIsThe following are important PHP routines, which can suffice your requirement to work withPostgreSQL database from your PHP program. If you are looking for a more sophisticatedapplication, then you can look into the PHP official documentation.S.No. API & Description1resource pg_connect ( string $connection_string [, int $connect_type ] )This opens a connection to a PostgreSQL database specified by theconnection_string.If PGSQL_CONNECT_FORCE_NEW is passed as connect_type, then a newconnection is created in case of a second call to pg_connect() , even if theconnection_string is identical to an existing connection.46. PostgreSQL – PHP Interface

PostgreSQL2232bool pg_connection_reset ( resource $connection )This routine resets the connection. It is useful for error recovery. ReturnsTRUE on success or FALSE on failure.3int pg_connection_status ( resource $connection )This routine returns the status of the specified connection. ReturnsPGSQL_CONNECTION_OK or PGSQL_CONNECTION_BAD.4string pg_dbname ([ resource $connection ] )This routine returns the name of the database that the given PostgreSQLconnection resource.5resource pg_prepare ([ resource $connection ], string $stmtname ,string $query )This submits a request to create a prepared statement with the givenparameters and waits for completion.6resource pg_execute ([ resource $connection ], string $stmtname ,array $params )This routine sends a request to execute a prepared statement with givenparameters and waits for the result.7resource pg_query ([ resource $connection ], string $query )This routine executes the query on the specified database connection.8array pg_fetch_row ( resource $result [, int $row ] )This routine fetches one row of data from the result associated with thespecified result resource.9array pg_fetch_all ( resource $result )This routine returns an array that contains all rows (records) in the resultresource.10int pg_affected_rows ( resource $result )This routine returns the number of rows affected by INSERT, UPDATE, andDELETE queries.11int pg_num_rows ( resource $result )

PostgreSQL224This routine returns the number of rows in a PostgreSQL result resource forexample number of rows returned by SELECT statement.12bool pg_close ([ resource $connection ] )This routine closes the non-persistent connection to a PostgreSQL databaseassociated with the given connection resource.13string pg_last_error ([ resource $connection ] )This routine returns the last error message for a given connection.14string pg_escape_literal ([ resource $connection ], string $data )This routine escapes a literal for insertion into a text field.15string pg_escape_string ([ resource $connection ], string $data )This routine escapes a string for querying the database.ConnectingtoDatabaseThe following PHP code shows how to connect to an existing database on a local machineand finally a database connection object will be returned.<?php$host = "host=127.0.0.1";$port = "port=5432";$dbname = "dbname=testdb";$credentials = "user=postgres password=pass123";$db = pg_connect( "$host $port $dbname $credentials" );if(!$db){echo "Error : Unable to open databasen";} else {echo "Opened database successfullyn";}?>Now, let us run the above given program to open our database testdb; if the database issuccessfully opened, then it will give the following message-Opened database successfully

PostgreSQL225CreateaTableThe following PHP program will be used to create a table in a previously created database-<?php$host = "host=127.0.0.1";$port = "port=5432";$dbname = "dbname=testdb";$credentials = "user=postgres password=pass123";$db = pg_connect( "$host $port $dbname $credentials" );if(!$db){echo "Error : Unable to open databasen";} else {echo "Opened database successfullyn";}$sql =<<<EOFCREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);EOF;$ret = pg_query($db, $sql);if(!$ret){echo pg_last_error($db);} else {echo "Table created successfullyn";}pg_close($db);?>When the above given program is executed, it will create COMPANY table inyour testdb and it will display the following messages-Opened database successfullyTable created successfully

PostgreSQL226INSERTOperationThe following PHP program shows how we can create records in our COMPANY tablecreated in above example-<?php$host = "host=127.0.0.1";$port = "port=5432";$dbname = "dbname=testdb";$credentials = "user=postgres password=pass123";$db = pg_connect( "$host $port $dbname $credentials" );if(!$db){echo "Error : Unable to open databasen";} else {echo "Opened database successfullyn";}$sql =<<<EOFINSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (2, 'Allen', 25, 'Texas', 15000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );EOF;$ret = pg_query($db, $sql);if(!$ret){echo pg_last_error($db);} else {echo "Records created successfullyn";}pg_close($db);?>

PostgreSQL227When the above given program is executed, it will create the given records in COMPANYtable and will display the following two lines-Opened database successfullyRecords created successfullySELECTOperationThe following PHP program shows how we can fetch and display records from ourCOMPANY table created in above example-<?php$host = "host=127.0.0.1";$port = "port=5432";$dbname = "dbname=testdb";$credentials = "user=postgres password=pass123";$db = pg_connect( "$host $port $dbname $credentials" );if(!$db){echo "Error : Unable to open databasen";} else {echo "Opened database successfullyn";}$sql =<<<EOFSELECT * from COMPANY;EOF;$ret = pg_query($db, $sql);if(!$ret){echo pg_last_error($db);exit;}while($row = pg_fetch_row($ret)){echo "ID = ". $row[0] . "n";echo "NAME = ". $row[1] ."n";echo "ADDRESS = ". $row[2] ."n";echo "SALARY = ".$row[4] ."nn";}echo "Operation done successfullyn";

PostgreSQL228pg_close($db);?>When the above given program is executed, it will produce the following result. Keep anote that fields are returned in the sequence they were used while creating table.Opened database successfullyID = 1NAME = PaulADDRESS = CaliforniaSALARY = 20000ID = 2NAME = AllenADDRESS = TexasSALARY = 15000ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000Operation done successfullyUPDATEOperationThe following PHP code shows how we can use the UPDATE statement to update any recordand then fetch and display updated records from our COMPANY table-<?php$host = "host=127.0.0.1";$port = "port=5432";$dbname = "dbname=testdb";$credentials = "user=postgres password=pass123";

PostgreSQL229$db = pg_connect( "$host $port $dbname $credentials" );if(!$db){echo "Error : Unable to open databasen";} else {echo "Opened database successfullyn";}$sql =<<<EOFUPDATE COMPANY set SALARY = 25000.00 where ID=1;EOF;$ret = pg_query($db, $sql);if(!$ret){echo pg_last_error($db);exit;} else {echo "Record updated successfullyn";}$sql =<<<EOFSELECT * from COMPANY;EOF;$ret = pg_query($db, $sql);if(!$ret){echo pg_last_error($db);exit;}while($row = pg_fetch_row($ret)){echo "ID = ". $row[0] . "n";echo "NAME = ". $row[1] ."n";echo "ADDRESS = ". $row[2] ."n";echo "SALARY = ".$row[4] ."nn";}echo "Operation done successfullyn";pg_close($db);?>When the above given program is executed, it will produce the following result-

PostgreSQL230Opened database successfullyRecord updated successfullyID = 2NAME = AllenADDRESS = 25SALARY = 15000ID = 3NAME = TeddyADDRESS = 23SALARY = 20000ID = 4NAME = MarkADDRESS = 25SALARY = 65000ID = 1NAME = PaulADDRESS = 32SALARY = 25000Operation done successfullyDELETEOperationThe following PHP code shows how we can use the DELETE statement to delete any recordand then fetch and display the remaining records from our COMPANY table-<?php$host = "host=127.0.0.1";$port = "port=5432";$dbname = "dbname=testdb";$credentials = "user=postgres password=pass123";$db = pg_connect( "$host $port $dbname $credentials" );if(!$db){echo "Error : Unable to open databasen";} else {echo "Opened database successfullyn";}$sql =<<<EOF

PostgreSQL231DELETE from COMPANY where ID=2;EOF;$ret = pg_query($db, $sql);if(!$ret){echo pg_last_error($db);exit;} else {echo "Record deleted successfullyn";}$sql =<<<EOFSELECT * from COMPANY;EOF;$ret = pg_query($db, $sql);if(!$ret){echo pg_last_error($db);exit;}while($row = pg_fetch_row($ret)){echo "ID = ". $row[0] . "n";echo "NAME = ". $row[1] ."n";echo "ADDRESS = ". $row[2] ."n";echo "SALARY = ".$row[4] ."nn";}echo "Operation done successfullyn";pg_close($db);?>When the above given program is executed, it will produce the following result-Opened database successfully

PostgreSQL232Record deleted successfullyID = 3NAME = TeddyADDRESS = 23SALARY = 20000ID = 4NAME = MarkADDRESS = 25SALARY = 65000ID = 1NAME = PaulADDRESS = 32SALARY = 25000Operation done successfully

PostgreSQL233InstallationThe PostgreSQL can be integrated with Perl using Perl DBI module, which is a databaseaccess module for the Perl programming language. It defines a set of methods, variablesand conventions that provide a standard database interface.Here are simple steps to install DBI module on your Linux/Unix machine-$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz$ tar xvfz DBI-1.625.tar.gz$ cd DBI-1.625$ perl Makefile.PL$ make$ make installIf you need to install SQLite driver for DBI, then it can be installed as follows-$ wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz$ tar xvfz DBD-Pg-2.19.3.tar.gz$ cd DBD-Pg-2.19.3$ perl Makefile.PL$ make$ make installBefore you start using Perl PostgreSQL interface, find the pg_hba.conf file in yourPostgreSQL installation directory and add the following line-# IPv4 local connections:host all all 127.0.0.1/32 md5You can start/restart the postgres server, in case it is not running, using the followingcommand-[root@host]# service postgresql restartStopping postgresql service: [ OK ]Starting postgresql service: [ OK ]47. PostgreSQL – Perl Interface

PostgreSQL234DBIInterfaceAPIsFollowing are the important DBI routines, which can suffice your requirement to work withSQLite database from your Perl program. If you are looking for a more sophisticatedapplication, then you can look into Perl DBI official documentation.S. No. API & Description1DBI->connect($data_source, "userid", "password", %attr)Establishes a database connection, or session, to the requested $data_source.Returns a database handle object if the connection succeeds.Datasource has the form like: DBI:Pg:dbname=$database;host=127.0.0.1;port=5432 Pg isPostgreSQL driver name and testdb is the name of database.2$dbh->do($sql)This routine prepares and executes a single SQL statement. Returns thenumber of rows affected or undef on error. A return value of -1 means thenumber of rows is not known, not applicable, or not available. Here $dbh is ahandle returned by DBI->connect() call.3$dbh->prepare($sql)This routine prepares a statement for later execution by the database engineand returns a reference to a statement handle object.4$sth->execute()This routine performs whatever processing is necessary to execute theprepared statement. An undef is returned if an error occurs. A successfulexecute always returns true regardless of the number of rows affected. Here$sth is a statement handle returned by $dbh->prepare($sql) call.5$sth->fetchrow_array()This routine fetches the next row of data and returns it as a list containing thefield values. Null fields are returned as undef values in the list.6$DBI::errThis is equivalent to $h->err, where $h is any of the handle types like $dbh,$sth, or $drh. This returns native database engine error code from the lastdriver method called.

PostgreSQL2357$DBI::errstrThis is equivalent to $h->errstr, where $h is any of the handle types like$dbh, $sth, or $drh. This returns the native database engine error messagefrom the last DBI method called.8$dbh->disconnect()This routine closes a database connection previously opened by a call to DBI->connect().ConnectingtoDatabaseThe following Perl code shows how to connect to an existing database. If the databasedoes not exist, then it will be created and finally a database object will be returned.#!/usr/bin/perluse DBI;use strict;my $driver = "Pg";my $database = "testdb";my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";my $userid = "postgres";my $password = "pass123";my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })or die $DBI::errstr;print "Opened database successfullyn";Now, let us run the above given program to open our database testdb; if the database issuccessfully opened then it will give the following message-Open database successfullyCreateaTableThe following Perl program will be used to create a table in previously created database-#!/usr/bin/perluse DBI;use strict;my $driver = "Pg";my $database = "testdb";my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";

PostgreSQL236my $userid = "postgres";my $password = "pass123";my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })or die $DBI::errstr;print "Opened database successfullyn";my $stmt = qq(CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL););my $rv = $dbh->do($stmt);if($rv < 0){print $DBI::errstr;} else {print "Table created successfullyn";}$dbh->disconnect();When the above given program is executed, it will create COMPANY table inyour testdb and it will display the following messages-Opened database successfullyTable created successfullyINSERTOperationThe following Perl program shows how we can create records in our COMPANY tablecreated in above example-#!/usr/bin/perluse DBI;use strict;my $driver = "Pg";my $database = "testdb";my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";my $userid = "postgres";my $password = "pass123";

PostgreSQL237my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })or die $DBI::errstr;print "Opened database successfullyn";my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 ));my $rv = $dbh->do($stmt) or die $DBI::errstr;$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));$rv = $dbh->do($stmt) or die $DBI::errstr;$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ));$rv = $dbh->do($stmt) or die $DBI::errstr;$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););$rv = $dbh->do($stmt) or die $DBI::errstr;print "Records created successfullyn";$dbh->disconnect();When the above given program is executed, it will create given records in COMPANY tableand will display the following two lines-Opened database successfullyRecords created successfullySELECTOperationThe following Perl program shows how we can fetch and display records from our COMPANYtable created in above example-#!/usr/bin/perluse DBI;use strict;my $driver = "Pg";my $database = "testdb";my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";

PostgreSQL238my $userid = "postgres";my $password = "pass123";my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })or die $DBI::errstr;print "Opened database successfullyn";my $stmt = qq(SELECT id, name, address, salary from COMPANY;);my $sth = $dbh->prepare( $stmt );my $rv = $sth->execute() or die $DBI::errstr;if($rv < 0){print $DBI::errstr;}while(my @row = $sth->fetchrow_array()) {print "ID = ". $row[0] . "n";print "NAME = ". $row[1] ."n";print "ADDRESS = ". $row[2] ."n";print "SALARY = ". $row[3] ."nn";}print "Operation done successfullyn";$dbh->disconnect();When the above given program is executed, it will produce the following result-Opened database successfullyID = 1NAME = PaulADDRESS = CaliforniaSALARY = 20000ID = 2NAME = AllenADDRESS = TexasSALARY = 15000ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000

PostgreSQL239ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000Operation done successfullyUPDATEOperationThe following Perl code shows how we can use the UPDATE statement to update any recordand then fetch and display updated records from our COMPANY table-#!/usr/bin/perluse DBI;use strict;my $driver = "Pg";my $database = "testdb";my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";my $userid = "postgres";my $password = "pass123";my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })or die $DBI::errstr;print "Opened database successfullyn";my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);my $rv = $dbh->do($stmt) or die $DBI::errstr;if( $rv < 0 ){print $DBI::errstr;}else{print "Total number of rows updated : $rvn";}$stmt = qq(SELECT id, name, address, salary from COMPANY;);my $sth = $dbh->prepare( $stmt );$rv = $sth->execute() or die $DBI::errstr;if($rv < 0){print $DBI::errstr;}while(my @row = $sth->fetchrow_array()) {print "ID = ". $row[0] . "n";

PostgreSQL240print "NAME = ". $row[1] ."n";print "ADDRESS = ". $row[2] ."n";print "SALARY = ". $row[3] ."nn";}print "Operation done successfullyn";$dbh->disconnect();When the above given program is executed, it will produce the following result-Opened database successfullyTotal number of rows updated : 1ID = 1NAME = PaulADDRESS = CaliforniaSALARY = 25000ID = 2NAME = AllenADDRESS = TexasSALARY = 15000ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000Operation done successfullyDELETEOperationThe following Perl code shows how we can use the DELETE statement to delete any recordand then fetch and display the remaining records from our COMPANY table-#!/usr/bin/perl

PostgreSQL241use DBI;use strict;my $driver = "Pg";my $database = "testdb";my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";my $userid = "postgres";my $password = "pass123";my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })or die $DBI::errstr;print "Opened database successfullyn";my $stmt = qq(DELETE from COMPANY where ID=2;);my $rv = $dbh->do($stmt) or die $DBI::errstr;if( $rv < 0 ){print $DBI::errstr;}else{print "Total number of rows deleted : $rvn";}$stmt = qq(SELECT id, name, address, salary from COMPANY;);my $sth = $dbh->prepare( $stmt );$rv = $sth->execute() or die $DBI::errstr;if($rv < 0){print $DBI::errstr;}while(my @row = $sth->fetchrow_array()) {print "ID = ". $row[0] . "n";print "NAME = ". $row[1] ."n";print "ADDRESS = ". $row[2] ."n";print "SALARY = ". $row[3] ."nn";}print "Operation done successfullyn";$dbh->disconnect();When the above given program is executed, it will produce the following result-Opened database successfullyTotal number of rows deleted : 1

PostgreSQL242ID = 1NAME = PaulADDRESS = CaliforniaSALARY = 25000ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000Operation done successfully

PostgreSQL243InstallationThe PostgreSQL can be integrated with Python using psycopg2 module. sycopg2 is aPostgreSQL database adapter for the Python programming language. psycopg2 waswritten with the aim of being very small and fast, and stable as a rock. You do not needto install this module separately because it is shipped, by default, along with Pythonversion 2.5.x onwards.If you do not have it installed on your machine then you can use yum command to installit as follows-$yum install python-psycopg2To use psycopg2 module, you must first create a Connection object that represents thedatabase and then optionally you can create cursor object which will help you in executingall the SQL statements.Pythonpsycopg2moduleAPIsFollowing are important psycopg2 module routines, which can suffice your requirement towork with PostgreSQL database from your Python program. If you are looking for a moresophisticated application, then you can look into Python psycopg2 module's officialdocumentation.S. No. API & Description1psycopg2.connect(database="testdb", user="postgres",password="cohondob", host="127.0.0.1", port="5432")This API opens a connection to the PostgreSQL database. If database isopened successfully, it returns a connection object.2connection.cursor()This routine creates a cursor which will be used throughout of your databaseprogramming with Python.3cursor.execute(sql [, optional parameters])This routine executes an SQL statement. The SQL statement may beparameterized (i.e., placeholders instead of SQL literals). The psycopg2module supports placeholder using %s signFor example:cursor.execute("insert into people values (%s, %s)", (who,age))48. PostgreSQL – Python Interface

PostgreSQL2444curosr.executemany(sql, seq_of_parameters)This routine executes an SQL command against all parameter sequences ormappings found in the sequence sql.5curosr.callproc(procname[, parameters])This routine executes a stored database procedure with the given name. Thesequence of parameters must contain one entry for each argument that theprocedure expects.6cursor.rowcountThis read-only attribute which returns the total number of database rows thathave been modified, inserted, or deleted by the last last execute*().7connection.commit()This method commits the current transaction. If you don't call this method,anything you did since the last call to commit() is not visible from otherdatabase connections.8connection.rollback()This method rolls back any changes to the database since the last call tocommit().9connection.close()This method closes the database connection. Note that this does notautomatically call commit(). If you just close your database connectionwithout calling commit() first, your changes will be lost!10cursor.fetchone()This method fetches the next row of a query result set, returning a singlesequence, or None when no more data is available.11cursor.fetchmany([size=cursor.arraysize])This routine fetches the next set of rows of a query result, returning a list. Anempty list is returned when no more rows are available. The method tries tofetch as many rows as indicated by the size parameter.12cursor.fetchall()This routine fetches all (remaining) rows of a query result, returning a list. Anempty list is returned when no rows are available.

PostgreSQL245ConnectingtoDatabaseThe following Python code shows how to connect to an existing database. If the databasedoes not exist, then it will be created and finally a database object will be returned.#!/usr/bin/pythonimport psycopg2conn = psycopg2.connect(database="testdb", user="postgres", password="pass123",host="127.0.0.1", port="5432")print "Opened database successfully"Here, you can also supply database testdb as name and if database is successfullyopened, then it will give the following message-Open database successfullyCreateaTableThe following Python program will be used to create a table in previously created database-#!/usr/bin/pythonimport psycopg2conn = psycopg2.connect(database="testdb", user="postgres", password="pass123",host="127.0.0.1", port="5432")print "Opened database successfully"cur = conn.cursor()cur.execute('''CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);''')print "Table created successfully"conn.commit()conn.close()When above program is executed, it will create COMPANY table in your test.db and it willdisplay the following messages:Opened database successfullyTable created successfully

PostgreSQL246INSERTOperationThe following Python program shows how we can create records in our COMPANY tablecreated in the above example-#!/usr/bin/pythonimport psycopg2conn = psycopg2.connect(database="testdb", user="postgres", password="pass123",host="127.0.0.1", port="5432")print "Opened database successfully"cur = conn.cursor()cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )");cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");conn.commit()print "Records created successfully";conn.close()When the above given program is executed, it will create given records in COMPANY tableand will display the following two lines-Opened database successfullyRecords created successfully

PostgreSQL247SELECTOperationThe following Python program shows how we can fetch and display records from ourCOMPANY table created in the above example-#!/usr/bin/pythonimport psycopg2conn = psycopg2.connect(database="testdb", user="postgres", password="pass123",host="127.0.0.1", port="5432")print "Opened database successfully"cur = conn.cursor()cur.execute("SELECT id, name, address, salary from COMPANY")rows = cur.fetchall()for row in rows:print "ID = ", row[0]print "NAME = ", row[1]print "ADDRESS = ", row[2]print "SALARY = ", row[3], "n"print "Operation done successfully";conn.close()When the above given program is executed, it will produce the following result-Opened database successfullyID = 1NAME = PaulADDRESS = CaliforniaSALARY = 20000.0ID = 2NAME = AllenADDRESS = TexasSALARY = 15000.0ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000.0ID = 4NAME = MarkADDRESS = Rich-Mond

PostgreSQL248SALARY = 65000.0Operation done successfullyUPDATEOperationThe following Python code shows how we can use the UPDATE statement to update anyrecord and then fetch and display updated records from our COMPANY table-#!/usr/bin/pythonimport psycopg2conn = psycopg2.connect(database="testdb", user="postgres", password="pass123",host="127.0.0.1", port="5432")print "Opened database successfully"cur = conn.cursor()cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")conn.commitprint "Total number of rows updated :", cur.rowcountcur.execute("SELECT id, name, address, salary from COMPANY")rows = cur.fetchall()for row in rows:print "ID = ", row[0]print "NAME = ", row[1]print "ADDRESS = ", row[2]print "SALARY = ", row[3], "n"print "Operation done successfully";conn.close()When the above given program is executed, it will produce the following result-Opened database successfullyTotal number of rows updated : 1ID = 1NAME = PaulADDRESS = CaliforniaSALARY = 25000.0ID = 2NAME = Allen

PostgreSQL249ADDRESS = TexasSALARY = 15000.0ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000.0ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000.0Operation done successfullyDELETEOperationThe following Python code shows how we can use the DELETE statement to delete anyrecord and then fetch and display the remaining records from our COMPANY table-#!/usr/bin/pythonimport psycopg2conn = psycopg2.connect(database="testdb", user="postgres", password="pass123",host="127.0.0.1", port="5432")print "Opened database successfully"cur = conn.cursor()cur.execute("DELETE from COMPANY where ID=2;")conn.commitprint "Total number of rows deleted :", cur.rowcountcur.execute("SELECT id, name, address, salary from COMPANY")rows = cur.fetchall()for row in rows:print "ID = ", row[0]print "NAME = ", row[1]print "ADDRESS = ", row[2]print "SALARY = ", row[3], "n"

PostgreSQL250print "Operation done successfully";conn.close()When the above given program is executed, it will produce the following result-Opened database successfullyTotal number of rows deleted : 1ID = 1NAME = PaulADDRESS = CaliforniaSALARY = 20000.0ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000.0ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000.0Operation done successfully

Movatterモバイル変換

Change Language

Postgresql tutorial

Embed presentation

Recommended

More Related Content

What's hot

Similar to Postgresql tutorial

More from Ashoka Vanjare

Recently uploaded

Postgresql tutorial