Index

Symbols

#> pointer symbol, Querying JSON
#>> operator, Querying JSON
$$ (dollar quoting), Dollar Quoting-DO
& (and operator), TSQueries
&& (and operator), TSQueries
&& (overlap operator), Array Containment Checks, Overlap operator, Exclusion Constraints
() (parentheses), Building Custom Data Types
+ (addition operator), Datetime Operators and Functions
- (subtraction operator), Datetime Operators and Functions, Editing JSONB data, Editing JSONB data
-> operator, Querying JSON
->> operator, Querying JSON
: (colon), Shortcuts
<-> (distance operator), Features Introduced in PostgreSQL 9.6
<@ (contained in operator), Array Containment Checks, Contains and contained in operators, Binary JSON: jsonb
= (equality operator), Array Containment Checks, Binary JSON: jsonb
? (key exists operator), Binary JSON: jsonb
?& (all of array of keys exists operator), Binary JSON: jsonb
?| (any of array of keys exists operator), Binary JSON: jsonb
@ sign, selecting attributes of elements, Querying XML Data
@> (contains operator), Array Containment Checks, Contains and contained in operators, Binary JSON: jsonb
@@ operator, Using Full Text Search
\ (backslash), Regular Expressions and Pattern Matching
\! command, Executing Shell Commands
| (or operator), TSQueries
|| (concatenation operator), String Functions, Array Slicing and Splicing, Editing JSONB data, TSVectors
|| (or operator), TSQueries
~ (similar to operator), Regular Expressions and Pattern Matching
~~ operator, Operator Classes

A

addition operator (+), Datetime Operators and Functions
Adminer tool, Adminer
adminpack extension, Editing postgresql.conf and pg_hba.conf from pgAdmin3
AFTER trigger, Triggers and Trigger Functions
aggregate functions
- about, Aggregates-Aggregates
- window functions and, Aggregates
- writing in SQL, Writing SQL Aggregate Functions-Writing SQL Aggregate Functions
- writing with PL/V8, Writing Aggregate Functions with PL/V8
aggregates
- about, Aggregates-Aggregates
- FILTER clause and, FILTER Clause for Aggregates-FILTER Clause for Aggregates
- PL/V8 and, Writing Aggregate Functions with PL/V8
- SQL and, Writing SQL Aggregate Functions-Writing SQL Aggregate Functions
- window functions, Window Functions-ORDER BY
all of array of keys exists operator (?&), Binary JSON: jsonb
ALTER DATABASE command, Using Schemas, Moving Objects Among Tablespaces, FTS Configurations
ALTER DEFAULT PRIVILEGES command, Default Privileges
ALTER FOREIGN TABLE command, Querying Other PostgreSQL Servers
ALTER ROLE command, Creating Group Roles
ALTER SEQUENCE command, Serials
ALTER SERVER command, Querying Other PostgreSQL Servers
ALTER SYSTEM command, Changing the postgresql.conf settings, Configuring the Master
ALTER SYSTEM SET command, Features Introduced in PostgreSQL 9.4
ALTER TABLE command
- adding unique keys, Unique Constraints
- dropping primary key, Sample Runs and Output
- moving tables, Moving Objects Among Tablespaces
- unlogged tables and, Features Introduced in PostgreSQL 9.5
ALTER TABLESPACE command, Features Introduced in PostgreSQL 9.4, Moving Objects Among Tablespaces, Random Page Cost and Quality of Drives
ALTER TYPE command, TYPE OF
Amazon Redshift data warehouse, Notable PostgreSQL Forks
and operator (&&), TSQueries
and operator (&), TSQueries
any of array of keys exists operator (?|), Binary JSON: jsonb
ANY operator, ANY Array Search
apt_postgresql repository, Debian, Ubuntu
archive_command configuration directive, Configuring the Master
arguments in functions, Function Basics
array function, Array Constructors
arrays
- about, Arrays
- ANY operator and, ANY Array Search
- containment checks for, Array Containment Checks
- creating, Array Constructors
- passing in, Features Introduced in PostgreSQL 9.5
- referencing elements in, Referencing Elements in an Array
- slicing and splicing, Array Slicing and Splicing
- splitting strings into, Splitting Strings into Arrays, Tables, or Substrings
- unnest function, Features Introduced in PostgreSQL 9.4
- unnesting to rows, Unnesting Arrays to Rows
- zero-indexed for JSON, Querying JSON
array_agg function, Features Introduced in PostgreSQL 9.5, Array Constructors, Outputting JSON, Composite Types in Queries
array_to_json function, Outputting JSON, Composite Types in Queries
array_upper function, Referencing Elements in an Array
asynchronous replication, Replication Jargon
at sign, selecting attributes of elements, Querying XML Data
authentication methods, The pg_hba.conf File-Authentication methods
autocommit commands, Autocommit Commands

B

B-Tree indexes, PostgreSQL Stock Indexes, Operator Classes
B-Tree-GIN indexes, PostgreSQL Stock Indexes
B-Tree-GiST indexes, PostgreSQL Stock Indexes
back-referencing, Regular Expressions and Pattern Matching
background workers, dynamic, Features Introduced in PostgreSQL 9.4
backslash (\), Regular Expressions and Pattern Matching
backup and restore
- about, Backup and Restore
- pgAdmin tool, Backup and Restore-Selective backup of database assets
- pg_basebackup tool, Backup and Restore
- pg_dump tool, Backup and Restore-Selective Backup Using pg_dump, Backup and Restore-pgScript, Database Backup Using pg_dump
- pg_dumpall tool, Backup and Restore, Systemwide Backup Using pg_dumpall, Backing up systemwide objects, Server Backup: pg_dumpall
- pg_restore tool, Selective Backup Using pg_dump, Restoring Data-Using pg_restore, Backup and Restore, Database Restore: pg_restore
- psql tool, Restoring Data
- third-party tools, Backup and Restore
Barman tool, Backup and Restore
Bartunov, Oleg, Ranking Results
basic CTEs, Basic CTEs
batch jobs, pgAgent and, Installing pgAgent
BDR (bi-directional replication), Notable PostgreSQL Forks
BEFORE trigger, Triggers and Trigger Functions
BETWEEN operator, Datetime Operators and Functions
bi-directional replication (BDR), Notable PostgreSQL Forks
bigint data type, Serials
bigserial data type, Serials
BigSQL technology, Getting Started, Windows and Desktop Linux, macOS
bitmap index scan, Multicolumn Indexes
block range indexes (BRIN), Features Introduced in PostgreSQL 9.5, PostgreSQL Stock Indexes
BRIN (block range indexes), Features Introduced in PostgreSQL 9.5, PostgreSQL Stock Indexes
btree_gin extension, Popular extensions
btree_gist extension, Popular extensions, Exclusion Constraints
btrim function, String Functions

C

