This is a test
- 464 pages
- English
- ePUB (mobile friendly)
- Available on iOS & Android
eBook - ePub
Learning PostgreSQL
Book details
Book preview
Table of contents
Citations
About This Book
Create, develop and manage relational databases in real world applications using PostgreSQL
About This Book
- Learn about the PostgreSQL development life cycle including its testing and refactoring
- Build productive database solutions and use them in Java applications
- A comprehensive guide to learn about SQL, PostgreSQL procedural language and PL/pgSQL
Who This Book Is For
If you are a student, database developer or an administrator, interested in developing and maintaining a PostgreSQL database, then this book is for you. No knowledge of database programming or administration is necessary.
What You Will Learn
- Learn concepts of data modelling and relation algebra
- Install and set up PostgreSQL database server and client software
- Implement data structures in PostgreSQL
- Manipulate data in the database using SQL
- Implement data processing logic in the database with stored functions, triggers and views
- Test database solutions and assess the performance
- Integrate database with Java applications
- Detailed knowledge of the main PostgreSQL building objects, most used extensions
- Practice database development life cycle including analysis, modelling, (documentation), testing, bug fixes and refactoring
In Detail
PostgreSQL is one of the most powerful and easy to use database management systems. It has strong support from the community and is being actively developed with a new release every year. PostgreSQL supports the most advanced features included in SQL standards. Also it provides NoSQL capabilities, and very rich data types and extensions. All that makes PostgreSQL a very attractive solution in various kinds of software systems.
The book starts with the introduction of relational databases with PostegreSQL. It then moves on to covering data definition language (DDL) with emphasis on PostgreSQL and common DDL commands supported by ANSI SQL. You will then learn the data manipulation language (DML), and advanced topics like locking and multi version concurrency control (MVCC). This will give you a very robust background to tune and troubleshoot your application. The book then covers the implementation of data models in the database such as creating tables, setting up integrity constraints, building indexes, defining views and other schema objects. Next, it will give you an overview about the NoSQL capabilities of PostgreSQL along with Hstore, XML, Json and arrays. Finally by the end of the book, you'll learn to use the JDBC driver and manipulate data objects in the Hibernate framework.
Style and approach
An easy-to-follow guide to learn programming build applications with PostgreSQL, and manage a PostgreSQL database instance.
Frequently asked questions
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Yes, you can access Learning PostgreSQL by Salahaldin Juba, Achim Vannahme, Andrey Volkov in PDF and/or ePUB format, as well as other popular books in Computer Science & Databases. We have over one million books available in our catalogue for you to explore.
Information
Learning PostgreSQL
Table of Contents
Learning PostgreSQL
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers, and more
Why subscribe?
Free access for Packt account holders
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example code
Errata
Piracy
Questions
1. Relational Databases
Database management systems
A brief history
Database categories
The NoSQL databases
The CAP theorem
NoSQL motivation
Key value databases
Columnar databases
Document databases
Graph databases
Relational and object relational databases
ACID properties
The SQL Language
Basic concepts
Relation
Tuple
Attribute
Constraint
Domain integrity constraint
Entity integrity constraint
Referential integrity constraints
Semantic constraints
Relational algebra
The SELECT and PROJECT operations
The RENAME operation
The Set theory operations
The CROSS JOIN (Cartesian product) operation
Data modeling
Data model perspectives
The entity-relation model
Sample application
Entities, attributes, and keys
Mapping ER to Relations
UML class diagrams
Summary
2. PostgreSQL in Action
An overview of PostgreSQL
PostgreSQL history
The advantages of PostgreSQL
Business advantages of PostgreSQL
PostgreSQL user advantages
PostgreSQL applications
Success stories
Forks
PostgreSQL architecture
PostgreSQL abstract architecture
The PostgreSQL community
PostgreSQL capabilities
Replication
Security
Extension
NoSQL capabilities
Foreign data wrapper
Performance
Very rich SQL constructs
Installing PostgreSQL
Installing PostgreSQL on Ubuntu
Client installation
Server installation
Basic server configuration
Installing PostgreSQL on Windows
The PostgreSQL clients
The psql client
Psql advanced settings
PostgreSQL utility tools
Backup and replication
Utilities
PgAdmin III
Summary
3. PostgreSQL Basic Building Blocks
Database coding
Database naming conventions
PostgreSQL identifiers
Documentation
Version control system
PostgreSQL objects hierarchy
Template databases
User databases
Roles
Tablespace
Template procedural languages
Settings
Setting parameters
Setting a context
PostgreSQL high-level object interaction
PostgreSQL database components
Schema
Schema usages
Table
PostgreSQL native data types
Numeric types
Character types
Date and time types
The car web portal database
Summary
4. PostgreSQL Advanced Building Blocks
Views
View synopsis
Views categories
Materialized views
Updatable views
Indexes
Index types
Partial indexes
Indexes on expressions
Unique indexes
Multicolumn indexes
Best practices on indexes
Functions
PostgreSQL native programming languages
Creating a function in the C language
Creating functions in the SQL language
Creating a function in the PL/pgSQL language
PostgreSQL function usages
PostgreSQL function dependency
PostgreSQL function categories
PostgreSQL anonymous functions
PostgreSQL user-defined data types
The PostgreSQL CREATE DOMAIN command
The PostgreSQL CREATE TYPE command
Triggers and rule systems
The PostgreSQL rule system
The PostgreSQL trigger system
Triggers with arguments
Using triggers to make views updatable
Summary
5. SQL Language
SQL fundamentals
SQL lexical structure
Querying the data with the SELECT statement
The structure of the SELECT query
Select-list
SQL expressions
DISTINCT
FROM clause
Selecting from multiple tables
Self-joins
WHERE clause
Comparison operators
Pattern matching
Row and array comparison constructs
Grouping and aggregation
GROUP BY clause
HAVING clause
Ordering and limiting the results
Subqueries
Set operations – UNION, EXCEPT, and INTERSECT
Dealing with NULLs
Changing the data in the database
INSERT statement
UPDATE statement
UPDATE using sub-select
UPDATE using additional tables
DELETE statement
TRUNCATE statement
Summary
6. Advanced Query Writing
Common table expressions
Reusing SQL code with CTE
Recursive and hierarchical queries
Changing data in multiple tables at a time
Window functions
Window definition
The WINDOW clause
Using window functions
Window functions with grouping and aggregation
Advanced SQL
Selecting the first records
Set returning functions
Lateral subqueries
Advanced usage of aggregating functions
Transaction isolation and multiversion concurrency control
Summary
7. Server-Side Programming with PL/pgSQL
Introduction
SQL language and PL/pgSQL – a comparison
PostgreSQL function parameters
Function authorization-related parameters
Function planner-related parameters
Function configuration-related parameters
The PostgreSQL PL/pgSQL control statements
Declaration statements
Assignment statements
Conditional statements
Iteration
The loop statement
The while loop statement
The for loop statement
Returning from the function
Returning void
Returning a single row
Returning multiple rows
Function predefined variables
Exception handling
Dynamic SQL
Executing DDL statements in dynamic SQL
Executing DML statements in dynamic SQL
Dynamic SQL and the caching effect
Recommended practices when using dynamic SQL
Summary
8. PostgreSQL Security
Authentication in PostgreSQL
PostgreSQL pg_hba.conf
Listen addresses
Authentication best practices
PostgreSQL default access privile...
Table of contents
- Learning PostgreSQL