Home Page Icon
Home Page
Table of Contents for
Table of Contents
Close
Table of Contents
by Andrey Volkov, Achim Vannahme, Salahaldin Juba
Learning PostgreSQL
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 privileges
Role system and proxy authentication
PostgreSQL security levels
Database security level
Schema security level
Table-level security
Column-level security
Row-level security
Encrypting data
PostgreSQL role password encryption
pgcrypto
One-way encryption
Two-way encryption
Summary
9. The PostgreSQL System Catalog and System Administration Functions
The system catalog
Getting the database cluster and client tools version
Getting ready
How to do it…
There's more…
Terminating and canceling user sessions
Getting ready
How to do it…
How it works…
There's more…
Setting and getting database cluster settings
Getting ready
How to do it…
There's more…
Getting the database and database object size
Getting ready
How to do it…
There's more…
Cleaning up the database
Getting ready
How to do it…
There's more…
Cleaning up data in the database
Getting ready
How to do it…
There's more…
Managing database locks
Adding missing indexes on foreign keys and altering the default statistic
Getting ready
How to do it…
Getting the views dependency tree
Getting ready
How to do it…
There's more…
Summary
10. Optimizing Database Performance
PostgreSQL configuration tuning
Maximum number of connections
Memory settings
Hard disk settings
Planner-related settings
Benchmarking is your friend
Tuning PostgreSQL queries
The EXPLAIN command and execution plan
Detecting problems in query plans
Common mistakes in writing queries
Unnecessary operations
Misplaced indexes
Unnecessary table or index scans
Using correlated nested queries
Using CTE when not mandatory
Using the PL/pgSQL procedural language consideration
Cross column correlation
Table partitioning
Constraint exclusion limitations
Summary
11. Beyond Conventional Data types
PostgreSQL arrays
Common functions of arrays and their operators
Modifying and accessing arrays
Indexing arrays in PostgreSQL
Hash store
Modifying and accessing an hstore
Indexing an hstore in PostgreSQL
The PostgreSQL JSON data type
JSON and XML
The JSON data type
Modifying and accessing JSON types
Indexing a JSON data type
The PostgreSQL RESTful API with JSON
A PostgreSQL full text search
The tsquery and tsvector data types
The tsvector data type
The tsquery data type
Pattern matching
Full text search indexing
Summary
12. Testing
Unit testing
Unit testing in databases
Unit test frameworks
Schema difference
The interfaces test
Data difference
PostgreSQL benchmarks
Summary
13. PostgreSQL JDBC
Introduction to JDBC
Connecting to a PostgreSQL database
Installing the driver
Initializing the driver
Obtaining a connection
Error handling
SQLWarnings
Issuing a query and processing the results
Static statements
PreparedStatements
Using a ResultSet
Navigating through a ResultSet
Reading row data
Handling null values
Scrollable and updateable ResultSets
Navigating through a ResultSet
Changing the data in a ResultSet
Using cursors
Getting information about the table structure
Function handling
Calling a stored function
Getting a ResultSet from a stored function
Getting a ResultSet from a function returning SETOF
Getting a ResultSet from a function returning a refcursor
Design considerations
Summary
14. PostgreSQL and Hibernate
Introduction to ORM and Hibernate
Hibernate overview and architecture
Installation and configuration
Installation of Hibernate
Configuring Hibernate
Getting a session from the SessionFactory
Mapping classes to tables
Creating an entity class
Creating a mapping file
Using annotation-based mapping
Working with entities
States of an entity
Making a new entity persistent
Loading an entity from the database
Loading a list of entries
Named queries
Creating dynamic queries
Modifying entities
Deleting entities
Using association mapping
One-to-many and many-to-one mappings
One-to-one mapping and component mapping
Many-to-many mapping
Fetching strategies
Configuring the fetch type
Configuring the fetch mode
Tuning the performance of Hibernate
Using caching
Using connection pools
Dealing with partitioned tables
Summary
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Cover
Next
Next Chapter
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 privileges
Role system and proxy authentication
PostgreSQL security levels
Database security level
Schema security level
Table-level security
Column-level security
Row-level security
Encrypting data
PostgreSQL role password encryption
pgcrypto
One-way encryption
Two-way encryption
Summary
9. The PostgreSQL System Catalog and System Administration Functions
The system catalog
Getting the database cluster and client tools version
Getting ready
How to do it…
There's more…
Terminating and canceling user sessions
Getting ready
How to do it…
How it works…
There's more…
Setting and getting database cluster settings
Getting ready
How to do it…
There's more…
Getting the database and database object size
Getting ready
How to do it…
There's more…
Cleaning up the database
Getting ready
How to do it…
There's more…
Cleaning up data in the database
Getting ready
How to do it…
There's more…
Managing database locks
Adding missing indexes on foreign keys and altering the default statistic
Getting ready
How to do it…
Getting the views dependency tree
Getting ready
How to do it…
There's more…
Summary
10. Optimizing Database Performance
PostgreSQL configuration tuning
Maximum number of connections
Memory settings
Hard disk settings
Planner-related settings
Benchmarking is your friend
Tuning PostgreSQL queries
The EXPLAIN command and execution plan
Detecting problems in query plans
Common mistakes in writing queries
Unnecessary operations
Misplaced indexes
Unnecessary table or index scans
Using correlated nested queries
Using CTE when not mandatory
Using the PL/pgSQL procedural language consideration
Cross column correlation
Table partitioning
Constraint exclusion limitations
Summary
11. Beyond Conventional Data types
PostgreSQL arrays
Common functions of arrays and their operators
Modifying and accessing arrays
Indexing arrays in PostgreSQL
Hash store
Modifying and accessing an hstore
Indexing an hstore in PostgreSQL
The PostgreSQL JSON data type
JSON and XML
The JSON data type
Modifying and accessing JSON types
Indexing a JSON data type
The PostgreSQL RESTful API with JSON
A PostgreSQL full text search
The tsquery and tsvector data types
The tsvector data type
The tsquery data type
Pattern matching
Full text search indexing
Summary
12. Testing
Unit testing
Unit testing in databases
Unit test frameworks
Schema difference
The interfaces test
Data difference
PostgreSQL benchmarks
Summary
13. PostgreSQL JDBC
Introduction to JDBC
Connecting to a PostgreSQL database
Installing the driver
Initializing the driver
Obtaining a connection
Error handling
SQLWarnings
Issuing a query and processing the results
Static statements
PreparedStatements
Using a ResultSet
Navigating through a ResultSet
Reading row data
Handling null values
Scrollable and updateable ResultSets
Navigating through a ResultSet
Changing the data in a ResultSet
Using cursors
Getting information about the table structure
Function handling
Calling a stored function
Getting a ResultSet from a stored function
Getting a ResultSet from a function returning SETOF
Getting a ResultSet from a function returning a refcursor
Design considerations
Summary
14. PostgreSQL and Hibernate
Introduction to ORM and Hibernate
Hibernate overview and architecture
Installation and configuration
Installation of Hibernate
Configuring Hibernate
Getting a session from the SessionFactory
Mapping classes to tables
Creating an entity class
Creating a mapping file
Using annotation-based mapping
Working with entities
States of an entity
Making a new entity persistent
Loading an entity from the database
Loading a list of entries
Named queries
Creating dynamic queries
Modifying entities
Deleting entities
Using association mapping
One-to-many and many-to-one mappings
One-to-one mapping and component mapping
Many-to-many mapping
Fetching strategies
Configuring the fetch type
Configuring the fetch mode
Tuning the performance of Hibernate
Using caching
Using connection pools
Dealing with partitioned tables
Summary
Index
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset
