PostgreSQL Developer's Guide

Design, develop, and implement streamlined databases with PostgreSQL

PostgreSQL Developer's Guide

Progressing
Ibrar Ahmed, Asif Fayyaz, Amjad Shahzad

4 customer reviews
Design, develop, and implement streamlined databases with PostgreSQL
$26.99
$44.99
RRP $26.99
RRP $44.99
eBook
Print + eBook
$29.99 p/month

Join FREE for a month

Instantly access this course right now and get the skills you need in 2017

With unlimited access to a constantly growing library of over 4,000 eBooks and Videos, a subscription to Mapt gives you everything you need to get that next promotion or to land that dream job. Cancel anytime.

Code Files
Free Sample

Book Details

ISBN 139781783989027
Paperback270 pages

Book Description

PostgreSQL is an enterprise-level database that competes among proprietary database vendors, owing to its remarkable feature set and reliability without the expensive licensing overhead.

This book is a comprehensive and pragmatic guide to developing databases in PostgreSQL. Beginning with a refresher of basic SQL skills, you will gradually be exposed to advanced concepts, such as learning how to program in native PostgreSQL procedural languages, implementing triggers, custom functions, and views. You will learn database optimization techniques such as query optimization and indexing while exploring extensive code examples. Lastly, you will explore foreign data wrappers, implementing extensibility, and improving maintainability.

Table of Contents

Chapter 1: Getting Started with PostgreSQL
Writing queries using psql
Creating, altering, and truncating a table
Inserting, updating, and deleting data from tables
PostgreSQL's supported data types
PostgreSQL's operators and usage
Constraints in PostgreSQL
Privileges in PostgreSQL
Summary
Chapter 2: The Procedural Language
Why should you use PL/pgSQL?
Installing PL/pgSQL
Understanding the structure of PL/pgSQL
Using comments in PL/pgSQL
Declaring variables in PL/pgSQL
Declaring function parameters
Declaring the %TYPE attribute
Declaring the row-type and record type variables
Statements and expressions
Control structures
Exception handling
Native support for other procedural languages
Summary
Chapter 3: Working with Indexes
What is an index?
How to create an index
How to drop an index
Types of indexes
Index types
Index bloating
Reindexing
Points to ponder
Summary
Chapter 4: Triggers, Rules, and Views
Understanding triggers
PostgreSQL rules
Understanding views
Summary
Chapter 5: Window Functions
Understanding window functions
Summary
Chapter 6: Partitioning
Creating a table partition
Partitioning in PostgreSQL
Summary
Chapter 7: Query Optimization
What is EXPLAIN?
Cost parameters
Sequential scans
Index scans
Bitmap scans
Common Table Expressions
Joins
Query planning
Hints
Configuration parameters to optimize queries
Summary
Chapter 8: Dealing with Large Objects
Why large objects?
PostgreSQL large objects
Manipulating large objects through the libpq client interface library
Summary
Chapter 9: Communicating with PostgreSQL Using LibPQ
Connecting and disconnecting to PostgreSQL
Establishing a connection asynchronously
Connection-supporting functions
Executing a query
Executing prepared statements
Retrieving data
Using status functions
Summary
Chapter 10: Embedded SQL in C – ECPG
Writing ECPG programs
Compiling an ECPG program
Running SQL commands
Error handling
Summary
Chapter 11: Foreign Data Wrapper
Creating foreign data wrappers
The basic components of foreign data wrappers
Loading foreign data wrappers
Using foreign data wrappers
Summary
Chapter 12: Extensions
Features of an extension
Creating extensions
Altering extensions
Dropping extensions
How to check available extensions
Additional supplied extensions
Other available extensions
Summary

What You Will Learn

  • Refresh your knowledge of SQL with PostgreSQL-supported DDL and DML
  • Explore native PostgreSQL procedural languages and learn how to write custom functions
  • Leverage the power of indexes for optimal database efficiency
  • Unfold tricky trigger concepts and implement them in native procedural languages
  • Discover how to implement views, partitioned tables, and window functions in PostgreSQL
  • Design efficient queries with query optimization and query analysis
  • Communicate with PostgreSQL servers using foreign data wrappers such as LibPQ and ECPG
  • Extend database powers using foreign data wrappers and achieve maintainability via PostgreSQL extensions