caching, Caching-Caching
canonical form, Discrete Versus Continuous Ranges
CASCADE modifier, TYPE OF
cascading replication, Replication Jargon
cascading standby, Replication Jargon
CASE expression
- FILTER clause and, FILTER Clause for Aggregates, Using FILTER Instead of CASE
- usage considerations, Make Good Use of CASE
case sensitivity
- removing from character types, Textuals
- searches and, ILIKE for Case-Insensitive Search
casts, PostgreSQL Database Objects, Shorthand Casting
catalogs, PostgreSQL Database Objects
\cd command, psql Import, Accessing psql from pgAdmin3
cert authentication method, Authentication methods
char data type, Textuals
characters and strings
- about, Textuals
- dollar quoting, Dollar Quoting-DO
- pattern matching and, Regular Expressions and Pattern Matching-Regular Expressions and Pattern Matching
- regular expressions and, Regular Expressions and Pattern Matching-Regular Expressions and Pattern Matching
- removing case sensitivity from character types, Textuals
- splitting strings, Splitting Strings into Arrays, Tables, or Substrings
- string functions, String Functions
check constraints, Inserting XML Data, Inherited Tables, Check Constraints
Citus project, Notable PostgreSQL Forks
CLUSTER command, Materialized Views
CoffeeScript language, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions
colon (:), Shortcuts
COLUMNS clause, Querying XML Data
columns view, PostgreSQL Database Objects
command-line tools
- fetching output from, Copying from or to Program
- packaged, PostgreSQL Packaged Command-Line Tools-psql Noninteractive Commands
- retrieving prior commands, Retrieving Prior Commands
common table expressions (CTEs)
- about, Common Table Expressions
- basic, Basic CTEs
- recursive, Recursive CTE
- writable, Writable CTEs
composite data type
- about, Custom and Composite Data Types, Composite Types in Queries
- NULL value and, Composites and NULLs
- set-returning functions and, Basic SQL Function
- tables and, TYPE OF
concatenation operator (||), String Functions, Array Slicing and Splicing, Editing JSONB data, TSVectors
CONCURRENTLY qualifier, Materialized Views
configuration files, Configuration Files-Authentication methods, Editing postgresql.conf and pg_hba.conf from pgAdmin3
configuration variables, Replication Jargon
conflict handling, Features Introduced in PostgreSQL 9.5
\connect command, Custom Prompts
connections
- managing, Managing Connections-Check for Queries Being Blocked
- to servers, Connecting to a PostgreSQL Server
constraints
- about, Tables, Constraints, and Indexes, Constraints
- check, Inserting XML Data, Inherited Tables, Check Constraints
- exclusion, Exclusion Constraints
- foreign key, Foreign Key Constraints
- unique, Unique Constraints, Partial Indexes
contained in operator (<@), Array Containment Checks, Contains and contained in operators, Binary JSON: jsonb
contains operator (@>), Array Containment Checks, Contains and contained in operators, Binary JSON: jsonb
continuous range types, Discrete Versus Continuous Ranges
contribs (see extensions)
Coordinated Universal Time (UTC), Temporals
\copy command, Importing and Exporting Data-Copying from or to Program, Importing files
COST qualifier, Function Basics
CREATE AGGREGATE command, Aggregates, Writing Aggregate Functions with PL/V8
CREATE DATABASE command, Database Creation, Getting Started, Selective Backup Using pg_dump, Using pg_restore
CREATE EXTENSION command, PostgreSQL Database Objects
- about, Step 2: Installing into a database
- installing adminpack, Editing postgresql.conf and pg_hba.conf from pgAdmin3
- installing FDWs, Foreign Data Wrappers, Querying Other PostgreSQL Servers
- installing hunspell, FTS Configurations
- installing pgAgent, Installing pgAgent
- installing PL/V8 language family, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions
- slave servers and, Replication Jargon
CREATE FOREIGN TABLE command, Querying Other PostgreSQL Servers
CREATE GROUP command, Roles
CREATE INDEX command, Binary JSON: jsonb
CREATE MATERIALIZED VIEW command, Materialized Views
CREATE OPERATOR command, Building Operators and Functions for Custom Types
CREATE OR REPLACE VIEW command, Single Table Views
CREATE PRODCEDURAL LANGUAGE command, PostgreSQL Database Objects
CREATE PUBLICATION command, Replication Jargon, Replicating Only Some Tables or Databases with Logical Replication
CREATE ROLE command, Roles, Creating Login Roles, Getting Started, Configuring the Master
CREATE SCHEMA command, Using Schemas, Querying Other Tabular Formats with ogr_fdw
CREATE SEQUENCE command, Serials
CREATE STATISTICS command, Features Introduced in PostgreSQL 10, Table Statistics
CREATE SUBSCRIPTION command, Replication Jargon, Replicating Only Some Tables or Databases with Logical Replication
CREATE TABLE command, Serials, Inserting JSON Data, Binary JSON: jsonb, Partitioned Tables
CREATE TABLESPACE command, Creating Tablespaces
CREATE TYPE command, TYPE OF
CREATE UNIQUE INDEX command, Materialized Views
CREATE USER command, Roles, Querying Other PostgreSQL Servers
CREATEDB privilege, Database Creation
crontab command, Job Scheduling with pgAgent
\crosstab command, Crosstabs
\crosstabview command, Crosstabs
CSV format, Exporting queries as a structured file or report in pgAdmin, Querying Flat Files, Querying Other Tabular Formats with ogr_fdw
CTEs (common table expressions)
- about, Common Table Expressions
- basic, Basic CTEs
- recursive, Recursive CTE
- writable, Writable CTEs
CUBE operator, Features Introduced in PostgreSQL 9.5, GROUPING SETS, CUBE, ROLLUP
current_user global variable, Creating Group Roles
custom data types
- building, Building Custom Data Types
- building operators and functions for, Building Operators and Functions for Custom Types
- tables as, All Tables Are Custom Data Types

D

\d+ command, Retrieving Details of Database Objects
data definition language (DDL), PostgreSQL Database Objects, Replication Jargon
data types
- about, PostgreSQL Database Objects, Data Types
- ANY operator and, ANY Array Search
- arrays, Arrays-Array Containment Checks
- characters and strings, Textuals-Regular Expressions and Pattern Matching
- custom and composite, Custom and Composite Data Types-Building Operators and Functions for Custom Types
- json, JSON-Editing JSONB data
- jsonb, JSON, Binary JSON: jsonb-Editing JSONB data
- numerics, Numerics-Generate Series Function
- range types, Range Types-Contains and contained in operators
- shorthand casting, Shorthand Casting
- temporals, Temporals-Datetime Operators and Functions
- tsvector, TSVectors-TSVectors
- xml, XML-Querying XML Data
database administration
- backup and restore, Backup and Restore-Using pg_restore, Backing up an entire database-Selective backup of database assets, Database Backup Using pg_dump-Database Restore: pg_restore
- common mistakes, Verboten Practices-Don’t Try to Start PostgreSQL on a Port Already in Use
- configuration files, Configuration Files-Authentication methods
- creating assets, Creating Database Assets and Setting Privileges
- database creation, Database Creation-Using Schemas
- extensions and, Extensions-Classic extensions
- logical replication and, Replicating Only Some Tables or Databases with Logical Replication-Replicating Only Some Tables or Databases with Logical Replication
- making configurations take effect, Making Configurations Take Effect-Restarting
- managing connections, Managing Connections-Check for Queries Being Blocked
- managing disk storage, Managing Disk Storage with Tablespaces
- privileges and, Privileges, Creating Database Assets and Setting Privileges-Privilege management
- roles and, Roles-Creating Group Roles
- services and, PostgreSQL Database Objects
database drivers, Database Drivers
database objects
- retrieving details of, Retrieving Details of Database Objects
- types supported, PostgreSQL Database Objects-PostgreSQL Database Objects
date data type, Temporals
daterange data type, Temporals, Built-in Range Types
datetime operators and functions, Datetime Operators and Functions-Datetime Operators and Functions
date_part function, Datetime Operators and Functions
daylight saving time (DST), Temporals
dblink extension, Popular extensions
DDL (data definition language), PostgreSQL Database Objects, Replication Jargon
deadlock_timeout setting, Managing Connections
Debian platform, Debian, Ubuntu
DECLARE command, Writing PL/pgSQL Functions
default privileges, Default Privileges-Default Privileges
DELETE command, Restricting DELETE, UPDATE, and SELECT from Inherited Tables
DELETE USING command, DELETE USING
delimiters, psql Export, Exporting queries as a structured file or report in pgAdmin
dependencies statistic, Table Statistics
\dF command, FTS Configurations
discrete range types, Discrete Versus Continuous Ranges
distance operator <->, Features Introduced in PostgreSQL 9.6
DISTINCT ON clause, DISTINCT ON
Django web framework, Database Drivers
DO command, DO
Document Type Definition (DTD), Inserting XML Data
dollar quoting ($$), Dollar Quoting-DO
DROP FOREIGN TABLE command, Querying Flat Files
DROP MATERIALIZED VIEW command, Materialized Views
DROP PUBLICATION command, Replicating Only Some Tables or Databases with Logical Replication
DROP STATISTICS command, Table Statistics
DROP SUBSCRIPTION command, Replicating Only Some Tables or Databases with Logical Replication
DROP TABLE command, Partitioned Tables
DST (daylight saving time), Temporals
DTD (Document Type Definition), Inserting XML Data
Dunstan Andrew, Basic Functions
dynamic background workers, Features Introduced in PostgreSQL 9.4
dynamic SQL execution, Dynamic SQL Execution-Dynamic SQL Execution
dynamic_shared_memory_type network setting, Parallelized Queries

E

