PostgreSQL Developer's Guide

Design, develop, and implement streamlined databases with PostgreSQL
PostgreSQL Developer's Guide Book Cover
Preview in Mapt
Code Files

PostgreSQL Developer's Guide

Ibrar Ahmed, Asif Fayyaz, Amjad Shahzad

1 customer reviews
Design, develop, and implement streamlined databases with PostgreSQL
Quick links: > Table of contents > What will you learn? > Product reviews
Mapt Subscription
FREE
€29.73/m after trial
eBook
€9.66
RRP €26.16
Save 63%
Print + eBook
€35.99
RRP €35.99
What do I get with a Mapt Pro subscription?
  • Unlimited access to all Packt’s 5,000+ eBooks and Videos
  • Early Access content, Progress Tracking, and Assessments
  • 1 Free eBook or Video to download and keep every month after trial
What do I get with an eBook?
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with Print & eBook?
  • Get a paperback copy of the book delivered to you
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with a Video?
  • Download this Video course in MP4 format
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
€0.00
€9.66
€35.99
€29.74 p/m after trial
RRP €26.16
RRP €35.99
Subscription
eBook
Print + eBook
Start 14 Day Trial

Frequently bought together


PostgreSQL Developer's Guide Book Cover
PostgreSQL Developer's Guide
€ 26.16
€ 9.66
PostgreSQL Developer's Guide
Feb 2015
270 pages
€ 9.66
Learning PostgreSQL Book Cover
Learning PostgreSQL
€ 47.58
€ 9.66
Learning PostgreSQL
Nov 2015
464 pages
€ 9.66
Buy 2 for €19.32
Save €45.74
Add to Cart

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 1 reviews

Read More Reviews

Recommended for You

Learning PostgreSQL Book Cover
Learning PostgreSQL
€ 47.58
€ 9.66
Learning PostgreSQL
Nov 2015
464 pages
€ 9.66
PostgreSQL 9 Administration Cookbook - Second Edition Book Cover
PostgreSQL 9 Administration Cookbook - Second Edition
€ 47.58
€ 9.66
PostgreSQL 9 Administration Cookbook - Second Edition
Apr 2015
504 pages
€ 9.66
PostgreSQL Administration Cookbook, 9.5/9.6 Edition Book Cover
PostgreSQL Administration Cookbook, 9.5/9.6 Edition
€ 54.72
€ 9.66
PostgreSQL Administration Cookbook, 9.5/9.6 Edition
Apr 2017
556 pages
€ 9.66
PostgreSQL 9.0 High Performance Book Cover
PostgreSQL 9.0 High Performance
€ 28.54
€ 9.66
PostgreSQL 9.0 High Performance
Oct 2010
468 pages
€ 9.66
PostgreSQL High Availability Cookbook - Second Edition Book Cover
PostgreSQL High Availability Cookbook - Second Edition
€ 49.96
€ 9.66
PostgreSQL High Availability Cookbook - Second Edition
Feb 2017
536 pages
€ 9.66
PostgreSQL High Performance Cookbook Book Cover
PostgreSQL High Performance Cookbook
€ 49.96
€ 9.66
PostgreSQL High Performance Cookbook
Mar 2017
360 pages
€ 9.66