Authors

Continue reading

Table of Contents

Chapter 1: Getting Started with PostgreSQL
Writing queries using psql
Creating, altering, and truncating a table
Inserting, updating, and deleting data from tables
PostgreSQL's supported data types
PostgreSQL's operators and usage
Constraints in PostgreSQL
Privileges in PostgreSQL
Summary
Chapter 2: The Procedural Language
Why should you use PL/pgSQL?
Installing PL/pgSQL
Understanding the structure of PL/pgSQL
Using comments in PL/pgSQL
Declaring variables in PL/pgSQL
Declaring function parameters
Declaring the %TYPE attribute
Declaring the row-type and record type variables
Statements and expressions
Control structures
Exception handling
Native support for other procedural languages
Summary
Chapter 3: Working with Indexes
What is an index?
How to create an index
How to drop an index
Types of indexes
Index types
Index bloating
Reindexing
Points to ponder
Summary
Chapter 4: Triggers, Rules, and Views
Understanding triggers
PostgreSQL rules
Understanding views
Summary
Chapter 5: Window Functions
Understanding window functions
Summary
Chapter 6: Partitioning
Creating a table partition
Partitioning in PostgreSQL
Summary
Chapter 7: Query Optimization
What is EXPLAIN?
Cost parameters
Sequential scans
Index scans
Bitmap scans
Common Table Expressions
Joins
Query planning
Hints
Configuration parameters to optimize queries
Summary
Chapter 8: Dealing with Large Objects
Why large objects?
PostgreSQL large objects
Manipulating large objects through the libpq client interface library
Summary
Chapter 9: Communicating with PostgreSQL Using LibPQ
Connecting and disconnecting to PostgreSQL
Establishing a connection asynchronously
Connection-supporting functions
Executing a query
Executing prepared statements
Retrieving data
Using status functions
Summary
Chapter 10: Embedded SQL in C – ECPG
Writing ECPG programs
Compiling an ECPG program
Running SQL commands
Error handling
Summary
Chapter 11: Foreign Data Wrapper
Creating foreign data wrappers
The basic components of foreign data wrappers
Loading foreign data wrappers
Using foreign data wrappers
Summary
Chapter 12: Extensions
Features of an extension
Creating extensions
Altering extensions
Dropping extensions
How to check available extensions
Additional supplied extensions
Other available extensions
Summary

Book Details

ISBN 139781783989027
Paperback270 pages
Read More
From 4 reviews

Read More Reviews

DEAL OF THE DAY

Sign up here to get exclusive deep discounts on our latest and bestselling eBooks, delivered straight to your inbox every day.

FREE LEARNING FOREVER

Master new IT skills and unlock valuable software & web development knowledge with a FREE eBook every day.

Recommended for You

PostgreSQL 9 Administration Cookbook - Second Edition
$ 47.99
Over 150 recipes to help you run an efficient PostgreSQL database in the cloud
PostgreSQL 9.0 High Performance
$ 29.99
If you’re an intermediate to advanced database administrator, this book is the shortcut to optimizing and troubleshooting your PostgreSQL database. With a balanced mix of theory and practice, it will quickly hone your expertise.
PostgreSQL Administration Essentials
$ 16.99
Discover efficient ways to administer, monitor, replicate, and handle your PostgreSQL databases
PostgreSQL Server Programming
$ 29.99
Take your skills with PostgreSQL to a whole new level with this fascinating guide to server programming. A step by step approach with illuminating examples will educate you in the full range of possibilities.
PostgreSQL Cookbook
$ 26.99
Over 90 hands-on recipes to effectively manage, administer, and design solutions using PostgreSQL
PostgreSQL for Data Architects
$ 39.99
Discover how to design, develop, and maintain your database application effectively with PostgreSQL