effective_cache_size network setting, Checking postgresql.conf settings
enable_nestloop setting, Strategy Settings
enable_seqscan setting, Strategy Settings
end-of-life (EOL) support, Why Upgrade?
EnterpriseDB, Notable PostgreSQL Forks, Windows and Desktop Linux-macOS
environment variables, Environment Variables
EOL (end-of-life) support, Why Upgrade?
equality operator (=), Array Containment Checks, Binary JSON: jsonb
eval function, Basic Functions
EXCEPT modifier, Querying Other PostgreSQL Servers
exclusion constraints, Exclusion Constraints
EXPLAIN ANALYZE command
- about, EXPLAIN, EXPLAIN Options
- graphical outputs, Graphical Outputs-Graphical Outputs
- sample runs and output, Sample Runs and Output-Sample Runs and Output
EXPLAIN command
- about, EXPLAIN
- graphical outputs, Graphical Outputs-Graphical Outputs
- optional arguments, EXPLAIN Options
- sample runs and output, Sample Runs and Output-Sample Runs and Output
EXPLAIN VERBOSE command, EXPLAIN Options
explicit casts, PostgreSQL Database Objects
exporting data
- pgAdmin and, Exporting queries as a structured file or report in pgAdmin-Exporting queries as a structured file or report in pgAdmin
- psql and, Importing and Exporting Data-psql Export
extensions
- about, PostgreSQL Database Objects, Extensions-Extensions
- classic, Classic extensions
- common, Common Extensions-Classic extensions
- creating schemas to house, PostgreSQL Database Objects, Using Schemas, Step 2: Installing into a database
- downloading, Installing Extensions
- getting information about, Extensions
- installing, Extensions-Upgrading to the new extension model
- popular, Popular extensions
- upgrading to new model, Upgrading to the new extension model

F

FDWs (foreign data wrappers)
- about, PostgreSQL Database Objects, Replication and External Data, Foreign Data Wrappers
- querying flat files, Querying Flat Files-Querying Flat Files as Jagged Arrays
- querying foreign servers, Querying Other PostgreSQL Servers-Querying Other PostgreSQL Servers
- querying nonconventional data sources, Querying Nonconventional Data Sources-Querying Nonconventional Data Sources
- querying other tabular formates, Querying Other Tabular Formats with ogr_fdw-Querying Other Tabular Formats with ogr_fdw
- version improvements, Features Introduced in PostgreSQL 10
Fedora platform, CentOS, Fedora, Red Hat, Scientific Linux
file_fdw wrapper, Foreign Data Wrappers
file_textarray_fdw wrapper, Querying Flat Files as Jagged Arrays
FILTER clause, FILTER Clause for Aggregates-FILTER Clause for Aggregates, Using FILTER Instead of CASE
filtered indexes, Unique Constraints, Partial Indexes
Fink package distribution, macOS
flat files, querying, Querying Flat Files-Querying Flat Files as Jagged Arrays
FOR ORDINALITY modifier, Querying XML Data
FOR VALUES FROM clause, Partitioned Tables
force_parallel_mode setting, What Does a Parallel Query Plan Look Like?
foreign data wrappers (FDWs)
- about, PostgreSQL Database Objects, Replication and External Data, Foreign Data Wrappers
- querying flat files, Querying Flat Files-Querying Flat Files as Jagged Arrays
- querying foreign servers, Querying Other PostgreSQL Servers-Querying Other PostgreSQL Servers
- querying nonconventional data sources, Querying Nonconventional Data Sources-Querying Nonconventional Data Sources
- querying other tabular formats, Querying Other Tabular Formats with ogr_fdw-Querying Other Tabular Formats with ogr_fdw
- version improvements, Features Introduced in PostgreSQL 10
foreign key constraints, Foreign Key Constraints
foreign servers
- creating, Querying Flat Files as Jagged Arrays
- querying, Querying Other PostgreSQL Servers-Querying Other PostgreSQL Servers
foreign tables
- about, PostgreSQL Database Objects, Foreign Data Wrappers
- creating, Querying Other PostgreSQL Servers
- inheritance and, Inherited Tables
- placing triggers in, Features Introduced in PostgreSQL 9.4
forking databases, Notable PostgreSQL Forks
FreeBSD platform, FreeBSD
FROM clause, WITH ORDINALITY
FTS (full text search)
- about, PostgreSQL Database Objects, Features Introduced in PostgreSQL 9.6, Full Text Search
- FTS configurations, FTS Configurations-FTS Configurations
- full text stripping, Full Text Stripping
- json data type support, Features Introduced in PostgreSQL 10, Full Text Support for JSON and JSONB
- jsonb data type support, Features Introduced in PostgreSQL 10, Full Text Support for JSON and JSONB
- ranking results, Ranking Results
- tsqueries, TSQueries-TSQueries
- tsvector data type, TSVectors
- usage considerations, Using Full Text Search
functional indexes, Functional Indexes
functions
- about, PostgreSQL Database Objects, Data Types, Writing Functions
- aggregate, Aggregates-Aggregates, Writing SQL Aggregate Functions-Writing SQL Aggregate Functions, Writing Aggregate Functions with PL/V8
- anatomy of, Anatomy of PostgreSQL Functions-Trusted and Untrusted Languages
- arguments in, Function Basics
- basic structure of, Function Basics-Function Basics
- building for custom data types, Building Operators and Functions for Custom Types
- cancelling, Managing Connections
- computing percentiles, Features Introduced in PostgreSQL 9.4
- datetime, Datetime Operators and Functions-Datetime Operators and Functions
- embedding within SELECT command, Managing Connections
- PL/CoffeeScript, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions-Writing Window Functions in PL/V8
- PL/LiveScript, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions-Writing Window Functions in PL/V8
- PL/pgSQL, Writing PL/pgSQL Functions-Writing Trigger Functions in PL/pgSQL
- PL/Python, Writing PL/Python Functions-Basic Python Function
- PL/V8, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions-Writing Window Functions in PL/V8
- ranking search results, Ranking Results
- set-returning, Set-Returning Functions in SELECT, WITH ORDINALITY, Basic SQL Function
- state, Aggregates
- statistical, Percentiles and Mode-Percentiles and Mode
- string, String Functions
- trigger, PostgreSQL Database Objects, Triggers and Trigger Functions-Triggers and Trigger Functions, Writing Trigger Functions in PL/pgSQL
- trusted and untrusted languages, Trusted and Untrusted Languages, Basic Python Function
- window, Window Functions-ORDER BY, Writing Window Functions in PL/V8-Writing Window Functions in PL/V8
- writing with SQL, Writing Functions with SQL-Writing SQL Aggregate Functions
fuzzystrmatch extension, Step 2: Installing into a database, Popular extensions

G

gather mode, What Does a Parallel Query Plan Look Like?
GDAL (Geospatial Data Abstraction Library), Querying Other Tabular Formats with ogr_fdw
Generalized Inverted Index (GIN), Features Introduced in PostgreSQL 9.4, PostgreSQL Stock Indexes
Generalized Search Tree (GiST) indexes, Features Introduced in PostgreSQL 9.5, Unlogged Tables, PostgreSQL Stock Indexes
generate_series function, Generate Series Function, Datetime Operators and Functions, Set-Returning Functions in SELECT, WITH ORDINALITY
geocoding, pgScript and, pgScript
geometric mean, Writing SQL Aggregate Functions-Writing SQL Aggregate Functions, Writing Aggregate Functions with PL/V8
Geospatial Data Abstraction Library (GDAL), Querying Other Tabular Formats with ogr_fdw
\gexec command, Dynamic SQL Execution
GIN (Generalized Inverted Index), Features Introduced in PostgreSQL 9.4, PostgreSQL Stock Indexes
GiST (Generalized Search Tree) indexes, Features Introduced in PostgreSQL 9.5, Unlogged Tables, PostgreSQL Stock Indexes
global variables, Creating Group Roles
Google Cloud SQL for PostgreSQL, Notable PostgreSQL Forks
Google V8 engine, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions
grand unified configuration (GUC), PostgreSQL Database Objects
GRANT command, Creating Group Roles, GRANT-Default Privileges
Grant Wizard, Privilege management
graphical explain plan, Graphical Explain-Graphical Explain
GreenPlum database, Notable PostgreSQL Forks
groonga engine, PostgreSQL Stock Indexes
GROUP BY clause, Table Statistics
group login roles, Roles
group roles
- about, Roles
- creating, Creating Group Roles-Creating Group Roles
- inheriting privileges from, Creating Group Roles
grouping sets, Features Introduced in PostgreSQL 9.5, GROUPING SETS, CUBE, ROLLUP-GROUPING SETS, CUBE, ROLLUP
GUC (grand unified configuration), PostgreSQL Database Objects

