book

Learning PostgreSQL

Name: Learning PostgreSQL
ISBN: 9781783989188

by Salahaldin Juba, Achim Vannahme, Andrey Volkov

November 2015

Beginner

464 pages

9h 46m

English

Packt Publishing

Read now

Unlock full access

Learning PostgreSQL
Table of Contents
Learning PostgreSQL
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers, and moreWhy 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 codeErrataPiracyQuestions
1. Relational Databases
Database management systemsA brief historyDatabase categoriesThe NoSQL databasesThe CAP theoremNoSQL motivationKey value databasesColumnar databasesDocument databasesGraph databasesRelational and object relational databasesACID propertiesThe SQL LanguageBasic conceptsRelationTupleAttributeConstraintDomain integrity constraintEntity integrity constraintReferential integrity constraintsSemantic constraints
Relational algebra
The SELECT and PROJECT operationsThe RENAME operationThe Set theory operationsThe CROSS JOIN (Cartesian product) operation
Data modeling
Data model perspectivesThe entity-relation modelSample applicationEntities, attributes, and keysMapping ER to RelationsUML class diagrams
Summary
2. PostgreSQL in Action
An overview of PostgreSQLPostgreSQL historyThe advantages of PostgreSQLBusiness advantages of PostgreSQLPostgreSQL user advantagesPostgreSQL applicationsSuccess storiesForksPostgreSQL architecturePostgreSQL abstract architectureThe PostgreSQL community
PostgreSQL capabilities
ReplicationSecurityExtensionNoSQL capabilitiesForeign data wrapperPerformanceVery rich SQL constructs
Installing PostgreSQL
Installing PostgreSQL on UbuntuClient installationServer installationBasic server configurationInstalling PostgreSQL on WindowsThe PostgreSQL clientsThe psql clientPsql advanced settingsPostgreSQL utility toolsBackup and replicationUtilitiesPgAdmin III
Summary
3. PostgreSQL Basic Building Blocks
Database codingDatabase naming conventionsPostgreSQL identifiersDocumentationVersion control system
PostgreSQL objects hierarchy
Template databasesUser databasesRolesTablespaceTemplate procedural languagesSettingsSetting parametersSetting a context
PostgreSQL high-level object interaction
PostgreSQL database components
SchemaSchema usagesTablePostgreSQL native data typesNumeric typesCharacter typesDate and time typesThe car web portal database
Summary
4. PostgreSQL Advanced Building Blocks
ViewsView synopsisViews categoriesMaterialized viewsUpdatable views
Indexes
Index typesPartial indexesIndexes on expressionsUnique indexesMulticolumn indexesBest practices on indexes
Functions
PostgreSQL native programming languagesCreating a function in the C languageCreating functions in the SQL languageCreating a function in the PL/pgSQL languagePostgreSQL function usagesPostgreSQL function dependencyPostgreSQL function categoriesPostgreSQL anonymous functions
PostgreSQL user-defined data types
The PostgreSQL CREATE DOMAIN commandThe PostgreSQL CREATE TYPE command
Triggers and rule systems
The PostgreSQL rule systemThe PostgreSQL trigger systemTriggers with argumentsUsing triggers to make views updatable
Summary
5. SQL Language
SQL fundamentalsSQL lexical structure
Querying the data with the SELECT statement
The structure of the SELECT querySelect-listSQL expressionsDISTINCTFROM clauseSelecting from multiple tablesSelf-joinsWHERE clauseComparison operatorsPattern matchingRow and array comparison constructsGrouping and aggregationGROUP BY clauseHAVING clauseOrdering and limiting the resultsSubqueriesSet operations – UNION, EXCEPT, and INTERSECTDealing with NULLs
Changing the data in the database
INSERT statementUPDATE statementUPDATE using sub-selectUPDATE using additional tablesDELETE statementTRUNCATE statement
Summary
6. Advanced Query Writing
Common table expressionsReusing SQL code with CTERecursive and hierarchical queriesChanging data in multiple tables at a time
Window functions
Window definitionThe WINDOW clauseUsing window functionsWindow functions with grouping and aggregation
Advanced SQL
Selecting the first recordsSet returning functionsLateral subqueriesAdvanced usage of aggregating functions
Transaction isolation and multiversion concurrency control
Summary
7. Server-Side Programming with PL/pgSQL
IntroductionSQL language and PL/pgSQL – a comparison
PostgreSQL function parameters
Function authorization-related parametersFunction planner-related parametersFunction configuration-related parameters
The PostgreSQL PL/pgSQL control statements
Declaration statementsAssignment statementsConditional statementsIterationThe loop statementThe while loop statementThe for loop statementReturning from the functionReturning voidReturning a single rowReturning multiple rowsFunction predefined variables
Exception handling
Dynamic SQL
Executing DDL statements in dynamic SQLExecuting DML statements in dynamic SQLDynamic SQL and the caching effectRecommended practices when using dynamic SQL
Summary
8. PostgreSQL Security
Authentication in PostgreSQLPostgreSQL pg_hba.confListen addressesAuthentication best practices
PostgreSQL default access privileges
Role system and proxy authentication
PostgreSQL security levels
Database security levelSchema security levelTable-level securityColumn-level securityRow-level security
Encrypting data
PostgreSQL role password encryptionpgcryptoOne-way encryptionTwo-way encryption
Summary
9. The PostgreSQL System Catalog and System Administration Functions
The system catalog
Getting the database cluster and client tools version
Getting readyHow to do it…There's more…
Terminating and canceling user sessions
Getting readyHow to do it…How it works…There's more…
Setting and getting database cluster settings
Getting readyHow to do it…There's more…
Getting the database and database object size
Getting readyHow to do it…There's more…
Cleaning up the database
Getting readyHow to do it…There's more…
Cleaning up data in the database
Getting readyHow to do it…There's more…
Managing database locks
Adding missing indexes on foreign keys and altering the default statistic
Getting readyHow to do it…
Getting the views dependency tree
Getting readyHow to do it…There's more…
Summary
10. Optimizing Database Performance
PostgreSQL configuration tuningMaximum number of connectionsMemory settingsHard disk settingsPlanner-related settingsBenchmarking is your friend
Tuning PostgreSQL queries
The EXPLAIN command and execution planDetecting problems in query plansCommon mistakes in writing queriesUnnecessary operationsMisplaced indexesUnnecessary table or index scansUsing correlated nested queriesUsing CTE when not mandatoryUsing the PL/pgSQL procedural language consideration
Cross column correlation
Table partitioning
Constraint exclusion limitations
Summary
11. Beyond Conventional Data types
PostgreSQL arraysCommon functions of arrays and their operatorsModifying and accessing arraysIndexing arrays in PostgreSQL
Hash store
Modifying and accessing an hstoreIndexing an hstore in PostgreSQL
The PostgreSQL JSON data type
JSON and XMLThe JSON data typeModifying and accessing JSON typesIndexing a JSON data typeThe PostgreSQL RESTful API with JSON
A PostgreSQL full text search
The tsquery and tsvector data typesThe tsvector data typeThe tsquery data typePattern matchingFull text search indexing
Summary
12. Testing
Unit testingUnit testing in databasesUnit 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 driverInitializing the driverObtaining a connectionError handlingSQLWarnings
Issuing a query and processing the results
Static statementsPreparedStatementsUsing a ResultSetNavigating through a ResultSetReading row dataHandling null valuesScrollable and updateable ResultSetsNavigating through a ResultSetChanging the data in a ResultSetUsing cursorsGetting information about the table structure
Function handling
Calling a stored functionGetting a ResultSet from a stored functionGetting a ResultSet from a function returning SETOFGetting a ResultSet from a function returning a refcursor
Design considerations
Summary
14. PostgreSQL and Hibernate
Introduction to ORM and HibernateHibernate overview and architecture
Installation and configuration
Installation of HibernateConfiguring HibernateGetting a session from the SessionFactoryMapping classes to tablesCreating an entity classCreating a mapping fileUsing annotation-based mapping
Working with entities
States of an entityMaking a new entity persistentLoading an entity from the databaseLoading a list of entriesNamed queriesCreating dynamic queriesModifying entitiesDeleting entitiesUsing association mappingOne-to-many and many-to-one mappingsOne-to-one mapping and component mappingMany-to-many mappingFetching strategiesConfiguring the fetch typeConfiguring the fetch modeTuning the performance of HibernateUsing cachingUsing connection poolsDealing with partitioned tables
Summary
Index

Content preview from Learning PostgreSQL

Advanced SQL

In the following sections, some other advanced SQL techniques will be introduced:

The DISTINCT ON clause, which helps finding the first records in groups
The set returning functions, which are functions that return relations
LATERAL joins, which allow subqueries to reference each other
Some special aggregating functions

Selecting the first records

Quite often it is necessary to find the first records based on some criteria. For example, let's take the car_portal database; suppose it is required to find the first advertisement for each car_id in the advertisement table.

Grouping can help in this case. It will require a subquery to implement the logic:

SELECT advertisement_id, advertisement_date, adv.car_id,
    seller_account_id
 FROM car_portal_app.advertisement ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781783989188

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Learning PostgreSQL

by Salahaldin Juba, Achim Vannahme, Andrey Volkov

Advanced SQL

Selecting the first records

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.