H

hadoop_fdw wrapper, Replication and External Data
hash indexes, PostgreSQL Stock Indexes
hash joins, Parallel Joins
HEADER option, psql Export
HISTSIZE environment variable, Retrieving Prior Commands
Homebrew package manager, macOS
hstore extension, Popular extensions, Composite Types in Queries
HTML format, Basic Reporting-Basic Reporting, Exporting queries as a structured file or report in pgAdmin
hunspell configuration, FTS Configurations-FTS Configurations

I

\i command, Accessing psql from pgAdmin3
ident authentication method, Authentication methods
IDENTITY qualifier, Features Introduced in PostgreSQL 10, Basic Table Creation-Basic Table Creation
idle_in_transaction_session_timeout setting, Managing Connections
ILIKE operator, Popular extensions, Full Text Search, ILIKE for Case-Insensitive Search
implicit casts, PostgreSQL Database Objects
IMPORT FOREIGN SCHEMA command, Features Introduced in PostgreSQL 9.5, Querying Other PostgreSQL Servers
importing data
- pgAdmin and, Import and Export
- psql and, Importing and Exporting Data-psql Import
index-only scan, Multicolumn Indexes
indexes
- about, Tables, Constraints, and Indexes, Indexes
- bitmap index scan, Multicolumn Indexes
- determining usefulness of, How Useful Is Your Index?-How Useful Is Your Index?
- filtered, Unique Constraints, Partial Indexes
- functional, Functional Indexes
- multicolumn, Multicolumn Indexes
- operator classes and, Operator Classes-Operator Classes
- partial, Unique Constraints, Partial Indexes
- types of, PostgreSQL Stock Indexes-PostgreSQL Stock Indexes
information_schema catalog, PostgreSQL Database Objects, Dynamic SQL Execution, Navigating pgAdmin
INHERIT modifier, Creating Group Roles
inheriting
- privileges from group roles, Creating Group Roles
- tables, PostgreSQL Database Objects, Inherited Tables, Restricting DELETE, UPDATE, and SELECT from Inherited Tables
INSERT command, Inserting JSON Data
insert conflict handling, Features Introduced in PostgreSQL 9.5
INSERT INTO clause, UPSERTs: INSERT ON CONFLICT UPDATE
INSTEAD OF triggers, Using Triggers to Update Views-Using Triggers to Update Views, Triggers and Trigger Functions, Writing Trigger Functions in PL/pgSQL
int4range data type, Built-in Range Types
int8range data type, Built-in Range Types
integer data type, Serials
interval data type, Temporals-Temporals

J

Java language, Database Drivers
JavaScript Object Notation (JSON), JSON-Editing JSONB data
job scheduling, Job Scheduling with pgAgent-Helpful pgAgent Queries
joins
- hash, Parallel Joins
- lateral, Lateral Joins-Lateral Joins
- parallel, Parallel Joins
JSON (JavaScript Object Notation), JSON-Editing JSONB data
json data type
- about, JSON
- full text support, Features Introduced in PostgreSQL 10, Full Text Support for JSON and JSONB
- inserting data, Inserting JSON Data
- outputting data, Outputting JSON
- PL/V8 and, Writing Functions
- queries and, Querying JSON
jsonb data type
- about, Features Introduced in PostgreSQL 9.4, JSON, Binary JSON: jsonb-Binary JSON: jsonb
- editing data, Editing JSONB data-Editing JSONB data
- full text support, Features Introduced in PostgreSQL 10, Full Text Support for JSON and JSONB
- PL/V8 and, Writing Functions
jsonb_array_elements function, Binary JSON: jsonb
jsonb_each function, Binary JSON: jsonb
jsonb_extract_path_text function, Binary JSON: jsonb
jsonb_insert function, JSON
jsonb_set function, Editing JSONB data
json_agg function, Outputting JSON, Composite Types in Queries
json_array_elements function, Querying JSON, Binary JSON: jsonb
json_build_array function, Features Introduced in PostgreSQL 9.4
json_build_object function, Features Introduced in PostgreSQL 9.4
json_each function, Binary JSON: jsonb
json_extract_path function, Querying JSON
json_extract_path_text function, Querying JSON, Binary JSON: jsonb
json_object function, Features Introduced in PostgreSQL 9.4
json_to_record function, Features Introduced in PostgreSQL 9.4
json_to_recordset function, Features Introduced in PostgreSQL 9.4

K

key exists operator (?), Binary JSON: jsonb

L

LAG function, ORDER BY
LANGUAGE qualifier, Function Basics
lateral joins, Lateral Joins-Lateral Joins
LATERAL keyword, Lateral Joins-Lateral Joins
LEAD function, ORDER BY
lexemes, TSVectors
LibreOffice office suite, Database Drivers
LIKE operator, Popular extensions, Full Text Search, Operator Classes, ILIKE for Case-Insensitive Search
LIMIT clause, LIMIT and OFFSET
LIMIT TO modifier, Querying Other PostgreSQL Servers
Linux platform
- archive_command directive and, Configuring the Master
- crontab command, Job Scheduling with pgAgent
- installing PostgreSQL, Windows and Desktop Linux
- psql tool and, psql Customizations
- restore_command directive and, Configuring the Slaves for Full Server Cluster Replication
- retrieving command history, Retrieving Prior Commands
listen_addresses network setting, Checking postgresql.conf settings
lists of objects, Retrieving Details of Database Objects
LiveScript language, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions
local variables, Writing PL/pgSQL Functions
lock_timeout setting, Managing Connections
logical decoding, Replication Jargon
logical replication, Replication Jargon, Replicating Only Some Tables or Databases with Logical Replication-Replicating Only Some Tables or Databases with Logical Replication
LOGIN PASSWORD clause, Creating Login Roles
login roles, Roles-Creating Login Roles
log_destination network setting, Checking postgresql.conf settings
lpad function, String Functions
ltrim function, String Functions
Lubaczewski, Hubert, Features Introduced in PostgreSQL 9.4, Copying from or to Program, Graphical Outputs

M

Mac OS X platform, macOS
MacPorts package distribution, macOS
maintenance_work_mem network setting, Checking postgresql.conf settings
master servers, Replication Jargon, Configuring the Master-Configuring the Master
materialized views, Features Introduced in PostgreSQL 9.4, Views, Materialized Views-Materialized Views
max_connections network setting, Checking postgresql.conf settings
max_parallel_workers network setting, Checking postgresql.conf settings, Parallelized Queries
max_parallel_workers_per_gather network setting, Checking postgresql.conf settings, Parallelized Queries, What Does a Parallel Query Plan Look Like?
max_worker_processes network setting, Parallelized Queries
md5 authentication method, Authentication methods
median (statistic), Percentiles and Mode
mode function, Percentiles and Mode
multicolumn indexes, Multicolumn Indexes
multirow constructor, Multirow Insert

N

named dollar quoting, Dollar Quoting-DO
named notation, Function Basics
naming considerations
- function arguments, Function Basics
- primary keys, Constraints
navigating pgAdmin tool, Navigating pgAdmin-Navigating pgAdmin
ndistinct statistic, Table Statistics
.NET Framework, Database Drivers
Netezza database, Notable PostgreSQL Forks
nextval function, Serials
Node.js framework, Database Drivers, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions
NOINHERIT modifier, Creating Group Roles
NOWAIT clause, Managing Connections
NULL value, Composites and NULLs
numeric data types, Serials
numrange data type, Built-in Range Types

O

ODBC (Open Database Connectivity), Database Drivers
OFFSET clause, LIMIT and OFFSET
ogr_all schema, Querying Other Tabular Formats with ogr_fdw
ogr_fdw extension, Querying Other Tabular Formats with ogr_fdw
ogr_fdw wrapper, Replication and External Data, Querying Other Tabular Formats with ogr_fdw-Querying Other Tabular Formats with ogr_fdw
OLAP (online analytical processing) applications, Materialized Views
ON CONFLICT DO clause, UPSERTs: INSERT ON CONFLICT UPDATE
ONLY modifier, Restricting DELETE, UPDATE, and SELECT from Inherited Tables, Writable CTEs
Open Database Connectivity (ODBC), Database Drivers
OpenSCG (company), Windows and Desktop Linux
operator classes, Operator Classes-Operator Classes
operators
- about, PostgreSQL Database Objects, Data Types
- building for custom data types, Building Operators and Functions for Custom Types
- datetime, Datetime Operators and Functions-Datetime Operators and Functions
- json data type, Querying JSON
- jsonb data type, Binary JSON: jsonb
- overriding for case sensitivity, Textuals
- range, Range Operators
- sort, Aggregates
- string, String Functions
or operator (|), TSQueries
or operator (||), TSQueries
ORDER BY clause, Materialized Views, LIMIT and OFFSET, Percentiles and Mode, ORDER BY-ORDER BY
overlap operator (&&), Array Containment Checks, Overlap operator, Exclusion Constraints
overlaps function, Datetime Operators and Functions
OVERLAPS operator (ANSI SQL), Datetime Operators and Functions

P

Paquier, Michael, Binary JSON: jsonb
PARALLEL qualifier, Function Basics
parallelized queries
- about, Features Introduced in PostgreSQL 9.6, Parallelized Queries
- feature improvements, Features Introduced in PostgreSQL 10
- parallel joins, Parallel Joins
- parallel query plans, What Does a Parallel Query Plan Look Like?-What Does a Parallel Query Plan Look Like?
- parallel scans, Parallel Scans
parentheses (), Building Custom Data Types
partial indexes, Unique Constraints, Partial Indexes
PARTITION BY clause, Features Introduced in PostgreSQL 10, Partitioned Tables, PARTITION BY
PARTITION BY RANGE modifier, Partitioned Tables
partitioned tables, Partitioned Tables-Partitioned Tables
PASSING modifier, Querying XML Data
password authentication method, Authentication methods
PATH clause, Querying XML Data
pattern matching, Regular Expressions and Pattern Matching-Regular Expressions and Pattern Matching
peer authentication method, Authentication methods
percentile_cont function, Features Introduced in PostgreSQL 9.4, Percentiles and Mode
percentile_disc function, Features Introduced in PostgreSQL 9.4, Percentiles and Mode
performance tuning (see query performance tuning)
Perl language, Database Drivers
permissions (see privileges)
pgAdmin tool
- about, pgAdmin-pgAdmin, Using pgAdmin
- accessing pqsql from, Accessing psql from pgAdmin3
- autogenerating queries from table definitions, Autogenerating Queries from Table Definitions
- backup and restore, Backup and Restore-Selective backup of database assets
- connecting to servers, Connecting to a PostgreSQL Server
- downloading, Getting Started
- editing configuration files, Editing postgresql.conf and pg_hba.conf from pgAdmin3
- exporting data and, Exporting queries as a structured file or report in pgAdmin-Exporting queries as a structured file or report in pgAdmin
- features overview, Overview of Features-Overview of Features, pgAdmin Features-Selective backup of database assets
- graphical explain, Graphical Explain-Graphical Explain
- importing data and, Import and Export
- job scheduling and, Job Scheduling with pgAgent-Helpful pgAgent Queries
- listing DDL triggers, PostgreSQL Database Objects
- navigating, Navigating pgAdmin-Navigating pgAdmin
- pgScript and, pgScript-pgScript
- privilege settings and, Privileges
- version considerations, Using pgAdmin
pgAgent tool
- about, Job Scheduling with pgAgent
- batch jobs and, Installing pgAgent
- installing, Installing pgAgent
- query examples, Helpful pgAgent Queries
- scheduling jobs, Scheduling Jobs-Scheduling Jobs
pgBackRest tool, Backup and Restore
pgc command-line tool, Windows and Desktop Linux
pgcrypto extension, Popular extensions
pgdevops package, Windows and Desktop Linux
PGHOST environment variable, Environment Variables
pglogical extension, Evolution of PostgreSQL Replication
PGPASSWORD environment variable, Backup and Restore
PGPORT environment variable, Environment Variables
pgrepuser account, Configuring the Master
pgroonga extension, PostgreSQL Stock Indexes
pgScript tool, pgScript-pgScript
pgTSQL language, Windows and Desktop Linux
PGUSER environment variable, Environment Variables
pg_available_extensions view, Step 1: Installing on the server, Upgrading to the new extension model
pg_basebackup tool, Backup and Restore, Configuring the Master
pg_buffercache extension, Caching
pg_cancel_backend function, Managing Connections
pg_catalog catalog, PostgreSQL Database Objects, Navigating pgAdmin
pg_clog folder, Don’t Delete PostgreSQL Core System Files and Binaries
pg_ctl reload command, Reloading
pg_default tablespace, Managing Disk Storage with Tablespaces
pg_dump tool
- about, Backup and Restore-Selective Backup Using pg_dump, Database Backup Using pg_dump
- pgAdmin and, Selective backup of database assets
- selective backup and, Selective backup of database assets
- unlogged tables and, Unlogged Tables
- version considerations, Backup and Restore
pg_dumpall tool
- about, Backup and Restore
- selective backup and, Backing up systemwide objects
- server backup and, Server Backup: pg_dumpall
- systemwide backup, Systemwide Backup Using pg_dumpall
pg_file_settings view, Checking postgresql.conf settings
pg_global tablespace, Managing Disk Storage with Tablespaces
pg_hba.conf file
- about, Configuration Files, The pg_hba.conf File-Authentication methods
- authentication methods, The pg_hba.conf File-Authentication methods
- editing, Editing postgresql.conf and pg_hba.conf from pgAdmin3
- replicating slaves, Configuring the Master
pg_hba_file_rules view, The pg_hba.conf File
pg_ident.conf file, Configuration Files, Authentication methods
pg_log folder, “I edited my postgresql.conf and now my server won’t start.”, “I edited my pg_hba.conf and now my server is broken.”, Don’t Delete PostgreSQL Core System Files and Binaries
pg_opclass system table, Operator Classes
pg_prewarm extension, Caching
pg_receivewal daemon, Configuring the Master
pg_receivexlog daemon, Configuring the Master
pg_restore tool
- about, Restoring Data-Using pg_restore
- database restore and, Database Restore: pg_restore
- parallel restore and, Selective Backup Using pg_dump
- version considerations, Backup and Restore
pg_settings view, Checking postgresql.conf settings
pg_stat_activity view, Managing Connections, Check for Queries Being Blocked
pg_stat_statements extension, Gathering Statistics on Statements, How Useful Is Your Index?
pg_stat_statements view, Gathering Statistics on Statements
pg_stat_statements_reset function, Gathering Statistics on Statements
pg_stat_user_indexes view, How Useful Is Your Index?
pg_stat_user_tables view, How Useful Is Your Index?
pg_terminate_backend function, Managing Connections
pg_trgm extension, Popular extensions, PostgreSQL Stock Indexes
pg_ts_config function, FTS Configurations
pg_wal folder, Don’t Delete PostgreSQL Core System Files and Binaries
pg_xact folder, Don’t Delete PostgreSQL Core System Files and Binaries
pg_xlog folder, Don’t Delete PostgreSQL Core System Files and Binaries
PHP language, Database Drivers
phpPgAdmin tool, phpPgAdmin
phraseto_tsquery function, TSQueries
PL/CoffeeScript language, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions-Writing Window Functions in PL/V8
PL/LiveScript language, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions-Writing Window Functions in PL/V8
PL/pgSQL language, Writing PL/pgSQL Functions-Writing Trigger Functions in PL/pgSQL
PL/Python language, Writing PL/Python Functions-Basic Python Function
PL/V8 language, Writing Functions, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions-Writing Window Functions in PL/V8
plainto_tsquery function, TSQueries
plpython2u extension, Writing PL/Python Functions
plpython3u extension, Writing PL/Python Functions
plpythonu extension, Writing PL/Python Functions
PLs (procedural languages), PostgreSQL Database Objects, Writing Functions, Trusted and Untrusted Languages
plv8x extension, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions
pointer symbols, Querying JSON
port network setting, Checking postgresql.conf settings
positional notation, Function Basics
postgis extension, Popular extensions
postgres service, Don’t Try to Start PostgreSQL on a Port Already in Use
postgres superuser account
- administrative privileges and, Don’t Grant Full OS Administrative Privileges to the Postgres System Account (postgres)
- creating login roles, Creating Login Roles
- mapping OS root account to, Configuration Files
- PL/Python functions and, Basic Python Function
Postgres-X2 database, Notable PostgreSQL Forks
Postgres-XL database, Notable PostgreSQL Forks
Postgres.app distribution, macOS
PostgreSQL
- additional resources, For More Information on PostgreSQL
- administration tools, Administration Tools-Adminer
- downloading, Where to Get PostgreSQL
- help resources, Where to Get Help
- installing, Windows and Desktop Linux-macOS
- reasons for not using, Why Not PostgreSQL?
- reasons for using, Why PostgreSQL?-Why PostgreSQL?
- reloading, Reloading
- restarting, Restarting
- version enhancements, What’s New in Latest Versions of PostgreSQL?-Features Introduced in PostgreSQL 9.4
postgresql-dev package, Querying Flat Files as Jagged Arrays, Querying Nonconventional Data Sources
postgresql-server-dev package, Debian, Ubuntu
postgresql.auto.conf file, The postgresql.conf File
postgresql.conf file
- about, Configuration Files, The postgresql.conf File
- changing settings, Changing the postgresql.conf settings
- checking settings, Checking postgresql.conf settings-Checking postgresql.conf settings
- editing, Editing postgresql.conf and pg_hba.conf from pgAdmin3
- global system settings and, Features Introduced in PostgreSQL 9.4
postgres_fdw wrapper
- about, Features Introduced in PostgreSQL 9.6, Querying Other PostgreSQL Servers
- installing, Foreign Data Wrappers
- options supported, Querying Other PostgreSQL Servers
- updating and, Replication and External Data
postmaster.pid file, “I edited my postgresql.conf and now my server won’t start.”
primary keys
- B-Tree and, PostgreSQL Stock Indexes
- dropping from tables, Sample Runs and Output
- inheritance and, Inherited Tables
- naming considerations, Constraints
- serial data type and, Serials, Basic Table Creation
- table constraints, Unique Constraints
privileges
- about, Privileges
- batch jobs and, Installing pgAgent
- default, Default Privileges-Default Privileges
- getting started, Getting Started
- GRANT command, GRANT
- idiosyncrasies of, Privilege Idiosyncrasies
- inheriting from group roles, Creating Group Roles
- postgres superuser account, Don’t Grant Full OS Administrative Privileges to the Postgres System Account (postgres)
- setting, Creating Database Assets and Setting Privileges-Privilege management
- types of, Types of Privileges
procedural languages (PLs), PostgreSQL Database Objects, Writing Functions, Trusted and Untrusted Languages
PROMPT1 system setting, psql Customizations
psql tool
- about, psql, psql
- accessing from pgAdmin, Accessing psql from pgAdmin3
- autocommit commands, Autocommit Commands
- basic reporting, Basic Reporting-Basic Reporting
- crosstab queries, Crosstabs
- custom prompts, Custom Prompts
- customizations, psql Customizations-Retrieving Prior Commands
- dynamic SQL execution, Dynamic SQL Execution-Dynamic SQL Execution
- environment variables and, Environment Variables
- executing shell commands, Executing Shell Commands
- exporting data, Importing and Exporting Data-psql Export
- feature enhancements, Features Introduced in PostgreSQL 9.6
- importing data, Importing and Exporting Data-psql Import
- interactive commands, Interactive versus Noninteractive psql, psql Interactive Commands-psql Interactive Commands
- lists and, Retrieving Details of Database Objects
- noninteractive commands, Interactive versus Noninteractive psql, psql Noninteractive Commands
- partitioned tables and, Partitioned Tables
- restoring data, Restoring Data
- retrieving details of database objects, Retrieving Details of Database Objects
- retrieving prior commands, Retrieving Prior Commands
- shortcuts for, Shortcuts
- timing executions, Timing Executions
- watching statements, Watching Statements
PSQLRC environment variable, Environment Variables
psqlrc.conf file, psql Customizations-Retrieving Prior Commands
PSQL_HISTORY environment variable, Environment Variables
Python language
- database drivers, Database Drivers
- writing PL/Python functions, Writing PL/Python Functions-Basic Python Function

Q

quality of drives, Random Page Cost and Quality of Drives
queries
- autogenerating from table definitions, Autogenerating Queries from Table Definitions
- checking for blocked, Check for Queries Being Blocked
- composite types in, Composite Types in Queries
- crosstab, Crosstabs
- flat files, Querying Flat Files-Querying Flat Files as Jagged Arrays
- foreign servers, Querying Other PostgreSQL Servers-Querying Other PostgreSQL Servers
- json data type and, Querying JSON
- lateral joins, Lateral Joins-Lateral Joins
- managing connections for, Managing Connections-Managing Connections
- nonconventional data sources, Querying Nonconventional Data Sources-Querying Nonconventional Data Sources
- other tabular formats, Querying Other Tabular Formats with ogr_fdw-Querying Other Tabular Formats with ogr_fdw
- parallelized, Features Introduced in PostgreSQL 10, Features Introduced in PostgreSQL 9.6, Parallelized Queries-Parallel Joins
- pgAgent and, Helpful pgAgent Queries
- tsqueries, TSQueries-TSQueries
- writing better, Writing Better Queries-Using FILTER Instead of CASE
- xml data type and, Querying XML Data-Querying XML Data
query performance tuning
- about, Query Performance Tuning
- caching and, Caching-Caching
- EXPLAIN command and, EXPLAIN-Graphical Outputs
- gathering statistics on statements, Gathering Statistics on Statements
- guiding the query planner, Guiding the Query Planner-Random Page Cost and Quality of Drives
- parallelized queries, Parallelized Queries-Parallel Joins
- writing better queries, Writing Better Queries-Using FILTER Instead of CASE
query planner
- about, Guiding the Query Planner
- index usefulness, How Useful Is Your Index?-How Useful Is Your Index?
- parallel query plans, What Does a Parallel Query Plan Look Like?-What Does a Parallel Query Plan Look Like?
- quality of drives, Random Page Cost and Quality of Drives
- random page cost and, Random Page Cost and Quality of Drives
- strategy settings, Strategy Settings
- table statistics, Table Statistics-Table Statistics
quotes, escaping in strings, Dollar Quoting-DO

R

random page cost (RPC) ratio, Random Page Cost and Quality of Drives
range constructor functions, Defining Ranges
range data types
- about, Range Types
- built-in, Built-in Range Types
- defining ranges, Defining Ranges
- defining tables with, Defining Tables with Ranges
- discrete versus continuous, Discrete Versus Continuous Ranges
- temporals and, Temporals
range operators, Range Operators
rank function, Window Functions
records (rows)
- converting to JSON objects, Outputting JSON
- partitioned tables and, Partitioned Tables
- returning affected records to users, Returning Affected Records to the User
- row numbers in returned sets, Features Introduced in PostgreSQL 9.4
- unnesting arrays to, Unnesting Arrays to Rows
recursive CTEs, Recursive CTE
Red Hat platform, CentOS, Fedora, Red Hat, Scientific Linux
REFRESH command, Views
REFRESH MATERIALIZED VIEW command, Materialized Views-Materialized Views
regexp_matches function, Regular Expressions and Pattern Matching
regexp_replace function, Regular Expressions and Pattern Matching
regular expressions, Regular Expressions and Pattern Matching-Regular Expressions and Pattern Matching
reloading PostgreSQL, Reloading
remastering process, Replication Jargon
replication
- about, Replication and External Data
- asynchronous, Replication Jargon
- cascading, Replication Jargon
- common terminology, Replication Jargon-Replication Jargon
- evolution of, Evolution of PostgreSQL Replication
- feature improvements, Features Introduced in PostgreSQL 10
- initiating process, Initiating the Streaming Replication Process
- logical, Replication Jargon, Replicating Only Some Tables or Databases with Logical Replication-Replicating Only Some Tables or Databases with Logical Replication
- setting up, Setting Up Full Server Replication-Replicating Only Some Tables or Databases with Logical Replication
- streaming, Replication Jargon, Initiating the Streaming Replication Process
- synchronous, Replication Jargon
- third-party options, Third-Party Replication Options
replication slots, Replication Jargon
reports
- export options, Exporting queries as a structured file or report in pgAdmin
- psql and, Basic Reporting-Basic Reporting
restarting PostgreSQL, Restarting
restore (see backup and restore)
restore_command configuration directive, Configuring the Slaves for Full Server Cluster Replication
RETURNING clause, Editing JSONB data, All Tables Are Custom Data Types
RETURNING predicate, Returning Affected Records to the User, Writable CTEs
RETURNS TABLE clause, Basic SQL Function
REVOKE command, GRANT
rights (see privileges)
roles
- about, Configuration Files, Roles
- backing up, Systemwide Backup Using pg_dumpall
- group, Roles-Creating Group Roles
- login, Roles-Creating Login Roles
- organizing schemas by, Using Schemas
ROLLUP operator, Features Introduced in PostgreSQL 9.5, GROUPING SETS, CUBE, ROLLUP
row-level security, Features Introduced in PostgreSQL 9.5
rows (records)
- converting to JSON objects, Outputting JSON
- partitioned tables and, Partitioned Tables
- returning affected records to users, Returning Affected Records to the User
- row numbers in returned sets, Features Introduced in PostgreSQL 9.4
- unnesting arrays to, Unnesting Arrays to Rows
ROWS FROM clause, Features Introduced in PostgreSQL 9.4
ROWS qualifier, Function Basics
row_number function, Window Functions, ORDER BY
row_to_json function, Outputting JSON
rpad function, String Functions
RPC (random page cost) ratio, Random Page Cost and Quality of Drives
rtrim function, String Functions
Ruby language, Database Drivers
rules, PostgreSQL Database Objects, Using Triggers to Update Views
RUM index method type, PostgreSQL Stock Indexes

S

scans, parallel, Parallel Scans
scheduling jobs, Job Scheduling with pgAgent-Helpful pgAgent Queries
schemas
- about, PostgreSQL Database Objects
- creating to house extensions, PostgreSQL Database Objects, Using Schemas, Step 2: Installing into a database
- index names and, Indexes
- ogr_all, Querying Other Tabular Formats with ogr_fdw
- usage considerations, Using Schemas-Using Schemas
searches
- ANY operator and, ANY Array Search
- case-insensitive, ILIKE for Case-Insensitive Search
- full text, PostgreSQL Database Objects, Features Introduced in PostgreSQL 9.6, Full Text Search-Full Text Support for JSON and JSONB
SECURITY DEFINER qualifier, Function Basics
security, row-level, Features Introduced in PostgreSQL 9.5
SELECT command
- avoiding *, Avoid SELECT *
- embedding functions within, Managing Connections
- overusing subqueries in, Overusing Subqueries in SELECT-Overusing Subqueries in SELECT
- restricting from inherited tables, Restricting DELETE, UPDATE, and SELECT from Inherited Tables
- set-returning functions in, Set-Returning Functions in SELECT
sequences
- about, PostgreSQL Database Objects
- serial data types and, Serials
serial data type, Serials, Basic Table Creation
session_user global variable, Creating Group Roles
\set command, psql Customizations, Autocommit Commands
set force_parallel_mode setting, What Does a Parallel Query Plan Look Like?
SET ROLE command, Creating Group Roles-Creating Group Roles
SET SESSION AUTHORIZATION command, Creating Group Roles-Creating Group Roles
set-returning functions, Set-Returning Functions in SELECT, WITH ORDINALITY, Basic SQL Function
sets
- grouping, Features Introduced in PostgreSQL 9.5, GROUPING SETS, CUBE, ROLLUP-GROUPING SETS, CUBE, ROLLUP
- row numbers in returned, Features Introduced in PostgreSQL 9.4
setweight function, TSVectors
shared_buffers network setting, Checking postgresql.conf settings, “I edited my postgresql.conf and now my server won’t start.”, Don’t Set shared_buffers Too High
shell commands, executing, Executing Shell Commands
shorthand casting, Shorthand Casting
SHOW ALL command, Checking postgresql.conf settings
SHOW command, Checking postgresql.conf settings
similar to operator (~), Regular Expressions and Pattern Matching
single table views, Single Table Views
SKIP LOCKED clause, Managing Connections
slave servers, Replication Jargon, Configuring the Slaves for Full Server Cluster Replication
sort operator, Aggregates
SP-GIST indexes, PostgreSQL Stock Indexes
split_part function, Splitting Strings into Arrays, Tables, or Substrings
SQL language
- about, Writing Functions with SQL
- basic functions, Basic SQL Function-Basic SQL Function
- dynamic execution, Dynamic SQL Execution-Dynamic SQL Execution
- writing aggregate functions, Writing SQL Aggregate Functions-Writing SQL Aggregate Functions
state function, Aggregates
statement_timeout setting, Managing Connections
statistics
- computing percentiles, median, mode, Percentiles and Mode-Percentiles and Mode
- gathering on statements, Gathering Statistics on Statements
- table, Table Statistics-Table Statistics
storage
- command history, Retrieving Prior Commands
- managing with tablespaces, Managing Disk Storage with Tablespaces
streaming replication, Replication Jargon, Initiating the Streaming Replication Process
STRICT qualifier, Function Basics
strings (see characters and strings)
string_agg function, Basic Reporting, String Functions, Overlap operator, DO
string_to_array function, Splitting Strings into Arrays, Tables, or Substrings, Array Constructors
strip function, Full Text Stripping
stripping, full text, Full Text Stripping
subqueries, Overusing Subqueries in SELECT-Overusing Subqueries in SELECT, Make Good Use of CASE
substring function, String Functions
substrings
- extracting, String Functions
- splitting strings into, Splitting Strings into Arrays, Tables, or Substrings
subtraction operator (#-), Editing JSONB data, Editing JSONB data
subtraction operator (-), Datetime Operators and Functions
superuser roles, Roles-Creating Group Roles
synchronous replication, Replication Jargon
synchronous_standby_name configuration variable, Replication Jargon

T

tab-delimited files, psql Export
tables
- about, PostgreSQL Database Objects, Tables, Constraints, and Indexes
- as custom data types, All Tables Are Custom Data Types
- autogenerating queries from definitions, Autogenerating Queries from Table Definitions
- automatic type creation, PostgreSQL Database Objects
- composite data type and, TYPE OF
- creating, Basic Table Creation-Basic Table Creation
- creating columns in, Serials
- creating to store json data, Inserting JSON Data
- creating using pgScript, pgScript
- defining with ranges, Defining Tables with Ranges
- dropping primary keys from, Sample Runs and Output
- foreign, PostgreSQL Database Objects, Features Introduced in PostgreSQL 9.4, Inherited Tables, Foreign Data Wrappers, Querying Other PostgreSQL Servers
- IDENTITY qualifier, Features Introduced in PostgreSQL 10
- inherited, PostgreSQL Database Objects, Inherited Tables, Restricting DELETE, UPDATE, and SELECT from Inherited Tables
- inserting data into, Binary JSON: jsonb
- lateral joins, Lateral Joins-Lateral Joins
- logical replication and, Replicating Only Some Tables or Databases with Logical Replication-Replicating Only Some Tables or Databases with Logical Replication
- moving, Moving Objects Among Tablespaces
- partitioned, Partitioned Tables-Partitioned Tables
- populating, Features Introduced in PostgreSQL 9.5
- populating with pgScript, pgScript
- querying, Querying Other Tabular Formats with ogr_fdw-Querying Other Tabular Formats with ogr_fdw
- single views, Single Table Views
- splitting strings into, Splitting Strings into Arrays, Tables, or Substrings
- statistics and, Table Statistics-Table Statistics
- types supported, Tables
- unlogged, Features Introduced in PostgreSQL 9.5, Unlogged Tables
tables view, PostgreSQL Database Objects
tablespaces
- backing up, Systemwide Backup Using pg_dumpall
- creating, Creating Tablespaces
- expedited moves between, Features Introduced in PostgreSQL 9.4
- managing disk storage with, Managing Disk Storage with Tablespaces
- moving objects among, Moving Objects Among Tablespaces
tabular explain plan, Graphical Outputs
template databases, Template Databases
temporal data types
- about, Temporals-Temporals
- adding intervals, Datetime Operators and Functions
- datetime operators and functions, Datetime Operators and Functions-Datetime Operators and Functions
- subtracting intervals, Datetime Operators and Functions
text data type, Textuals, Basic Table Creation
textuals (see characters and strings)
third-party replication options, Third-Party Replication Options
time data type, Temporals
time zones
- about, Time Zones: What They Are and Are Not-Time Zones: What They Are and Are Not
- temporals and, Temporals
timestamp data type, Temporals, Datetime Operators and Functions-Datetime Operators and Functions
timestamptz data type, Temporals, Basic Table Creation
timetz data type, Temporals
\timing command, Timing Executions
timing executions (psql), Timing Executions
TOAST (The Oversized-Attribute Storage Technique), Avoid SELECT *
to_char function, Datetime Operators and Functions
to_tsquery function, TSQueries
to_tsvector function, Features Introduced in PostgreSQL 10, TSVectors, Full Text Support for JSON and JSONB
transaction log, Replication Jargon
trigger functions
- about, PostgreSQL Database Objects, Triggers and Trigger Functions-Triggers and Trigger Functions
- writing in PL/pgSQL, Writing Trigger Functions in PL/pgSQL
triggers
- about, PostgreSQL Database Objects, Triggers and Trigger Functions-Triggers and Trigger Functions
- INSTEAD OF, Using Triggers to Update Views-Using Triggers to Update Views, Triggers and Trigger Functions, Writing Trigger Functions in PL/pgSQL
- placing on foreign tables, Features Introduced in PostgreSQL 9.4
- PLpg/SQL and, Writing Trigger Functions in PL/pgSQL
- updating views, Using Triggers to Update Views-Using Triggers to Update Views
trim function, String Functions
TRUNCATE event, Using Triggers to Update Views
trust authentication method, Authentication methods
trusted languages, Trusted and Untrusted Languages
tsearch extension, Classic extensions
tsqueries, TSQueries-TSQueries
tsrange data type, Temporals, Built-in Range Types
tstzrange data type, Temporals, Built-in Range Types
tsvector data type, TSVectors-TSVectors
tsvector_update_trigger function, TSVectors
ts_headline function, Features Introduced in PostgreSQL 10, Full Text Support for JSON and JSONB
ts_rank function, Ranking Results
ts_rank_cd function, Ranking Results
types (data) (see data types)

U

Ubuntu platform, Debian, Ubuntu
unique constraints, Unique Constraints, Partial Indexes
Unix platform
- archive_command directive and, Configuring the Master
- crontab command, Job Scheduling with pgAgent
- installing PostgreSQL, CentOS, Fedora, Red Hat, Scientific Linux
- psql tool and, psql Customizations
- restore_command directive and, Configuring the Slaves for Full Server Cluster Replication
- retrieving command history, Retrieving Prior Commands
UNLOGGED modifier, Unlogged Tables
unlogged tables, Features Introduced in PostgreSQL 9.5, Unlogged Tables
unnest function
- improved functionality, Features Introduced in PostgreSQL 9.4
- string_to_array function and, Splitting Strings into Arrays, Tables, or Substrings
- unnesting arrays into rows, Regular Expressions and Pattern Matching, Unnesting Arrays to Rows
- xpath function and, Querying XML Data
\unset command, psql Customizations
untrusted languages, Trusted and Untrusted Languages, Basic Python Function
updatable setting, Querying Other PostgreSQL Servers
UPDATE command, Template Databases, Single Table Views, Restricting DELETE, UPDATE, and SELECT from Inherited Tables
UPDATE OF clause, PostgreSQL Database Objects, Triggers and Trigger Functions
updates
- conflict handling, Features Introduced in PostgreSQL 9.5
- lock failures, Features Introduced in PostgreSQL 9.5
- protecting against in views, Features Introduced in PostgreSQL 9.4
upper function, ILIKE for Case-Insensitive Search
UPSERT construct, UPSERTs: INSERT ON CONFLICT UPDATE
UTC (Coordinated Universal Time), Temporals

V

VACUUM ANALYZE command, Table Statistics
VALID UNTIL clause, Creating Login Roles
VALUES keyword, Multirow Insert
values list, Multirow Insert
varchar data type, Textuals, Basic Table Creation
variables
- configuration, Replication Jargon
- environment, Environment Variables
- global, Creating Group Roles
- local, Writing PL/pgSQL Functions
- psql shortcuts and, Shortcuts
versions
- pgAdmin tool, Using pgAdmin
- pgAgent tool, Helpful pgAgent Queries
- pg_dump tool, Backup and Restore
- pg_restore tool, Backup and Restore
- PostgreSQL 10, Features Introduced in PostgreSQL 10
- PostgreSQL 9.4, Features Introduced in PostgreSQL 9.4-Features Introduced in PostgreSQL 9.4
- PostgreSQL 9.5, Features Introduced in PostgreSQL 9.5
- PostgreSQL 9.6, Features Introduced in PostgreSQL 9.6
- upgrade recommendations, Why Upgrade?
views, PostgreSQL Database Objects
- (see also specific views)
- about, PostgreSQL Database Objects, Views
- avoiding SELECT * within, Avoid SELECT *
- materialized, Features Introduced in PostgreSQL 9.4, Views, Materialized Views-Materialized Views
- protecting against updates in, Features Introduced in PostgreSQL 9.4
- single table, Single Table Views
- updating with triggers, Using Triggers to Update Views-Using Triggers to Update Views
views view, PostgreSQL Database Objects
VODKA index method type, PostgreSQL Stock Indexes
VOLATILITY setting, Function Basics

W

WAL (write-ahead log), Replication Jargon
\watch command, Watching Statements
WHEN trigger condition, Triggers and Trigger Functions
WHERE clause, Single Table Views
whitespace, trimiming, String Functions
window functions
- about, Window Functions
- aggregate functions and, Aggregates
- ORDER BY clause, ORDER BY-ORDER BY
- PARTITION BY clause, PARTITION BY
- writing in PL/V8, Writing Window Functions in PL/V8-Writing Window Functions in PL/V8
Windows platform
- archive_command directive and, Configuring the Master
- installing PostgreSQL, Windows and Desktop Linux
- pgAgent versions and, Helpful pgAgent Queries
- psql tool and, psql Customizations
- restore_command directive and, Configuring the Slaves for Full Server Cluster Replication
- retrieving command history, Retrieving Prior Commands
window_object helper function, Writing Window Functions in PL/V8
WITH CHECK OPTION modifier, Features Introduced in PostgreSQL 9.4, Views
WITH clause, PostgreSQL Database Objects
WITH GRANT OPTION modifier, GRANT
WITH ORDINALITY clause, Querying XML Data, WITH ORDINALITY-WITH ORDINALITY
WITHIN GROUP modifier, Features Introduced in PostgreSQL 9.4, Percentiles and Mode
work_mem network setting, Checking postgresql.conf settings
writable CTEs, Writable CTEs
write-ahead log (WAL), Replication Jargon
writing better queries
- about, Writing Better Queries
- avoiding SELECT *, Avoid SELECT *
- CASE usage considerations, Make Good Use of CASE
- FILTER usage considerations, Using FILTER Instead of CASE
- overusing subqueries in SELECT, Overusing Subqueries in SELECT-Overusing Subqueries in SELECT
writing functions
- about, Writing Functions
- anatomy of functions, Anatomy of PostgreSQL Functions-Trusted and Untrusted Languages
- in PL/CoffeeScript, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions-Writing Window Functions in PL/V8
- in PL/LiveScript, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions-Writing Window Functions in PL/V8
- in PL/pgSQL, Writing PL/pgSQL Functions-Writing Trigger Functions in PL/pgSQL
- in PL/Python, Writing PL/Python Functions-Basic Python Function
- in PL/V8, Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions-Writing Window Functions in PL/V8
- in SQL, Writing Functions with SQL-Writing SQL Aggregate Functions
www_fdw wrapper, Querying Nonconventional Data Sources

X

xlst_process function, Classic extensions
xml data type
- about, XML
- inserting data, Inserting XML Data
- querying data, Querying XML Data-Querying XML Data
xml extension, Classic extensions
XML format, Exporting queries as a structured file or report in pgAdmin
XML Schema Definition (XSD), Inserting XML Data
XMLTABLE clause, Features Introduced in PostgreSQL 10, Querying XML Data
xpath function, Querying XML Data
XSD (XML Schema Definition), Inserting XML Data

Y

Yum repository, CentOS, Fedora, Red Hat, Scientific Linux
yyyy-mm-dd format, Datetime Operators and Functions

Z

zero-indexed arrays, Querying JSON