Making Configurations Take Effect

Some configuration changes require a PostgreSQL service restart, which closes any active connections from clients. Other changes require just a reload. New users connecting after a reload will receive the new setting. Extant users with active connections will not be affected during a reload. If you’re not sure whether a configuration change requires a reload or restart, look under the context setting associated with a configuration. If the context is postmaster, you’ll need a restart. If the context is user, a reload will suffice.

pg_ctl reload -D your_data_directory_here

service postgresql-9.5 reload

SELECT pg_reload_conf();

service postgresql-9.6 restart

pg_ctl restart -D your_data_directory_here

The postgresql.conf File

postgresql.conf controls the life-sustaining settings of the PostgreSQL server. You can override many settings at the database, role, session, and even function levels. You’ll find many details on how to finetune your server by tweaking settings in the article Tuning Your PostgreSQL Server.

Version 9.4 introduced an important change: instead of editing postgresql.conf directly, you should override settings using an additional file called postgresql.auto.conf. We further recommend that you don’t touch the postgresql.conf and place any custom settings in postgresql.auto.conf.

SELECT
    name,
    context ,
    unit ,
    setting, boot_val, reset_val 
FROM pg_settings
WHERE name IN ('listen_addresses','deadlock_timeout','shared_buffers',
    'effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY context, name;

name                 | context    | unit | setting | boot_val  | reset_val
---------------------+------------+------+-------- +-----------+----------
listen_addresses     | postmaster |      | *       | localhost | *
shared_buffers       | postmaster | 8kB  | 131584  | 1024      | 131584
deadlock_timeout     | superuser  | ms   | 1000    | 1000      | 1000
effective_cache_size | user       | 8kB  | 16384   | 16384     | 16384
maintenance_work_mem | user       | kB   | 16384   | 16384     | 16384
work_mem             | user       | kB   | 5120    | 1024      | 5120

SELECT name, sourcefile, sourceline, setting, applied
FROM pg_file_settings
WHERE name IN ('listen_addresses','deadlock_timeout','shared_buffers',
    'effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY name;

name                 | sourcefile                    | sourceline | setting | applied
---------------------+-------------------------------+------------+---------+--------
effective_cache_size | E:/data96/postgresql.auto.conf|         11 | 8GB     | t
listen_addresses     | E:/data96/postgresql.conf     |         59 | *       | t
maintenance_work_mem | E:/data96/postgresql.auto.conf|          3 | 16MB    | t
shared_buffers       | E:/data96/postgresql.conf     |        115 | 128MB   | f
shared_buffers       | E:/data96/postgresql.auto.conf|          5 | 131584  | t

ALTER SYSTEM SET work_mem = '500MB';

SELECT pg_reload_conf();

include 'filename'

The pg_hba.conf File

The pg_hba.conf file controls which IP addresses and users can connect to the database. Furthermore, it dictates the authentication protocol that the client must follow. Changes to the file require at least a reload to take effect. A typical pg_hba.conf looks like Example 2-4.

# TYPE  DATABASE USER ADDRESS         METHOD
host    all      all  127.0.0.1/32    ident 
host    all      all  ::1/128         trust 
host    all      all  192.168.54.0/24 md5 
hostssl all      all  0.0.0.0/0       md5 

# TYPE DATABASE    USER     ADDRESS      METHOD
# Allow replication connections from localhost, 
# by a user with replication privilege. 
#host  replication postgres 127.0.0.1/32 trust
#host  replication postgres ::1/128      trust

For each connection request, pg_hba.conf is checked from the top down. As soon as a rule granting access is encountered, a connection is allowed and the server reads no further in the file. As soon as a rule rejecting access is encountered, the connection is denied and the server reads no further in the file. If the end of the file is reached without any matching rules, the connection is denied. A common mistake people make is to put the rules in the wrong order. For example, if you added 0.0.0.0/0 reject before 127.0.0.1/32 trust, local users won’t be able to connect, even though a rule is in place allowing them to.

New in version 10 is the pg_hba_file_rules system view that lists all the contents of the pg_hba.conf file.

Check for Queries Being Blocked

The pg_stat_activity view has changed considerably since version 9.1 with the renaming, dropping, and addition of new columns. Starting from version 9.2, procpid was renamed to pid.

pg_stat_activity changed in PostgreSQL 9.6 to provide more detail about waiting queries. In prior versions of PostgreSQL, there was a field called waiting that could take the value true or false. true denoted a query that was being blocked waiting some resource, but the resource being waited for was never stated. In PostgreSQL 9.6, waiting was removed and replaced with wait_event_type and wait_event to provide more information about what resource a query was waiting for. Therefore, prior to PostgreSQL 9.6, use waiting = true to determine what queries are being blocked. In PostgreSQL 9.6 or higher, use wait_event IS NOT NULL.

In addition to the change in structure, PostgreSQL 9.6 will now track additional wait locks that did not get set to waiting=true in prior versions. As a result, you may find lighter lock waits being listed for queries than you saw in prior versions. For a list of different wait_event types, refer to PostgreSQL Manual: wait_event names and types.

Creating Login Roles

When you initialize the data cluster during setup, PostgreSQL creates a single login role with the name postgres. (PostgreSQL also creates a namesake database called postgres.) You can bypass the password setting by mapping an OS root user to the new role and using ident, peer, or trust for authentication. After you’ve installed PostgreSQL, before you do anything else, you should log in as postgres and create other roles. pgAdmin has a graphical section for creating user roles, but if you want to create one using SQL, execute an SQL command like the one shown in Example 2-5.

CREATE ROLE leo LOGIN PASSWORD 'king' VALID UNTIL 'infinity' CREATEDB;

Specifying VALID UNTIL is optional. If omitted, the role remains active indefinitely. CREATEDB grants database creation privilege to the new role.

To create a user with superuser privileges, follow Example 2-6. Naturally, you must be a superuser to create other superusers.

CREATE ROLE regina LOGIN PASSWORD 'queen' VALID UNTIL '2020-1-1 00:00' SUPERUSER;

Both of the previous examples create roles that can log in. To create roles that cannot log in, omit the LOGIN PASSWORD clause.

Creating Group Roles

Group roles generally cannot log in. Rather, they serve as containers for other roles. This is merely a best-practice suggestion. Nothing stops you from creating a role that can log in as well as contain other roles.

Create a group role using the following SQL:

CREATE ROLE royalty INHERIT;

Note the use of the modifier INHERIT. This means that any member of royalty will automatically inherit privileges of the royalty role, except for the superuser privilege. For security, PostgreSQL never passes down the superuser privilege. INHERIT is the default, but we recommend that you always include the modifier for clarity.

To refrain from passing privileges from the group to its members, create the role with the NOINHERIT modifier.

To add members to a group role, you would do:

GRANT royalty TO leo;
GRANT royalty TO regina;

Some privileges can’t be inherited. For example, although you can create a group role that you mark as superuser, this doesn’t make its member roles superusers. However, those users can impersonate their group role by using the SET ROLE command, thereby gaining superuser privileges for the duration of the session. For example:

Let’s give the royalty role superuser rights with the command:

ALTER ROLE royalty SUPERUSER;

Although leo is a member of the royalty group and he inherits most rights of royalty, when he logs in, he still will not have superuser rights. He can gain superuser rights by doing:

SET ROLE royalty;

His superuser rights will last only for his current session.

This feature, though peculiar, is useful if you want to prevent yourself from unintentionally doing superuser things while you are logged in.

SET ROLE is a command available to all users, but a more powerful command called SET SESSION AUTHORIZATION is available to people who log in as superusers. In order to understand the differences, we’ll first introduce two global variables that PostgreSQL has called: current_user and session_user. You can see these values when you log in by running the SQL statement:

SELECT session_user, current_user;

When you first log in, the values of these two variables are the same. SET ROLE changes the current_user, while SET SESSION AUTHORIZATION changes both the current_user and session_user variables.

Here are the salient properties of SET ROLE:

• SET ROLE does not require superuser rights.

• SET ROLE changes the current_user variable, but not the session_user variable.

• A session_user that has superuser rights can SET ROLE to any other role.

• Nonsuperusers can SET ROLE only to the role the session_user is or the roles the session_user belongs to.

• When you do SET ROLE you gain all privileges of the impersonated user except for SET SESSION_AUTHORIZATION and SET ROLE.

A more powerful command, SET SESSION AUTHORIZATION, is available as well. Key features of SET SESSION AUTHORIZATION are as follows:

• Only a user that logs in as a superuser has permission to do SET SESSION AUTHORIZATION to another role.

• The SET SESSION AUTHORIZATION privilege is in effect for the life of the session, meaning that even if you SET SESSION AUTHORIZATION to a user that is not a superuser, you still have the SET SESSION AUTHORIZATION privilege for the life of your session.

• SET SESSION AUTHORIZATION changes the values of the current_user and session_user variables to those of the user being impersonated.

• A session_user that has superuser rights can SET ROLE to any other role.

We’ll do a set of exercises that illustrate the differences between SET ROLE and SET SESSION AUTHORIZATION by first logging in as leo and then running the code in Example 2-7.

SELECT session_user, current_user;

 session_user | current_user
--------------+--------------
 leo          | leo
(1 row)

SET SESSION AUTHORIZATION regina;

ERROR:  permission denied to set session authorization

SET ROLE regina;

ERROR:  permission denied to set role "regina"

ALTER ROLE leo SUPERUSER;

ERROR:  must be superuser to alter superusers

SET ROLE royalty;
SELECT session_user, current_user;

 session_user | current_user
--------------+--------------
 leo          | royalty
(1 row)

SET ROLE regina;

ERROR:  permission denied to set role "regina"

ALTER ROLE leo SUPERUSER;
SET ROLE regina;
SELECT session_user, current_user;

 session_user | current_user
--------------+--------------
 leo          | regina
(1 row)

SET SESSION AUTHORIZATION regina;

ERROR:  permission denied to set session authorization

-- After ending session and logging back in as leo
SELECT session_user, current_user;
SET SESSION AUTHORIZATION regina;
SELECT session_user, current_user;

session_user | current_user
--------------+--------------
leo | leo
(1 row)
SET SESSION AUTHORIZATION
session_user | current_user
--------------+--------------
regina | regina
(1 row)

In Example 2-7 leo was unable to use SET SESSION AUTHORIZATION because he’s not a superuser. He was also unable to SET ROLE to regina because he is not in the regina group. However, he was able to SET ROLE royalty since he is a member of the royalty group (he’s a king consort). Even though royalty has superuser rights, he still wasn’t able to impersonate the queen, regina, because his SET ROLE abilities are still based on being the powerless leo. Since royalty is a group that has superuser rights, he was able to promote his own account leo to be a superuser. Once leo is promoted to power, he can then impersonate regina. He is now able to completely take over her session_user and current_user persona with SET SESSION AUTHORIZATION.

Template Databases

A template database is, as the name suggests, a database that serves as a skeleton for new databases. When you create a new database, PostgreSQL copies all the database settings and data from the template database to the new database.

The default PostgreSQL installation comes with two template databases: template0 and template1. If you don’t specify a template database to follow when you create a database, template1 is used.

The basic syntax to create a database modeled after a specific template is:

CREATE DATABASE my_db TEMPLATE my_template_db;

You can pick any database to serve as the template. This could come in quite handy when making replicas. You can also mark any database as a template database. Once you do, the database is no longer editable and deletable. Any role with the CREATEDB privilege can use a template database. To make any database a template, run the following SQL as a superuser:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'mydb';

If ever you need to edit or drop a template database, first set the datistemplate attribute to FALSE. Don’t forget to change the value back after you’re done with edits.

Using Schemas

Schemas organize your database into logical groups. If you have more than two dozen tables in your database, consider cubbyholing them into schemas. Objects must have unique names within a schema but need not be unique across the database. If you cram all your tables into the default public schema, you’ll run into name clashes sooner or later. It’s up to you how to organize your schemas. For example, if you are an airline, you can place all tables of planes you own and their maintenance records into a planes schema. Place all your crew and staff into an employees schema and place all passenger-related information into a passengers schema.

Another common way to organize schemas is by roles. We found this to be particularly handy with applications that serve multiple clients whose data must be kept separate.

Suppose that you started a dog beauty management business (doggie spa). You start with a table in public called dogs to track all the dogs you hope to groom. You convince your two best friends to become customers. Whimsical government privacy regulation passes, and now you have to put in iron-clad assurances that one customer cannot see dog information from another. To comply, you set up one schema per customer and create the same dogs table in each as follows:

CREATE SCHEMA customer1;
CREATE SCHEMA customer2;

You then move the dog records into the schema that corresponds with the client. The final touch is to create different login roles for each schema with the same name as the schema. Dogs are now completely isolated in their respective schemas. When customers log in to your database to make appointments, they will be able to access only information pertaining to their own dogs.

Wait, it gets better. Because we named our roles to match their respective schemas, we’re blessed with another useful technique. But we must first introduce the search_path database variable.

As we mentioned earlier, object names must be unique within a schema, but you can have same-named objects in different schemas. For example, you have the same table called dogs in all 12 of your schemas. When you execute something like SELECT * FROM dogs, how does PostgreSQL know which schema you’re referring to? The simple answer is to always prepend the schema name onto the table name with a dot, such as in SELECT * FROM customer1.dogs. Another method is to set the search_path variable to be something like customer1, public. When the query executes, the planner searches for the dogs table first in the customer1 schema. If not found, it continues to the public schema and stops there.

PostgreSQL has a little-known variable called user that retrieves the role currently logged in. SELECT user returns this name. user is just an alias for current_user, so you can use either.

Recall how we named our customers’ schemas to be the same as their login roles. We did this so that we can take advantage of the default search path set in postgresql.conf:

search_path = "$user", public;

Now, if role customer1 logs in, all queries will first look in the customer1 schema for the tables before moving to public. Most importantly, the SQL remains the same for all customers. Even if the business grows to have thousands or hundreds of thousands of dog owners, none of the SQL scripts need to change. Commonly shared tables such as common lookup tables can be put in the public schema.

Another practice that we strongly encourage is to create schemas to house extensions (“Step 2: Installing into a database”). When you install an extension, new tables, functions, data types, and plenty of other relics join your server. If they all swarm into the public schema, it gets cluttered. For example, the entire PostGIS suite of extensions will together add thousands of functions. If you’ve already created a few tables and functions of your own in the public schema, imagine how maddening it would be to scan a list of tables and functions trying to find your own among the thousands.

Before you install any extensions, create a new schema:

CREATE SCHEMA my_extensions;

Then add your new schema to the search path:

ALTER DATABASE mydb SET search_path='$user', public, my_extensions;

When you install extensions, be sure to indicate your new schema as their new home.

Types of Privileges

PostgreSQL has a few dozen privileges, some of which you may never need to worry about. The more mundane privileges are SELECT, INSERT, UPDATE, ALTER, EXECUTE, DELETE, and TRUNCATE.

Most privileges must have a context. For example, a role having an ALTER privilege is meaningless unless qualified with a database object such as ALTER privilege on tables1, SELECT privilege on table2, EXECUTE privilege on function1, and so on. Not all privileges apply to all objects: an EXECUTE privilege for a table is nonsense.

Some privileges make sense without a context. CREATEDB and CREATE ROLE are two privileges where context is irrelevant.

Getting Started

So you successfully installed PostgreSQL; you should have one superuser, whose password you know by heart. Now you should take the following steps to set up additional roles and assign privileges:

1. PostgreSQL creates one superuser and one database for you at installation, both named postgres. Log in to your server as postgres.

2. Before creating your first database, create a role that will own the database and can log in, such as:

   CREATE ROLE mydb_admin LOGIN PASSWORD 'something';

3. Create the database and set the owner:

   CREATE DATABASE mydb WITH owner = mydb_admin;

4. Now log in as the mydb_admin user and start setting up additional schemas and tables.

GRANT

The GRANT command is the primary means to assign privileges. Basic usage is:

GRANT some_privilege TO some_role;

A few things to keep in mind when it comes to GRANT:

• Obviously, you need to have the privilege you’re granting. And, you must have the GRANT privilege yourself. You can’t give away what you don’t have.

• Some privileges always remain with the owner of an object and can never be granted away. These include DROP and ALTER.

• The owner of an object retains all privileges. Granting an owner privilege in what it already owns is unnecessary. Keep in mind, though, that ownership does not drill down to child objects. For instance, if you own a database, you may not necessarily own all the schemas within it.

• When granting privileges, you can add WITH GRANT OPTION. This means that the grantee can grant her own privileges to others, passing them on:

  GRANT ALL ON ALL TABLES IN SCHEMA public TO mydb_admin WITH GRANT OPTION;

• To grant specific privileges on ALL objects of a specific type use ALL instead of the specific object name, as in:

  GRANT SELECT, REFERENCES, TRIGGER ON 
  ALL TABLES IN SCHEMA my_schema TO 
  PUBLIC;

  Note that ALL TABLES includes regular tables, foreign tables, and views.

• To grant privileges to all roles, you can use the PUBLIC alias, as in:

  GRANT USAGE ON SCHEMA my_schema TO PUBLIC;

The GRANT command is covered in detail in GRANT. We strongly recommend that you take the time to study this document before you inadvertently knock a big hole in your security wall.

Some privileges are, by default, granted to PUBLIC. These are CONNECT and CREATE TEMP TABLE for databases and EXECUTE for functions. In many cases you might consider revoking some of the defaults with the REVOKE command, as in:

REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA my_schema FROM PUBLIC;

Default Privileges

Default privileges ease privilege management by letting you set privileges before their creation.

Let’s suppose we want all users of our database to have EXECUTE and SELECT privileges access to any future tables and functions in a particular schema. We can define privileges as shown in Example 2-8. All roles of a PostgreSQL server are members of the group PUBLIC.

GRANT USAGE ON SCHEMA my_schema TO PUBLIC; 

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT SELECT, REFERENCES ON TABLES TO PUBLIC; 

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT ALL ON TABLES TO mydb_admin WITH GRANT OPTION;  

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema  
GRANT SELECT, UPDATE ON SEQUENCES TO public;

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema 
GRANT ALL ON FUNCTIONS TO mydb_admin WITH GRANT OPTION;

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema 
GRANT USAGE ON TYPES TO PUBLIC;

Allows all users that can connect to the database to also be able to use and create objects in a schema if they have rights to those objects in the schema. GRANT USAGE on a schema is the first step to granting access to objects in the schema. If a user has rights to select from a table in a schema but no USAGE on the schema, then he will not be able to query the table.

Grant read and reference rights (the ability to create foreign key constraints against columns in a table) for all future tables created in a schema to all users that have USAGE of the schema.

GRANT ALL permissions on future tables to role mydb_admin. In addition, allow members in mydb_admin to be able to grant a subset or all privileges to other users to future tables in this schema. GRANT ALL gives permission to add/update/delete/truncate rows, add triggers, and create constraints on the tables.

GRANT permissions on future sequences, functions, and types.

To read more about default privileges, see ALTER DEFAULT PRIVILEGES.

Privilege Idiosyncrasies

Before we unleash you to explore privileges on your own, we do want to point out a few quirks that may not be apparent.

Unlike in other database products, being the owner of a PostgreSQL database does not give you access to all objects in the database. Another role could conceivably create a table in your database and deny you access to it! However, the privilege to drop the entire database could never be wrestled away from you.

After granting privileges to tables and functions with a schema, don’t forget to grant usage on the schema itself.

Installing Extensions

Getting an extension into your database takes two installation steps. First, download the extension and install it onto your server. Second, install the extension into your database.

We cover both steps in this section as well as how to install on PostgreSQL versions prior to extension support.

SELECT * FROM pg_available_extensions;

CREATE EXTENSION fuzzystrmatch;

psql -p 5432 -d mydb -c "CREATE EXTENSION fuzzystrmatch;"

CREATE EXTENSION fuzzystrmatch SCHEMA my_extensions;

CREATE EXTENSION tablefunc SCHEMA contrib FROM unpackaged;

Common Extensions

Many extensions come packaged with PostgreSQL but are not installed by default. Some past extensions have gained enough traction to become part of the PostgreSQL core. If you’re upgrading from an ancient version, you may gain functionality without needing any extensions.

Selective Backup Using pg_dump

For day-to-day backup, pg_dump is more expeditious than pg_dumpall because pg_dump can selectively back up tables, schemas, and databases. pg_dump can back up to plain SQL, as well as compressed, TAR, and directory formats. Compressed, TAR, and directory format backups can take advantage of the parallel restore feature of pg_restore. Directory backups allow parallel pg_dump of a large database. Because we believe you’ll be using pg_dump as part of your daily regimen, we have included a full dump of the help in “Database Backup Using pg_dump” so you can see the myriad switches in a single glance.

The next examples demonstrate a few common backup scenarios and corresponding pg_dump options. They should work for any version of PostgreSQL.

To create a compressed, single database backup:

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb

To create a plain-text single database backup, including a -C option, which stands for CREATE DATABASE:

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

To create a compressed backup of tables whose names start with pay in any schema:

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *.pay* -f pay.backup mydb

To create a compressed backup of all objects in the hr and payroll schemas:

pg_dump -h localhost -p 5432 -U someuser -F c -b -v \
-n hr -n payroll -f hr.backup mydb

To create a compressed backup of all objects in all schemas, excluding the public schema:

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -N public \
-f all_sch_except_pub.backup mydb

To create a plain-text SQL backup of select tables, useful for porting structure and data to lower versions of PostgreSQL or non-PostgreSQL databases (plain text generates an SQL script that you can run on any system that speaks SQL):

pg_dump -h localhost -p 5432 -U someuser -F p --column-inserts \
-f select_tables.backup mydb

The directory format option was introduced in version PostgreSQL 9.1. This option backs up each table as a separate file in a folder and gets around file size limitations. This option is the only pg_dump backup format option that results in multiple files, as shown in Example 2-10. It creates a new directory and populates it with a gzipped file for each table; also included is a file listing the hierarchy. This backup command exits with an error if the directory already exists.

pg_dump -h localhost -p 5432 -U someuser -F d -f /somepath/a_directory mydb

A parallel backup option was introduced in version 9.3 using the --jobs or -j option and specifying the number of jobs. For example: --jobs=3 (-j 3) runs three backups in parallel. Parallel backup makes sense only with the directory format option, because it’s the only backup where multiple files are created. Example 2-11 demonstrates its use.

pg_dump -h localhost -p 5432 -U someuser -j 3 -Fd -f /somepath/a_directory mydb

Systemwide Backup Using pg_dumpall

Use the pg_dumpall utility to back up all databases on a server into a single plain-text file. This comprehensive backup automatically includes server globals such as tablespace definitions and roles. See “Server Backup: pg_dumpall” for a listing of available pg_dumpall command options.

It’s a good idea to back up globals on a daily basis. Although you can use pg_dumpall to back up databases as well, we prefer backing up databases individually using pg_dump or using pg_basebackup to do a PostgreSQL service-level backup. Restoring from a huge plain-text backup tries our patience. Using pg_basebackup in conjunction with streaming replication is the fastest way to recover from major server failure.

To back up all globals and tablespace definitions only, use the following:

pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only

To back up specific global settings, use the following:

pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only

Restoring Data

There are two ways to restore data in PostgreSQL from backups created with pg_dump or pg_dumpall:

• Use psql to restore plain-text backups generated with pg_dumpall or pg_dump.

• Use pg_restore to restore compressed, TAR, and directory backups created with pg_dump.

psql -U postgres -f myglobals.sql

psql -U postgres --set ON_ERROR_STOP=on -f myglobals.sql

psql -U postgres -d mydb -f select_objects.sql

CREATE DATABASE mydb;

pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup

pg_restore --dbname=postgres --create --jobs=4 --verbose mydb.backup

CREATE DATABASE mydb2;

pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

Creating Tablespaces

To create a new tablespace, specify a logical name and a physical folder and make sure that the postgres service account has full access to the physical folder. If you are on a Windows server, use the following command (note the use of Unix-style forwardslashes):

CREATE TABLESPACE secondary LOCATION 'C:/pgdata94_secondary';

For Unix-based systems, you first must create the folder or define an fstab location, then use this command:

CREATE TABLESPACE secondary LOCATION '/usr/data/pgdata94_secondary';

Moving Objects Among Tablespaces

You can shuffle database objects among different tablespaces. To move all objects in the database to your secondary tablespace, issue the following SQL command:

ALTER DATABASE mydb SET TABLESPACE secondary;

To move just one table:

ALTER TABLE mytable SET TABLESPACE secondary;

New in PostgreSQL 9.4 is the ability move a group of objects from one tablespace to another. If the role running the command is a superuser, all objects will be moved. If not, only the owned objects will be moved.

To move all objects from default tablespace to secondary use:

ALTER TABLESPACE pg_default MOVE ALL TO secondary;

During the move, your database or table will be locked.

Don’t Delete PostgreSQL Core System Files and Binaries

Perhaps this is stating the obvious, but when people run out of disk space, the first thing they do is start deleting files from the PostgreSQL data cluster folder because it’s so darn big. Part of the reason this mistake happens so frequently is that some folders sport innocuous names such as pg_log, pg_xlog, and pg_clog. Yes, there are some files you can safely delete, but unless you know precisely which ones, you could end up destroying your data.

The pg_log folder, often found in your data folder, is a folder that builds up quickly, especially if you have logging enabled. You can always purge files from this folder without harm. In fact, many people schedule jobs to remove logfiles on a regular basis.

Files in the other folders, except for pg_xlog, should never be deleted, even if they have log-sounding names. Don’t even think of touching pg_clog, the active commit log, unless you want to invite disaster.

pg_xlog stores transaction logs. Some systems we’ve seen are configured to move processed transaction logs into a subfolder called archive. You’ll often have an archive folder somewhere (not necessarily as a subfolder of pg_xlog) if you are running synchronous replication, doing continuous archiving, or just keeping logs around in case you need to revert to a different point in time. Deleting files in the root of pg_xlog will mess up the process. Deleting files in the archived folder will just prevent you from performing point-in-time recovery, or if a slave server hasn’t played back the logs, will prevent the slave from fetching them. If these scenarios don’t apply to you, it’s safe to remove files in the archive folder.

Be leery of overzealous antivirus programs, especially on Windows. We’ve seen cases in which antivirus software removed important binaries in the PostgreSQL bin folder. If PostgreSQL fails to start on a Windows system, the event viewer is the first place to look for clues as to why.

Don’t Grant Full OS Administrative Privileges to the Postgres System Account (postgres)

Many people are under the misconception that the postgres account needs to have full administrative privileges to the server. In fact, depending on your PostgreSQL version, if you give the postgres account full administrative privileges to the server, your database server might not even start.

The postgres account should always be created as a regular system user in the OS with privileges just to the data cluster and additional tablespace folders. Most installers will set up the correct permissions without you needing to worry. Don’t try to do postgres any favors by giving it more access than it needs. Granting unnecessary access leaves your system vulnerable if you fall victim to an SQL injection attack.

There are cases where you’ll need to give the postgres account write/delete/read rights to folders or executables outside of the data cluster. With scheduled jobs that execute batch files and FDWs that have foreign tables in files, this need often arises. Practice restraint and bestow only the minimum access necessary to get the job done.

Don’t Set shared_buffers Too High

Loading up your server with RAM doesn’t mean you can set the shared_buffers as high as your physical RAM. Try it and your server may crash or refuse to start. If you are running PostgreSQL on 32-bit Windows, setting it higher than 512 MB often results in instability. With 64-bit Windows, you can push the envelope higher, and can even exceed 8 GB without any issues. On some Linux systems, shared_buffers can’t be higher than the SHMMAX variable, which is usually quite low.

PostgreSQL 9.3 changed how kernel memory is used, so that many of the issues people ran into with limitations in prior versions are no longer issues. You can find more details in Kernel Resources.

Don’t Try to Start PostgreSQL on a Port Already in Use

If you try to start PostgreSQL on a port that’s already in use, you’ll see errors in your pg_log files of the form: make sure PostgreSQL is not already running. Here are the common reasons why this happens:

• You’ve already started the postgres service.

• You are trying to run PostgreSQL on a port already in use by another service.

• Your postgres service had a sudden shutdown and you have an orphan postgresql.pid file in the data folder. Delete the file and try again.

• You have an orphaned PostgreSQL process. When all else fails, kill all running PostgreSQL processes and then try starting again.

Custom Prompts

If you spend your waking hours playing with psql connecting to multiple servers and databases, customizing your prompt to display the connected server and database will enhance your situational awareness and possibly avoid disaster. Here’s a simple way to set a highly informational prompt:

\set PROMPT1 '%n@%M:%>%x %/# '

This includes whom we are logged in as (%n), the host server (%M), the port (%>), the transaction status (%x), and the database (%/). This is probably overkill, so economize as you see fit. The complete listing of prompt symbols is documented in the psql Reference Guide.

When we connect with psql to our database, our enhanced prompt looks like:

postgres@localhost:5442 postgresql_book#

Should we switch to another database using \connect postgis_book, our prompt changes to:

postgres@localhost:5442 postgis_book#

Timing Executions

You may find it instructive to have psql output the time it took for each query to execute. Use the \timing command to toggle it on and off.

When enabled, each query you run will report the duration at the end. For example, with \timing on, executing SELECT COUNT(*) FROM pg_tables; outputs:

count
--------
73
(1 row)
Time: 18.650 ms

Autocommit Commands

By default, autocommit is on, meaning any SQL command you issue that changes data will immediately commit. Each command is its own transaction and is irreversible. If you are running a large batch of precarious updates, you may want a safety net. Start by turning off autocommit: \set AUTOCOMMIT off. Now, you have the option to roll back your statements:

UPDATE census.facts SET short_name = 'This is a mistake.';

To undo the update, run:

ROLLBACK;

To make the update permanent, run:

COMMIT;

Shortcuts

You can use the \set command to create useful keyboard shortcuts. Store universally applicable shortcuts in your psqlrc file. For example, if you use EXPLAIN ANALYZE VERBOSE once every 10 minutes, create a shortcut as follows:

\set eav 'EXPLAIN ANALYZE VERBOSE'

Now, all you have to type is :eav (the colon resolves the variable):

:eav SELECT COUNT(*) FROM pg_tables;

You can even save entire queries as shortcuts as we did in Example 3-2. Use lowercase to name your shortcuts to distinguish them from system settings.

Retrieving Prior Commands

As with many command-line tools, you can use the up arrows in psql to recall commands. The HISTSIZE variable determines the number of previous commands that you can recall. For example, \set HISTSIZE 10 lets you recover the past 10 commands.

If you spent time building and testing a difficult query or performing a series of important updates, you may want to have the history of commands piped into separate files for perusal later:

\set HISTFILE ~/.psql_history - :DBNAME

Executing Shell Commands

In psql, you can call out to the OS shell with the \! command. Let’s say you’re on Windows and need a directory listing. Instead of exiting psql or opening another window, you can just type \! dir at the psql prompt.

Watching Statements

The \watch command has been in psql since PostgreSQL 9.3. Use it to repeatedly run an SQL statement at fixed intervals so you can monitor the output. For example, suppose you want to keep tabs on queries that have yet to complete. Tag the watch command to the end of the query as shown in Example 3-3.

SELECT datname, query
FROM pg_stat_activity
WHERE state = 'active' AND pid != pg_backend_pid();
\watch 10

Although \watch is primarily for monitoring query output, you can use it to execute statements at fixed intervals. In Example 3-4, we first create a table using bulk insert syntax and then log activity every five seconds after. Only the last statement that does the insert is repeated every five seconds.

SELECT * INTO log_activity
FROM pg_stat_activity; 
INSERT INTO log_activity
SELECT * FROM pg_stat_activity; \watch 5 

Create table and do first insert.

Insert every five seconds.

To kill a watch, use CTRL-X CTRL-C.

Retrieving Details of Database Objects

Various psql describe commands list database objects along with details. Example 3-5 demonstrates how to list all tables and their sizes on disk in the pg_catalog schema that begins with the letters pg_t.

\dt+ pg_catalog.pg_t*

Schema     | Name             | Type  | Owner    | Size   | Description
-----------+------------------+-------+----------+--------+------------
pg_catalog | pg_tablespace    | table | postgres | 40 kB  |
pg_catalog | pg_trigger       | table | postgres | 16 kB  |
pg_catalog | pg_ts_config     | table | postgres | 40 kB  |
pg_catalog | pg_ts_config_map | table | postgres | 48 kB  |
pg_catalog | pg_ts_dict       | table | postgres | 40 kB  |
pg_catalog | pg_ts_parser     | table | postgres | 40 kB  |
pg_catalog | pg_ts_template   | table | postgres | 40 kB  |
pg_catalog | pg_type          | table | postgres | 112 kB |

If you need further detail on a particular object, use the \d+ command as shown in Example 3-6.

\d+ pg_ts_dict

Table "pg_catalog.pg_ts_dict"
Column         | Type | Modifiers | Storage  | Stats target | Description
---------------+------+-----------+----------+--------------+------------
dictname       | name | not null  | plain    |              |
dictnamespace  | oid  | not null  | plain    |              |
dictowner      | oid  | not null  | plain    |              |
dicttemplate   | oid  | not null  | plain    |              |
dictinitoption | text |           | extended |              |
Indexes:
"pg_ts_dict_dictname_index" UNIQUE, btree (dictname, dictnamespace)
"pg_ts_dict_oid_index" UNIQUE, btree (oid)
Has OIDs: yes

Crosstabs

New in PostgreSQL 9.6 psql is the \crosstabview command, which greatly simplifies crosstab queries. This labor-saving command is available only in the psql enviroment. We’ll illustrate with an example in Example 3-7, following it with an explanation.

SELECT student, subject, AVG(score)::numeric(5,2) As avg_score
FROM test_scores
GROUP BY student, subject
ORDER BY student, subject
\crosstabview student subject avg_score

 student | algebra | calculus | chemistry | physics | scheme
---------+---------+----------+-----------+---------+--------
 alex    |   74.00 |    73.50 |     82.00 |   81.00 |
 leo     |   82.00 |    65.50 |     75.50 |   72.00 |
 regina  |   72.50 |    64.50 |     73.50 |   84.00 |  90.00
 sonia   |   76.50 |    67.50 |     84.00 |   72.00 |
(4 rows)

The \crosstabview immediately follows the query you want to cross tabulate. The \crosstabview should list three columns selected by the query, with an optional fourth column to control sorting. The cross tabulation outputs a table where the first column serves as a row header, the second column as a column header, and the last as the value that goes in each cell. You can also omit the column names from the \crosstabview command, in which case the SELECT statement must request exactly three columns used in order for the cross tabulation.

In Example 3-7, student is the row header and subject is the column header. The average score column provides the entry for each pivoted cell. Should our data contain a missing student-subject pair, the corresponding cell would be null. We specified all the columns in the \crosstabview command, but we could have omitted them because they are in our SELECT in the right order.

Dynamic SQL Execution

Suppose you wanted to construct SQL statements to run based on the output of a query. In prior versions of PostgreSQL, you would build the SQL, output it to a file, then execute the file. Alternatively you could use the DO construct, which could be unwieldy in psql for long SQL statements. Starting with PostgreSQL 9.6, you can execute generated SQL in a single step with the new \gexec command, which iterates through each cell of your query and executes the SQL therein. Iteration is first by row then by column. It’s not yet smart enough to discern whether each cell contains a legitimate SQL. gexec is also oblivious to the result of the SQL execution. Should the SQL within a particular cell throw an error, gexec merrily treads along. However, it skips over nulls. Example 3-8 creates two tables and inserts one row in each table using the \gexec command.

SELECT
    'CREATE TABLE ' || person.name || '( a integer, b integer)' As create,
    'INSERT INTO ' || person.name || ' VALUES(1,2) ' AS insert
 FROM (VALUES ('leo'),('regina')) AS person (name) \gexec

CREATE TABLE
INSERT 0 1
CREATE TABLE
INSERT 0 1

In the next example we use gexec to obtain metadata by querying information_schema.

SELECT
'SELECT ' || quote_literal(table_name) || ' AS table_name,
COUNT(*) As count FROM ' || quote_ident(table_name) AS cnt_q
FROM information_schema.tables
WHERE table_name IN ('leo','regina') \gexec

table_name | count
-----------+------
leo        | 1
(1 row)

table_name | count
-----------+------
 regina    | 1
(1 row)

psql Import

Our usual sequence in loading denormalized or unfamiliar data is to create a staging schema to accept the incoming data. We then write explorative queries to get a sense of what we have on our hands. Finally, we distribute the data into various normalized production tables and delete the staging schema.

Before bringing the data into PostgreSQL, you must first create a table to store the incoming data. The data must match the file both in the number of columns and in data types. This could be an annoying extra step for a well-formed file, but it does obviate the need for psql to guess at data types.

psql processes the entire import as a single transaction; if it encounters any errors in the data, the entire import fails. If you’re unsure about the data contained in the file, we recommend setting up the table with the most accommodating data types and then recasting them later if necessary. For example, if you can’t be sure that a column will have just numeric values, make it character varying to get the data in for inspection and then recast it later.

Example 3-10 loads data into the table we created in Example 3-1. Launch psql from the command line and run the commands in Example 3-10.

\connect postgresql_book
\cd /postgresql_book/ch03
\copy staging.factfinder_import FROM DEC_10_SF1_QTH1_with_ann.csv CSV

In Example 3-10, we launch interactive psql, connect to our database, use \cd to change the current directory to the folder containing our file, and import our data using the \copy command. Because the default delimiter is a tab, we augment our statement with CSV to tell psql that our data is comma-separated instead.

If your file has nonstandard delimiters such as pipes, indicate the delimiter as follows:

\copy sometable FROM somefile.txt DELIMITER '|';

During import, you can replace null values with something of your own choosing by adding a NULL AS, as in the following:

\copy sometable FROM somefile.txt NULL As '';

psql Export

Exporting data is even easier than importing. You can even export selected rows from a table. Use the psql \copy command to export. Example 3-11 demonstrates how to export the data we just loaded back to a tab-delimited file.

\connect postgresql_book
\copy (SELECT * FROM staging.factfinder_import  WHERE s01 ~ E'^[0-9]+' ) 
TO '/test.tab'
WITH DELIMITER E'\t' CSV HEADER

The default behavior of exporting data without qualifications is to export to a tab-delimited file. However, the tab-delimited format does not export header columns. You can use the HEADER option only with the comma-delimited format (see Example 3-12).

\connect postgresql_book
\copy staging.factfinder_import TO '/test.csv'
WITH CSV HEADER QUOTE '"' FORCE QUOTE *

FORCE QUOTE * double quotes all columns. For clarity, we specified the quoting character even though psql defaults to double quotes.

Copying from or to Program

Since PostgreSQL 9.3, psql can fetch data from the output of command-line programs such as curl, ls, and wget, and dump the data into a table. Example 3-13 imports a directory listing using a dir command.

\connect postgresql_book
CREATE TABLE dir_list (filename text);
\copy dir_list FROM PROGRAM 'dir C:\projects /b'

Hubert Lubaczewski has more examples of using \copy. Visit Depesz: Piping copy to from an external program.

Overview of Features

To whet your appetite, here’s a list of our favorite goodies in pgAdmin. More are listed in pgAdmin Features:

Server and Desktop mode

pgAdmin4 can be installed in desktop mode or as a web server WSGI application. pgAdmin3 was a desktop-only application.

Graphical explain for your queries

This awesome feature offers pictorial insight into what the query planner is thinking. While verbose text-based planner output still has its place, a graphical explain provides a more digestible bird’s-eye view.

SQL pane

pgAdmin ultimately interacts with PostgreSQL via SQL, and it’s not shy about letting you see the generated SQL. When you use the graphical interface to make changes to your database, pgAdmin automatically displays, in an SQL pane, the underlying SQL that will perform the tasks. For novices, studying the generated SQL is a superb learning opportunity. For pros, taking advantage of the generated SQL is a great timesaver.

GUI editor for configuration files such as postgresql.conf and pg_hba.conf

You no longer need to dig around for the files and use another editor. This is currently only present in pgAdmin3, and to use it, you also need to install the pgadmin extension in the database called postgres.

Data export and import

pgAdmin can easily export query results as a CSV file or other delimited format and import such files as well. pgAdmin3 can even export results as HTML, providing you with a turnkey reporting engine, albeit a bit crude.

Backup and restore wizard

Can’t remember the myriad commands and switches to perform a backup or restore using pg_restore and pg_dump? pgAdmin has a nice interface that lets you selectively back up and restore databases, schemas, single tables, and globals. You can view and copy the underlying pg_dump or pg_restore command that pgAdmin used in the Message tab.

Grant wizard

This timesaver allows you to change privileges on many database objects in one fell swoop.

pgScript engine

This is a quick-and-dirty way to run scripts that don’t have to complete as transactions. With this you can execute loops that commit on each iteration, unlike functions that require all steps to be completed before the work is committed. Unfortunately, you cannot use this engine outside of pgAdmin and it is currently only available in pgAdmin3 (not 4).

SQL Editor Autocomplete feature

To trigger the autocomplete popup use CTRL-Space. The autocomplete feature is improved in pgAdmin4.

pgAgent

We’ll devote an entire section to this cross-platform job scheduling agent. pgAdmin provides a cool interface to it.

Connecting to a PostgreSQL Server

Connecting to a PostgreSQL server with pgAdmin is straightforward. The General and Connection tabs are shown in Figure 4-1.

Figure 4-1. pgAdmin4 register server connection dialog

Navigating pgAdmin

The tree layout of pgAdmin is intuitive to follow but does engender some possible anxiety, because it starts off by showing you every esoteric object found in the database. You can pare down the tree display by going into the Browser section of Preferences and deselecting objects that you would rather not have to stare at every time you use pgAdmin. To declutter the browse tree sections, go to Files→Preferences→Browser→Nodes. You will see the screen shown in Figure 4-2.

Figure 4-2. Hide or unhide database objects in the pgAdmin4 browse tree

If you select Show System Objects in the Display section, you’ll see the guts of your server: internal functions, system tables, hidden columns in tables, and so forth. You will also see the metadata stored in the PostgreSQL system catalogs: information_schema catalog and the pg_catalog. information_schema is an ANSI SQL standard catalog found in other databases such as MySQL and SQL Server. You may recognize some of the tables and columns from working with other database products.

Autogenerating Queries from Table Definitions

pgAdmin has this menu option that will autogenerate a template for SELECT, INSERT, and UPDATE statements from a table definition. You access this feature by right-clicking the table and accessing the SCRIPTS context menu option as shown in Figure 4-3.

Figure 4-3. Table Scripts menu

The “SELECT Script” option is particularly handy because it will create a query that lists all the columns in the table. If you have a lot of columns in a table and want to select a large subset but not all columns, this is a great timesaver. You can remove columns you don’t need in your query from the autogenerated statement.

Accessing psql from pgAdmin3

Although pgAdmin is a great tool, psql does a better job in a few cases. One of them is the execution of very large SQL files, such as those created by pg_dump and other dump tools. You can easily jump to psql from pgAdmin3, but this feature is not available in pgAdmin4. Click the plugin menu, as shown in Figure 4-4, and then click PSQL Console. This opens a psql session connected to the database you are currently connected to in pgAdmin. You can then use the \cd and \i commands to change directory and run the SQL file.

Figure 4-4. psql plugin

Because this feature relies on a database connection, you’ll see it disabled until you’re connected to a database.

Editing postgresql.conf and pg_hba.conf from pgAdmin3

You can edit configuration files directly from pgAdmin, provided that you installed the adminpack extension on your server. PostgreSQL one-click installers generally create the adminpack extension. If it’s present, you should see the Server Configuration menu enabled, as shown in Figure 4-5.

Figure 4-5. PgAdmin3 configuration file editor

If the menu is grayed out and you are connected to a PostgreSQL server, either you don’t have the adminpack installed on that server or you are not logged in as a superuser. To install the adminpack run the SQL statement CREATE EXTENSION adminpack; or use the graphical interface for installing extensions, as shown in Figure 4-6. Disconnect from the server and reconnect; you should see the menu enabled.

Figure 4-6. Installing extensions using pgAdmin4

Creating Database Assets and Setting Privileges

pgAdmin lets you create all kinds of database assets and assign privileges.

Creating databases and other database assets

Creating a new database in pgAdmin is easy. Just right-click the database section of the tree and choose New Database, as shown in Figure 4-7. The Definition tab provides a drop-down menu for you to select a template database, similar to what we did in “Template Databases”.

Figure 4-7. Creating a new database in pgAdmin4

Follow the same steps to create roles, schemas, and other objects. Each will have its own relevant set of tabs for you to specify additional attributes.

Privilege management

To manage the privileges of database assets, nothing beats the pgAdmin Grant Wizard, which you access from the Tools→Grant Wizard menu of pgAdmin. If you are interested in granting permissions only for objects in a specific schema, right-click the schema and choose “Grant Wizard.” The list will be filtered to just objects in the schema. As with many other features, this option is grayed out unless you are connected to a database. It’s also sensitive to the location in the tree you are on. For example, to set privileges for items in the census schema, select the schema and then choose Grant Wizard. The Grant Wizard screen is shown in Figure 4-8. You can then select all or some of the items and switch to the Privileges tab to set the roles and privileges you want to grant.

Figure 4-8. Grant Wizard in pgAdmin4

More often than setting privileges on existing objects, you may want to set default privileges for new objects in a schema or database. To do so, right-click the schema or database, select Properties, and then go to the Default Privileges tab, as shown in Figure 4-9.

Figure 4-9. Granting default privileges in pgAdmin4

When setting privileges for a schema, make sure to also set the usage privilege on the schema to the groups you will be giving access to.

Import and Export

Like psql, pgAdmin allows you to import and export text files.

Importing files

The import/export feature is really a wrapper around the psql \copy command and requires the table that will receive the data to exist already. In order to import data, right-click the table you want to import/export data to. Figure 4-10 shows the menu that comes up after we right-click the lu_fact_types table on the left.

Figure 4-10. Import menu in pgAdmin4

Exporting queries as a structured file or report in pgAdmin

In addition to importing data, you can export your queries as well. pgAdmin3 allows exporting to delimited CSV, HTML, or XML formats. The pgAdmin4 export feature is much simpler and basic than pgAdmin3.

In pgAdmin to export with delimiters, perform the following:

1. Open the query window ().

2. Write the query.

3. Run the query.

4. In pgAdmin3, you’d choose File→Export. In pgAdmin4, you click the download icon () and browse to where you want to save.

5. For pgAdmin3, you get additional prompts before being given a save option. Fill out the settings as shown in Figure 4-11.

Figure 4-11. Export menu

Exporting as HTML or XML is much the same, except you use the File→Quick Report option (see Figure 4-12).

Figure 4-12. Export report options

Backup and Restore

pgAdmin offers a graphical interface to pg_dump and pg_restore, covered in “Backup and Restore”. In this section, we’ll repeat some of the same examples using pgAdmin instead of the command line.

If several versions of PostgreSQL or pgAdmin are installed on your computer, it’s a good idea to make sure that the pgAdmin version is using the versions of the utilities that you expect. Check what the bin setting in pgAdmin is pointing to in order to ensure it’s the latest available, as shown in Figure 4-13.

Figure 4-13. pgAdmin File→Preferences

Backing up an entire database

In “Selective Backup Using pg_dump”, we demonstrated how to back up a database. To repeat the same steps using the pgAdmin interface, right-click the database you want to back up and choose Custom for Format, as shown in Figure 4-14.

Figure 4-14. Backup database

Selective backup of database assets

pgAdmin provides a graphical interface to pg_dump for selective backup. Right-click the asset you want to back up and select Backup (see Figure 4-15). You can back up an entire database, a particular schema, a table, or anything else.

Figure 4-15. pgAdmin schema backup

To back up the selected asset, you can forgo the other tabs (see Figure 4-14). In pgAdmin3, you can selectively drill down to more items by clicking the Objects tab, as shown in Figure 4-16. This feature is not yet present in pgAdmin4.

Figure 4-16. pgAdmin3 selective backup Objects tab

Tip

Behind the scenes, pgAdmin simply runs pg_dump to perform backups. If you ever want to know the actual commands pgAdmin is using, say for scripting, look at the Messages tab after you click the Backup button. You’ll see the exact call with arguments to pg_dump.

Installing pgAgent

You can download pgAgent from pgAgent Download. It is also available via the EDB Application Stackbuilder and BigSQL package. The packaged extension script creates a new schema named pgAgent in the postgres database. When you connect to your server via pgAdmin, you will see a new section called Jobs, as shown in Figure 4-18.

Figure 4-18. pgAdmin4 with pgAgent installed

If you want pgAgent to run batch jobs on additional servers, follow the same steps, except that you don’t have to reinstall the SQL script packaged with pgAgent. Pay particular attention to the OS permission settings of the pgAgent service/daemon account. Make sure each agent has sufficient privileges to execute the batch jobs that you will be scheduling.

Scheduling Jobs

Each scheduled job has two parts: the execution steps and the schedule. When creating a new job, start by adding one or more job steps. Figure 4-19 shows what the step add/edit screen looks like.

Figure 4-19. pgAdmin4 step edit screen

For each step, you can enter an SQL statement to run, point to a shell script on the OS, or even cut and paste in a full shell script as we commonly do.

If you choose SQL, the connection type option becomes enabled and defaults to local. With a local connection, the job step runs on the same server as the pgAgent and uses the same authentication username and password. You need to additionally specify the database that pgAgent should connect to in order to run the jobs. The screen offers you a drop-down list of databases to choose from. If you choose a remote connection type, the text box for entering a connection string becomes enabled. Type in the full connection string, including credentials and the database. When you connect to a remote PostgreSQL server with an earlier version of PostgreSQL, make sure that all the SQL constructs you use are supported on that version.

If you choose to run batch jobs, the syntax must be specific to the OS running the job. For example, if your pgAgent is running on Windows, your batch jobs should have valid DOS commands. If you are on Linux, your batch jobs should have valid shell or Bash commands.

Steps run in alphabetical order, and you can decide what kinds of actions you want to take upon success or failure of each step. You have the option of disabling steps that should remain dormant but that you don’t want to delete because you might reactivate them later.

Once you have the steps ready, go ahead and set up a schedule to run them. You can set up intricate schedules with the scheduling screen. You can even set up multiple schedules.

If you installed pgAgent on multiple servers and have them all pointing to the same pgAgent database, all these agents by default will execute all jobs.

If you want to run the job on just one specific machine, fill in the host agent field when creating the job. Agents running on other servers will skip the job if it doesn’t match their hostname.

A fully formed job is shown in Figure 4-20.

Figure 4-20. pgAgent jobs in pgAdmin

Helpful pgAgent Queries

With your finely honed SQL skills, you can easily replicate jobs, delete jobs, and edit jobs directly by messing with pgAgent metatables. Just be careful! For example, to get a glimpse inside the tables controlling all of your agents and jobs, connect to the postgres database and execute the query in Example 4-3.

SELECT c.relname As table_name, d.description
FROM
    pg_class As c INNER JOIN
    pg_namespace n ON n.oid = c.relnamespace INNER JOIN
    pg_description As d ON d.objoid = c.oid AND d.objsubid = 0
WHERE n.nspname = 'pgagent'
ORDER BY c.relname;

table_name     |       description
---------------+-------------------------
pga_job        | Job main entry
pga_jobagent   | Active job agents
pga_jobclass   | Job classification
pga_joblog     | Job run logs.
pga_jobstep    | Job step to be executed
pga_jobsteplog | Job step run logs.
pga_schedule   | Job schedule exceptions

Although pgAdmin already provides an intuitive interface to pgAgent scheduling and logging, you may find the need to generate your own job reports. This is especially true if you have many jobs or you want to compile stats from your job results. Example 4-4 demonstrates the one query we use often.

SELECT j.jobname, s.jstname, l.jslstart,l.jslduration, l.jsloutput
FROM
    pgagent.pga_jobsteplog As l INNER JOIN
    pgagent.pga_jobstep As s ON s.jstid = l.jsljstid INNER JOIN
    pgagent.pga_job As j ON j.jobid = s.jstjobid
WHERE jslstart > CURRENT_DATE
ORDER BY j.jobname, s.jstname, l.jslstart DESC;

We find this query essential for monitoring batch jobs because sometimes a job will report success even though it failed. pgAgent can’t always discern the success or failure of a shell script on the OS. The jsloutput field in the logs provides the shell output, which usually details what went wrong.

Serials

Serial and its bigger sibling, bigserial, are auto-incrementing integers often used as primary keys of tables in which a natural key is not apparent. This data type goes by different names in different database products, with autonumber being the most common alternative moniker. When you create a table and specify a column as serial, PostgreSQL first creates an integer column and then creates a sequence object named table_name_column_name_seq located in the same schema as the table. It then sets the default of the new integer column to read its value from the sequence. If you drop the column, PostgreSQL also drops the companion sequence object.

In PostgreSQL, the sequence type is a database asset in its own right. You can inspect and edit the sequences using SQL with the ALTER SEQUENCE command or using PGAdmin. You can set the current value, boundary values (both the upper and lower bounds), and even how many numbers to increment each time. Though decrementing is rare, you can do it by setting the increment value to a negative number. Because sequences are independent database assets, you can create them separately from a table using the CREATE SEQUENCE command, and you can use the same sequence across multiple tables. The cross-table sharing of the same sequence comes in handy when you’re assigning a universal key in your database.

To use an extant sequence for subsequent tables, create a new column in the table as integer or bigint—not as serial—then set the default value of the column using the nextval(sequence_name) function as shown in Example 5-1.

CREATE SEQUENCE s START 1;
CREATE TABLE stuff(id bigint DEFAULT nextval('s') PRIMARY KEY, name text);

Generate Series Function

PostgreSQL has a nifty function called generate_series not found in other database products. The function comes in two forms. One is a numeric version that creates a sequence of integers incremented by some value and one that creates a sequence of dates or timestamps incremented by some time interval. What makes generate_series so convenient is that it allows you to effectively mimic a for loop in SQL. Example 5-2 demonstrates the numeric version. Example 5-13 demonstrates the temporal version.

Example 5-2 uses integers with an optional step parameter.

SELECT x FROM generate_series(1,51,13) As x;

x
----
1
14
27
40

The default step is 1. As demonstrated in Example 5-2, you can pass in an optional step argument to specify how many steps to skip for each successive element. The end value will never exceed our prescribed range, so although our range ends at 51, our last number is 40 because adding another 13 to our 40 busts the upper bound.

String Functions

Common string manipulations are padding (lpad, rpad), trimming whitespace (rtrim, ltrim, trim, btrim), extracting substrings (substring), and concatenating (||). Example 5-3 demonstrates padding, and Example 5-4 demonstrates trimming.

SELECT
    lpad('ab', 4, '0') As ab_lpad,
    rpad('ab', 4, '0') As ab_rpad,
    lpad('abcde', 4, '0') As ab_lpad_trunc; 

ab_lpad | ab_rpad | ab_lpad_trunc
--------+---------+---------------
00ab    | ab00    | abcd

lpad truncates instead of padding if the string is too long.

By default, trim functions remove spaces, but you can pass in an optional argument indicating other characters to trim.

SELECT
    a As a_before, trim(a) As a_trim, rtrim(a) As a_rt,
    i As i_before, ltrim(i, '0') As i_lt_0,
    rtrim(i, '0') As i_rt_0, trim(i, '0') As i_t_0
FROM (
	SELECT repeat(' ', 4) || i || repeat(' ', 4) As a, '0' || i As i
	FROM generate_series(0, 200, 50) As i
) As x;

a_before | a_trim | a_rt | i_before | i_lt_0 | i_rt_0 | i_t_0
---------+--------+------+----------+--------+--------+------
0        | 0      | 0    | 00       |        |        |
50       | 50     | 50   | 050      | 50     | 05     | 5
100      | 100    | 100  | 0100     | 100    | 01     | 1
150      | 150    | 150  | 0150     | 150    | 015    | 15
200      | 200    | 200  | 0200     | 200    | 02     | 2

A helpful function for aggregating strings is the string_agg function, which we demonstrate in Examples 3-14 and 5-26.

Splitting Strings into Arrays, Tables, or Substrings

There are a couple of useful functions in PostgreSQL for tearing strings apart.

The split_part function is useful for extracting an element from a delimited string, as shown in Example 5-5. Here, we select the second item in a string of items delimited by periods.

SELECT split_part('abc.123.z45','.',2) As x;

x
---
123

The string_to_array function is useful for creating an array of elements from a delimited string. By combining string_to_array with the unnest function, you can expand the returned array into a set of rows, as shown in Example 5-6.

SELECT unnest(string_to_array('abc.123.z45', '.')) As x;

x
---
abc
123
z45

Regular Expressions and Pattern Matching

PostgreSQL’s regular expression support is downright fantastic. You can return matches as tables or arrays and choreograph replaces and updates. Back-referencing and other fairly advanced search patterns are also supported. In this section, we’ll provide a small sampling. For more information, see Pattern Matching and String Functions.

Example 5-7 shows you how to format phone numbers stored simply as contiguous digits.

SELECT regexp_replace(
'6197306254',
'([0-9]{3})([0-9]{3})([0-9]{4})',
 E'\(\\1\) \\2-\\3'
 ) As x;

x
--------------
(619) 730-6254

The \\1, \\2, etc., refer to elements in our pattern expression. We use a backslash (\) to escape the parentheses. The E' construct is PostgreSQL syntax for denoting that the string to follow should be taken literally.

Suppose some field contains text with embedded phone numbers; Example 5-8 shows how to extract the phone numbers and turn them into rows all in one step.

SELECT unnest(regexp_matches(
'Cell (619) 852-5083. Work (619)123-4567 , Casa 619-730-6254. Bésame mucho.',
E'[(]{0,1}[0-9]{3}[)-.]{0,1}[\\s]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}', 'g')
) As x;

x
--------------
(619) 852-5083
(619)123-4567
619-730-6254
(3 rows)

The matching rules for Example 5-8 are:

• [(]{0,1}: starts with zero or one open parenthesis.

• [0-9]{3}: followed by three digits.

• [)-.]{0,1}: followed by zero or one closed parenthesis, hyphen, or period.

• [\\s]+: followed by zero or more spaces.

• [0-9]{4}: followed by four digits.

• regexp_matches returns a string array consisting of matches of a regular expression. The last input to our function is the flags parameter. We set this to g, which stands for global and returns all matches of a regular expression as separate elements. If you leave out this flags parameter, then your array will only contain the first match. The flags parameter can consist of more than one flag. For example, if you have letters in your regular expression and text and you want to make the check case insensitive and global, you would use two flags, gi. In addition to the global flag, other allowed flags are listed in POSIX EMBEDDED OPTIONS.

• unnest explodes an array into a row set.

If you only care about the first match, you can utilize the substring function, which will return the first matching value as shown in Example 5-9.

SELECT substring(
'Cell (619) 852-5083. Work (619)123-4567 , Casa 619-730-6254. Bésame mucho.'
from E'[(]{0,1}[0-9]{3}[)-.]{0,1}[\\s]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}')
 As x;

       x
----------------
 (619) 852-5083
(1 row)

In addition to the wealth of regular expression functions, you can use regular expressions with the SIMILAR TO (~) operators. The following example returns all description fields with embedded phone numbers:

SELECT description
FROM mytable
WHERE description ~  
E'[(]{0,1}[0-9]{3}[)-.]{0,1}[\\s]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}';

Time Zones: What They Are and Are Not

A common misconception with PostgreSQL time zone−aware data types is that PostgreSQL records an extra time marker with the datetime value itself. This is incorrect. If you save 2012-2-14 18:08:00-8 (-8 being the Pacific offset from UTC), PostgreSQL internally thinks like this:

1. Calculate the UTC time for 2012-02-14 18:08:00-8. This is 2012-02-15 04:08:00-0.

2. Store the value 2012-02-15 04:08:00.

When you call the data back for display, PostgreSQL internally works like this:

1. Start with the requested time zone, defaulting to the server time zone if none is requested.

2. Compute the offset for time zone for this UTC time (-5 for America/New_York).

3. Determine the datetime with the offset (2012-02-15 16:08:00 with a -5 offset becomes 2012-02-15 21:08:00).

4. Display the result (2012-02-15 21:08:00-5).

So PostgreSQL doesn’t store the time zone, but uses it only to convert the datetime to UTC before storage. After that, the time zone information is discarded. When PostgreSQL displays datetime, it does so in the default time zone dictated by the session, user, database, or server, in that order. If you use time zone−aware data types, you should consider the consequence of a server move from one time zone to another. Suppose you based a server in New York City and subsequently restored the database in Los Angeles. All timestamps with time zone fields could suddenly display in Pacific time. This is fine as long as you anticipate this behavior.

Here’s an example of how something can go wrong. Suppose that McDonald’s had its server on the East Coast and the opening time for stores is stored as timetz. A new McDonald’s opens up in San Francisco. The new franchisee phones McDonald’s headquarters to add its store to the master directory with an opening time of 7 a.m. The data entry dude entered the information as he is told: 7 a.m. The East Coast PostgreSQL server interprets this to mean 7 a.m. Eastern, and now early risers in San Francisco are lining up at the door wondering why they can’t get their McBreakfasts at 4 a.m. Being hungry is one thing, but we can imagine many situations in which confusion over a difference of three hours could mean life or death.

Given the pitfalls, why would anyone want to use time zone−aware data types? First, it does spare you from having to do time zone conversions manually. For example, if a flight leaves Boston at 8 a.m. and arrives in Los Angeles at 11 a.m., and your server is in Europe, you don’t want to have to figure out the offset for each time manually. You could just enter the data with the Boston and Los Angeles local times. There’s another convincing reason to use time zone−aware data types: the automatic handling of DST. With countries deviating more and more from one another in DST schedules, manually keeping track of DST changes for a globally used database would require a dedicated programmer who does nothing but keep up-to-date with the latest DST schedules and map them to geographic enclaves.

Here’s an interesting example: a traveling salesperson catches a flight home from San Francisco to nearby Oakland. When she boards the plane, the clock at the terminal reads 2012-03-11 1:50 a.m. When she lands, the clock in the terminal reads 2012-03-11 3:10 a.m. How long was the flight? The key to the solution is that the change to DST occurred during the flight—the clocks sprang forward. With time zone−aware timestamps, you get 20 minutes, to which is a plausible answer for a short flight across the Bay. We get the wrong answer if we don’t use time zone−aware timestamps:

SELECT '2012-03-11 3:10 AM America/Los_Angeles'::timestamptz
 - '2012-03-11 1:50 AM America/Los_Angeles'::timestamptz;

gives you 20 minutes, whereas:

SELECT '2012-03-11 3:10 AM'::timestamp - '2012-03-11 1:50 AM'::timestamp;

gives you 1 hour and 20 minutes.

Let’s drive the point home with more examples, using a Boston server. For Example 5-10, I input my time in Los Angeles local time, but because my server is in Boston, I get a time returned in Boston local time. Note that it does give me the offset but that is merely display information. The timestamp is internally stored in UTC.

SELECT '2012-02-28 10:00 PM America/Los_Angeles'::timestamptz;

2012-02-29 01:00:00-05

In Example 5-11, we are getting back a timestamp without time zone. So the answer you get when you run this same query will be the same as mine, regardless of where in the world you are.

SELECT '2012-02-28 10:00 PM America/Los_Angeles'::timestamptz 
AT TIME ZONE 'Europe/Paris';

2012-02-29 07:00:00

The query is asking: what time is it in Paris if it’s 2012-02-28 10:00 p.m. in Los Angeles? Note the absence of the UTC offset in the result. Also, notice how you can specify a time zone with its official name rather than just an offset. Visit Wikipedia for a list of official time zone names.

Datetime Operators and Functions

The inclusion of a temporal interval data type greatly eases date and time arithmetic in PostgreSQL. Without it, we’d have to create another family of functions or use a nesting of functions as many other databases do. With intervals, we can add and subtract timestamp data simply by using the arithmetic operators we’re intimately familiar with. The following examples demonstrate operators and functions used with date and time data types.

The addition operator (+) adds an interval to a timestamp:

SELECT '2012-02-10 11:00 PM'::timestamp + interval '1 hour';

2012-02-11 00:00:00

You can also add intervals:

SELECT '23 hours 20 minutes'::interval + '1 hour'::interval;

24:20:00

The subtraction operator (-) subtracts an interval from a temporal type:

SELECT '2012-02-10 11:00 PM'::timestamptz - interval '1 hour';

2012-02-10 22:00:00-05

OVERLAPS, demonstrated in Example 5-12, returns true if two temporal ranges overlap. This is an ANSI SQL predicate equivalent to the overlaps function. OVERLAPS takes four parameters, the first pair constituting one range and the last pair constituting the other range. An overlap considers the time periods to be half open, meaning that the start time is included but the end time is outside the range. This is slightly different behavior from the common BETWEEN predicate, which considers both start and end to be included. This quirk won’t make a difference unless one of your ranges is a fixed point in time (a period for which start and end are identical). Watch out for this if you’re an avid user of the OVERLAPS function.

SELECT
	('2012-10-25 10:00 AM'::timestamp, '2012-10-25 2:00 PM'::timestamp)
	OVERLAPS
	('2012-10-25 11:00 AM'::timestamp,'2012-10-26 2:00 PM'::timestamp) AS x,
	('2012-10-25'::date,'2012-10-26'::date)
	OVERLAPS
	('2012-10-26'::date,'2012-10-27'::date) As y;

x  |y
---+---
t  |f

In addition to operators and predicates, PostgreSQL comes with functions supporting temporal types. A full listing can be found at Datetime Functions and Operators. We’ll demonstrate a sampling here.

Once again, we start with the versatile generate_series function. You can use this function with temporal types and interval steps.

As you can see in Example 5-13, we can express dates in our local datetime format or the more global ISO yyyy-mm-dd format. PostgreSQL automatically interprets differing input formats. To be safe, we tend to stick with entering dates in ISO, because date formats vary from culture to culture, server to server, or even database to database.

SELECT (dt - interval '1 day')::date As eom
FROM generate_series('2/1/2012', '6/30/2012', interval '1 month') As dt;

eom
------------
2012-01-31
2012-02-29
2012-03-31
2012-04-30
2012-05-31

Another popular activity is to extract or format parts of a datetime value. Here, the functions date_part and to_char fit the bill. Example 5-14 also drives home the behavior of DST for a time zone−aware data type. We intentionally chose a period that crosses a daylight saving switchover in US/East. Because the clock springs forward at 2 a.m., the final row of the table reflects the new time.

SELECT dt, date_part('hour',dt) As hr, to_char(dt,'HH12:MI AM') As mn
FROM
generate_series(
	'2012-03-11 12:30 AM',
	'2012-03-11 3:00 AM',
	interval '15 minutes'
) As dt;

dt                     | hr | mn
-----------------------+----+----------
2012-03-11 00:30:00-05 |  0 | 12:30 AM
2012-03-11 00:45:00-05 |  0 | 12:45 AM
2012-03-11 01:00:00-05 |  1 | 01:00 AM
2012-03-11 01:15:00-05 |  1 | 01:15 AM
2012-03-11 01:30:00-05 |  1 | 01:30 AM
2012-03-11 01:45:00-05 |  1 | 01:45 AM
2012-03-11 03:00:00-04 |  3 | 03:00 AM

By default, generate_series assumes timestamptz if you don’t explicitly cast values to timestamp.

Array Constructors

The most rudimentary way to create an array is to type the elements:

SELECT ARRAY[2001, 2002, 2003] As yrs;

If the elements of your array can be extracted from a query, you can use the more sophisticated constructor function, array():

SELECT array(
SELECT DISTINCT date_part('year', log_ts)
FROM logs
ORDER BY date_part('year', log_ts)
);

Although the array function has to be used with a query returning a single column, you can specify a composite type as the output, thereby achieving multicolumn results. We demonstrate this in “Custom and Composite Data Types”.

You can cast a string representation of an array to an array with syntax of the form:

SELECT '{Alex,Sonia}'::text[] As name, '{46,43}'::smallint[] As age;

name         | age
-------------+--------
{Alex,Sonia} | {46,43}

You can convert delimited strings to an array with the string_to_array function, as demonstrated in Example 5-15.

SELECT string_to_array('CA.MA.TX', '.') As estados;

estados
----------
{CA,MA,TX}
(1 row)

array_agg is an aggregate function that can take a set of any data type and convert it to an array, as demonstrated in Example 5-16.

SELECT array_agg(log_ts ORDER BY log_ts) As x
FROM logs
WHERE log_ts BETWEEN '2011-01-01'::timestamptz AND '2011-01-15'::timestamptz;

x
------------------------------------------
{'2011-01-01', '2011-01-13', '2011-01-14'}

PostgreSQL 9.5 introduced array_agg function support for arrays. In prior versions if you wanted to aggregate rows of arrays with array_agg, you’d get an error. array_agg support for arrays makes it much easier to build multidimensional arrays from one-dimensional arrays, as shown in Example 5-17.

SELECT array_agg(f.t)
 FROM ( VALUES ('{Alex,Sonia}'::text[]),
    ('{46,43}'::text[] ) ) As f(t);

array_agg
----------------------
{{Alex,Sonia},{46,43}}
(1 row)

In order to aggregate arrays, they must be of the same data type and the same dimension. To force that in Example 5-17, we cast the ages to text. We also have the same number of items in the arrays being aggregated: two people and two ages. Arrays with the same number of elements are called balanced arrays.

Unnesting Arrays to Rows

A common function used with arrays is unnest, which allows you to expand the elements of an array into a set of rows, as demonstrated in Example 5-18.

SELECT unnest('{XOX,OXO,XOX}'::char(3)[]) As tic_tac_toe;

tic_tac_toe
---
XOX
OXO
XOX

Although you can add multiple unnests to a single SELECT, if the number of resultant rows from each array is not balanced, you may get some head-scratching results.

A balanced unnest, as shown in Example 5-19, yields three rows.

SELECT
unnest('{three,blind,mice}'::text[]) As t,
unnest('{1,2,3}'::smallint[]) As i;

t     |i
------+-
three |1
blind |2
mice  |3

If you remove an element of one array so that you don’t have an equal number of elements in both, you get the result shown in Example 5-20.

SELECT
unnest( '{blind,mouse}'::varchar[]) AS v,
unnest('{1,2,3}'::smallint[]) AS i;

v     |i
------+-
blind |1
mouse |2
blind |3
mouse |1
blind |2
mouse |3

Version 9.4 introduced a multiargument unnest function that puts in null placeholders where the arrays are not balanced. The main drawback with the new unnest is that it can appear only in the FROM clause. Example 5-21 revisits our unbalanced arrays using the version 9.4 construct.

SELECT * FROM unnest('{blind,mouse}'::text[], '{1,2,3}'::int[]) AS f(t,i);

t      | i
-------+---
blind  | 1
mouse  | 2
<NULL> | 3

Array Slicing and Splicing

PostgreSQL also supports array slicing using the start:end syntax. It returns another array that is a subarray of the original. For example, to return new arrays that just contain elements 2 through 4 of each original array, type:

SELECT fact_subcats[2:4] FROM census.lu_fact_types;

To glue two arrays together end to end, use the concatenation operator ||:

SELECT fact_subcats[1:2] || fact_subcats[3:4] FROM census.lu_fact_types;

You can also add additional elements to an existing array as follows:

SELECT '{1,2,3}'::integer[] || 4 || 5;

The result is {1,2,3,4,5}.

Referencing Elements in an Array

Elements in arrays are most commonly referenced using the index of the element. PostgreSQL array indexes start at 1. If you try to access an element above the upper bound, you won’t get an error—only NULL will be returned. The next example grabs the first and last element of our array column:

SELECT
    fact_subcats[1] AS primero,
    fact_subcats[array_upper(fact_subcats, 1)] As segundo
FROM census.lu_fact_types;

We used the array_upper function to get the upper bound of the array. The second required parameter of the function indicates the dimension. In our case, our array is one-dimensional, but PostgreSQL does support multidimensional arrays.

Array Containment Checks

PostgreSQL has several operators for working with array data. We already saw the concatenation operator (||) for combining multiple arrays into one or adding an element to an array in “Array Slicing and Splicing”. Arrays also support the following comparison operators: =, <>, <, >, @>, <@, and &&. These operators require both sides of the operator to be arrays of the same array data type. If you have a GiST or GIN index on your array column, the comparison operators can utilize them.

The overlap operator (&&) returns true if two arrays have any elements in common. Example 5-22 will list all records in our table where the fact_subcats contains elements OCCUPANCY STATUS or For rent.

SELECT fact_subcats
FROM census.lu_fact_types
WHERE fact_subcats && '{OCCUPANCY STATUS,For rent}'::varchar[];

fact_subcats
-----------------------------------------------------------
{S01,"OCCUPANCY STATUS","Total housing units"...}
{S02,"OCCUPANCY STATUS","Total housing units"...}
{S03,"OCCUPANCY STATUS","Total housing units"...}
{S10,"VACANCY STATUS","Vacant housing units","For rent"...}
(4 rows)

The equality operator (=) returns true only if elements in all the arrays are equal and in the same order. If you don’t care about order of elements, and just need to know whether all the elements in one array appear as a subset of the other array, use the containment operators (@> , <@). Example 5-23 demonstrates the difference between the contains (@>) and contained by (@<) operators.

SELECT '{1,2,3}'::int[] @> '{3,2}'::int[] AS contains;

contains
--------
t
(1 row)

SELECT '{1,2,3}'::int[] <@ '{3,2}'::int[] AS contained_by;

contained_by
------------
f
(1 row)

Discrete Versus Continuous Ranges

PostgreSQL makes a distinction between discrete and continuous ranges. A range of integers or dates is discrete because you can enumerate each value within the range. Think of dots on a number line. A range of numerics or timestamps is continuous, because an infinite number of values lies between the end points.

A discrete range has multiple representations. Our earlier example of [-2,2) can be represented in the following ways and still include the same number of values in the range: [-2,1], (-3,1], (-3,2), [-2,2). Of these four representations, the one with [) is considered the canonical form. There’s nothing magical about closed-open ranges except that if everyone agrees to using that representation for discrete ranges, we can easily compare among many ranges without having to worry first about converting open to close or vice versa. PostgreSQL canonicalizes all discrete ranges, for both storage and display. So if you enter a date range as (2014-1-5,2014-2-1], PostgreSQL rewrites it as [2014-01-06,2014-02-02).

Built-in Range Types

PostgreSQL comes with six built-in range types for numbers and datetimes:

int4range, int8range

A range of integers. Integer ranges are discrete and subject to canonicalization.

numrange

A continuous range of decimals, floating-point numbers, or double-precision numbers.

daterange

A discrete date range of calendar dates without time zone awareness.

tsrange, tstzrange

A continuous date and time (timestamp) range allowing for fractional seconds. tstrange is not time zone−aware; tstzrange is time zone−aware.

For number-like ranges, if either the start point or the end point is left blank, PostgreSQL replaces it with a null. For practicality, you can interpret the null to represent either -infinity on the left or infinity on the right. In actuality, you’re bound by the smallest and largest values for the particular data type. So a int4range of (,) would be [-2147483648,2147483647).

For temporal ranges, -infinity and infinity are valid upper and lower bounds.

In addition to the built-in range types, you can create your own range types. When you do, you can set the range to be either discrete or continuous.

Defining Ranges

A range, regardless of type, is always comprised of two elements of the same type with the bounding condition denoted by brackets or parentheses, as shown in Example 5-24.

SELECT '[2013-01-05,2013-08-13]'::daterange; 
SELECT '(2013-01-05,2013-08-13]'::daterange; 
SELECT '(0,)'::int8range; 
SELECT '(2013-01-05 10:00,2013-08-13 14:00]'::tsrange; 

[2013-01-05,2013-08-14)
[2013-01-06,2013-08-14)
[1,)
("2013-01-05 10:00:00","2013-08-13 14:00:00"]

Ranges can also be defined using range constructor functions, which go by the same name as the range and can take two or three arguments. Here’s an example:

SELECT daterange('2013-01-05','infinity','[]');

The third argument denotes the bound. If omitted, the open-close [) convention is used by default. We suggest that you always include the third element for clarity.

Defining Tables with Ranges

Temporal ranges are popular. Suppose you have an employment table that stores employment history. Instead of creating separate columns for start and end dates, you can design a table as shown in Example 5-25. In the example, we added an index to the period column to speed up queries using our range column.

CREATE TABLE employment (id serial PRIMARY KEY, employee varchar(20), 
period daterange);
CREATE INDEX ix_employment_period ON employment USING gist (period); 
INSERT INTO employment (employee,period)
VALUES
	('Alex','[2012-04-24, infinity)'::daterange),
	('Sonia','[2011-04-24, 2012-06-01)'::daterange),
	('Leo','[2012-06-20, 2013-04-20)'::daterange),
	('Regina','[2012-06-20, 2013-04-20)'::daterange);

Add a GiST index on the range field.

Range Operators

Two range operators tend to be used most often: overlap (&&) and contains (@>). Those are the ones we’ll cover. To see the full catalog of range operators, go to Range Operators.

SELECT
	e1.employee,
	string_agg(DISTINCT e2.employee, ', ' ORDER BY e2.employee) As colleagues
FROM employment As e1 INNER JOIN employment As e2
ON e1.period && e2.period
WHERE e1.employee <> e2.employee
GROUP BY e1.employee;

employee | colleagues
---------+-------------------
Alex     | Leo, Regina, Sonia
Leo      | Alex, Regina
Regina   | Alex, Leo
Sonia    | Alex

SELECT employee FROM employment WHERE period @> CURRENT_DATE GROUP BY employee;

employee
--------
Alex

Inserting JSON Data

To create a table to store JSON, define a column as a json type:

CREATE TABLE persons (id serial PRIMARY KEY, person json);

Example 5-28 inserts JSON data. PostgreSQL automatically validates the input to make sure what you are adding is valid JSON. Remember that you can’t store invalid JSON in a JSON column, nor can you cast invalid JSON to a JSON data type.

INSERT INTO persons (person)
VALUES (
    '{
        "name":"Sonia",
        "spouse":
        {
            "name":"Alex",
            "parents":
            {
                "father":"Rafael",
                "mother":"Ofelia"
            },
            "phones":
            [
                {
                    "type":"work",
                    "number":"619-722-6719"
                },
                {
                    "type":"cell",
                    "number":"619-852-5083"
                }
            ]
        },
        "children":
        [
            {
                "name":"Brandon",
                "gender":"M"
            },
            {
                "name":"Azaleah",
                "girl":true,
                "phones": []
            }
        ]
    }'
);

Querying JSON

The easiest way to traverse the hierarchy of a JSON object is by using pointer symbols. Example 5-29 shows some common usage.

SELECT person->'name' FROM persons;
SELECT person->'spouse'->'parents'->'father' FROM persons;

You can also write the query using a path array as in the following example:

SELECT person#>array['spouse','parents','father'] FROM persons;

Notice that you must use the #> pointer symbol if what comes after is a path array.

To penetrate JSON arrays, specify the array index. JSON arrays is zero-indexed, unlike PostgreSQL arrays, whose indexes start at 1.

SELECT person->'children'->0->'name' FROM persons;

And the path array equivalent:

SELECT person#>array['children','0','name'] FROM persons;

All queries in the prior examples return the value as JSON primitives (numbers, strings, booleans). To return the text representation, add another greater-than sign as in the following examples:

SELECT person->'spouse'->'parents'->>'father' FROM persons;
SELECT person#>>array['children','0','name'] FROM persons;

If you are chaining the -> operator, only the very last one can be a ->> operator.

The json_array_elements function takes a JSON array and returns each element of the array as a separate row as in Example 5-30.

SELECT json_array_elements(person->'children')->>'name' As name FROM persons;

name
-------
Brandon
Azaleah
(2 rows)

Outputting JSON

In addition to querying JSON data, you can convert other data to JSON. In these next examples, we’ll demonstrate the use of JSON built-in functions to create JSON objects.

Example 5-31 demonstrates the use of row_to_json to convert a subset of columns in each record from the table we created and loaded in Example 5-28.

SELECT row_to_json(f) As x
FROM (
    SELECT id, json_array_elements(person->'children')->>'name' As cname FROM persons
) As f;

                x
--------------------------
{"id":1,"cname":"Brandon"}
{"id":1,"cname":"Azaleah"}
(2 rows)

To output each row in our persons table as JSON:

SELECT row_to_json(f) As jsoned_row FROM persons As f;

The use of a row as an output field in a query is a feature unique to PostgreSQL. It’s handy for creating complex JSON objects. We describe it further in “Composite Types in Queries”, and Example 7-20 demonstrates the use of array_agg and array_to_json to output a set of rows as a single JSON object. In version 9.3 we have at our disposal the json_agg function. We demonstrate its use in Example 7-21.

Binary JSON: jsonb

New in PostgreSQL 9.4 is the jsonb data type. It is handled through the same operators as those for the json type, and similarly named functions, plus several additional ones. jsonb performance is much better than json performance because jsonb doesn’t need to be reparsed during operations. There are a couple of key differences between the jsonb and json data types:

• jsonb is internally stored as a binary object and does not maintain the formatting of the original JSON text as the json data type does. Spaces aren’t preserved, numbers can appear slightly different, and attributes become sorted. For example, a number input as e-5 would be converted to its decimal representation.

• jsonb does not allow duplicate keys and silently picks one, whereas the json type preserves duplicates. This is demonstrated in Michael Paquier’s article “Manipulating jsonb data by abusing of key uniqueness”.

• jsonb columns can be directly indexed using the GIN index method (covered in “Indexes”), whereas json requires a functional index to extract key elements.

To demonstrate these concepts, we’ll create another persons table, replacing the json column with a jsonb:

CREATE TABLE persons_b (id serial PRIMARY KEY, person jsonb);

To insert data into our new table, we would repeat Example 5-28.

So far, working with JSON and binary JSON has been the same. Differences appear when you query. To make the binary JSON readable, PostgreSQL converts it to a canonical text representation, as shown in Example 5-32.

SELECT person As b FROM persons_b WHERE id = 1; 
SELECT person As j FROM persons WHERE id = 1;

b
---------------------------------------------------------------------------------
{"name": "Sonia",
"spouse": {"name": "Alex", "phones": [{"type": "work", "number": "619-722-6719"},
{"type": "cell", "number": "619-852-5083"}],
"parents": {"father": "Rafael", "mother": "Ofelia"}},
"children": [{"name": "Brandon", "gender": "M"},
 {"girl": true, "name": "Azaleah", "phones": []}]}
(1 row)

                       j
---------------------------------------------
 {
    "name":"Sonia",
         "spouse":
         {
             "name":"Alex",
             "parents":
             {
                 "father":"Rafael",
                 "mother":"Ofelia"
             },
             "phones":
             [
                 {
                     "type":"work",
                     "number":"619-722-6719"+
                 },
                 {
                     "type":"cell",
                     "number":"619-852-5083"+
                 }
             ]
         },
         "children":
         [
             {
                 "name":"Brandon",
                 "gender":"M"
             },
             {
                 "name":"Azaleah",
                 "girl":true,
                 "phones": []
             }
         ]
     }
(1 row)

jsonb reformats input and removes whitespace. Also, the order of attributes is not maintained from the insert.

json maintains input whitespace and the order of attributes.

jsonb has similarly named functions as json, plus some additional ones. So, for example, the json family of functions such as json_extract_path_text and json_each are matched in jsonb by jsonb_extract_path_text, jsonb_each, etc. However, the equivalent operators are the same, so you will find that the examples in “Querying JSON” work largely the same without change for the jsonb type—just replace the table name and json_array_elements with jsonb_array_elements.

In addition to the operators supported by json, jsonb has additional comparator operators for equality (=), contains (@>), contained (<@), key exists (?), any of array of keys exists (?|), and all of array of keys exists (?&).

So, for example, to list all people that have a child named Brandon, use the contains operator as demonstrated in Example 5-33.

 SELECT person->>'name' As name
FROM persons_b
WHERE person @> '{"children":[{"name":"Brandon"}]}';

name
-----
Sonia

These additional operators provide very fast checks when you complement them with a GIN index on the jsonb column:

CREATE INDEX ix_persons_jb_person_gin ON persons_b USING gin (person);

We don’t have enough records in our puny table for the index to kick in, but for more rows, you’d see that Example 5-33 utilizes the index.

Editing JSONB data

PostgreSQL 9.5 introduced native jsonb concatenation (||) and subtraction operators (-, #-) as well as companion functions for setting data. These operators do not exist for the json datatype. To be able to accomplish these tasks in prior versions, you’d have to lean on “Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions” to do the work.

The concatenation operator can be used to add and replace attributes of a jsonb object. In Example 5-34 we add an address attribute to the Gomez family and use the RETURNING construct covered in “Returning Affected Records to the User” to return the updated value. The new value has an address attribute.

UPDATE persons_b
SET person = person || '{"address": "Somewhere in San Diego, CA"}'::jsonb
WHERE person @> '{"name":"Sonia"}'
RETURNING person;

profile
-------------------------------------------------------------------------------
{"name": "Sonia", ... "address": "Somewhere in San Diego, CA", "children": ...}
(1 row)
UPDATE 1

Because JSONB requires that keys be unique, if you try to add a duplicate key, the original value will be replaced instead. So to update with a new address, we would repeat the exercise in Example 5-34, but replacing Somewhere in San Diego, CA with something else.

If we decided we no longer wanted an address, we could use the - as shown in Example 5-35.

UPDATE persons_b
SET person = person - 'address'
WHERE person @> '{"name":"Sonia"}';

The simple - operator works for first-level elements, but what if you wanted to remove an attribute from a particular member? This is when you’d use the #- operator. #- takes an array of text values that denotes the path of the element you want to remove. In Example 5-36 we remove the girl designator of Azaleah.

UPDATE persons_b
SET  person = person #- '{children,1,girl}'::text[]
WHERE person @> '{"name":"Sonia"}'
RETURNING person->'children'->1;

{"name": "Azaleah", "phones": []}

When removing elements from an array, you need to denote the index. Because JavaScript indexes start at 0, to remove an element from the second child, we use 1 instead of 2. If we wanted to remove Azaleah entirely, we would have used '{children,1}'::text[].

To add a gender attribute, or replace one that was previously set, we can use the jsonb_set function as shown in Example 5-37.

UPDATE persons_b
SET person = jsonb_set(person,'{children,1,gender}'::text[],'"F"'::jsonb, true)
WHERE person @> '{"name":"Sonia"}';

jsonb_set takes three arguments of form jsonb_set(jsonb_to_update, text_array_path, new_jsonb_value,allow_creation). If you set allow_creation to false when the property did not already exist, the statement will return an error.

Inserting XML Data

When you create a column of the xml data type, PostgreSQL automatically ensures that only valid XML values populate the rows. This is what distinguishes an XML column from just any text column. However, the XML is not validated against any Document Type Definition (DTD) or XML Schema Definition (XSD), even if it is specified in the XML document. To freshen up on what constitutes valid XML, Example 5-38 shows you how to append XML data to a table by declaring a column as xml and inserting into it as usual.

CREATE TABLE families (id serial PRIMARY KEY, profile xml);
INSERT INTO families(profile)
VALUES (
    '<family name="Gomez">
        <member><relation>padre</relation><name>Alex</name></member>
        <member><relation>madre</relation><name>Sonia</name></member>
        <member><relation>hijo</relation><name>Brandon</name></member>
        <member><relation>hija</relation><name>Azaleah</name></member>
	</family>');

Each XML value could have a different XML structure. To enforce uniformity, you can add a check constraint, covered in “Check Constraints”, to the XML column. Example 5-39 ensures that all family has at least one relation element. The '/family/member/relation' is XPath syntax, a basic way to refer to elements and other parts of XML.

ALTER TABLE families ADD CONSTRAINT chk_has_relation
CHECK (xpath_exists('/family/member/relation', profile));

If we then try to insert something like:

INSERT INTO families (profile) VALUES ('<family name="HsuObe"></family>');

we will get this error: ERROR: new row for relation "families" violates check constraint "chk_has_relation".

For more involved checks that require checking against DTD or XSD, you’ll need to resort to writing functions and using those in the check constraint, because PostgreSQL doesn’t have built-in functions to handle those kinds of checks.

Querying XML Data

To query XML, the xpath function is really useful. The first argument is an XPath query, and the second is an xml object. The output is an array of XML elements that satisfies the XPath query. Example 5-40 combines xpath with unnest to return all the family members. unnest unravels the array into a row set. We then cast the XML fragment to text.

SELECT ordinality AS id, family,
    (xpath('/member/relation/text()', f))[1]::text As relation,
    (xpath('/member/name/text()', f))[1]::text As mem_name 
FROM (
    SELECT
        (xpath('/family/@name', profile))[1]::text As family, 
        f.ordinality, f.f
        FROM families, unnest(xpath('/family/member', profile)) WITH ORDINALITY AS f
) x; 

 id | family | relation | mem_name
----+--------+----------+----------
  1 | Gomez  | padre    | Alex
  2 | Gomez  | madre    | Sonia
  3 | Gomez  | hijo     | Brandon
  4 | Gomez  | hija     | Azaleah
(4 rows)

Get the text element in the relation and name tags of each member element. We need to use array subscripting because xpath always returns an array, even if only one element is returned.

Get the name attribute from family root. For this we use @attribute_name.

Break the result of the SELECT into the subelements <member>, <relation>, </relation>, <name>, </name>, and </member> tags. The slash is a way of getting at subtag elements. For example, xpath('/family/member', 'profile') will return an array of all members in each family that is defined in a profile. The @ sign is used to select attributes of an element. So, for example, family/@name returns the name attribute of a family. By default, xpath always returns an element, including the tag part. The text() forces a return of just the text body of an element.

New in version 10 is the ANSI-SQL standard XMLTABLE construct. XMLTABLE converts text of XML into individual rows and columns based on some defined transformation. We’ll repeat Example 5-40 using XMLTABLE.

SELECT xt.*
  FROM families,
       XMLTABLE ('/family/member' PASSING profile  
                 COLUMNS 
                    id FOR ORDINALITY ,
                    family text PATH '../@name' ,
                    relation text NOT NULL ,
                    member_name text PATH 'name' NOT NULL
                ) AS xt;

 id | family | relation | mem_name
----+--------+----------+----------
  1 | Gomez  | padre    | Alex
  2 | Gomez  | madre    | Sonia
  3 | Gomez  | hijo     | Brandon
  4 | Gomez  | hija     | Azaleah
(4 rows)

FTS Configurations

Most PostgreSQL distributions come packaged with over 10 FTS configurations. All these are installed in the pg_catalog schema.

To see the listing of installed FTS configurations, run the query SELECT cfgname FROM pg_ts_config;. Or use the \dF command in psql. A typical list follows:

cfgname
----------
simple
danish
dutch
english
finnish
french
german
hungarian
italian
norwegian
portuguese
romanian
russian
spanish
swedish
turkish
(16 rows)

If you need to create your own configurations or dictionaries, refer to PostgreSQL Manual: Full Text Search Configuration and PostgreSQL Manual: Full Text Search Dictionaries.

You’re not limited to built-in FTS configurations. You can create your own. But before you do, you may wish to see what other users have already created that may suit your needs. If your text is medical-related, you may be able to find a configuration with dictionaries chock full of specialized anatomy terms. If your text is in Spanish, find a configuration that tailors to your particular dialect of Spanish.

Once you locate a configuration that you’d like added to your arsenal, installation is quite simple and usually doesn’t require additional compilation. We demonstrate by installing the popular hunspell configuration.

Start by downloading hunspell configurations from hunspell_dicts. You’ll be greeted by hunspell for many different languages. We’ll go with hunspell_en_us:

1. Download everything in the folder.

2. Copy en_us.affix and en_us.dict to your PostgreSQL installation directory share/tsearch_data.

3. Copy the hunspell_en_us--*.sql and hunspell_en_us.control files to your PostgreSQL installation directory share/extension folder.

Next, run:

CREATE EXTENSION hunspell_en_us SCHEMA pg_catalog;

From psql, if you now run Example 5-42, you’ll see details of the hunspell configuration and dictionary we just installed.

\dF+ english_hunspell;

Text search configuration "pg_catalog.english_hunspell"
Parser: "pg_catalog.default"
Token           | Dictionaries
----------------+-------------------------------
asciihword      | english_hunspell,english_stem
asciiword       | english_hunspell,english_stem
email           | simple
file            | simple
float           | simple
host            | simple
hword           | english_hunspell,english_stem
hword_asciipart | english_hunspell,english_stem
hword_numpart   | simple
hword_part      | english_hunspell,english_stem
int             | simple
numhword        | simple
numword         | simple
sfloat          | simple
uint            | simple
url             | simple
url_path        | simple
version         | simple
word            | english_hunspell,english_stem

Contrast that output to the built-in English configuration in Example 5-43, which gives you the dictionaries used by the English configuration.

\dF+ english;

Text search configuration "pg_catalog.english"
Parser: "pg_catalog.default"
Token           | Dictionaries
----------------+--------------
asciihword      | english_stem
asciiword       | english_stem
email           | simple
file            | simple
float           | simple
host            | simple
hword           | english_stem
hword_asciipart | english_stem
hword_numpart   | simple
hword_part      | english_stem
int             | simple
numhword        | simple
numword         | simple
sfloat          | simple
uint            | simple
url             | simple
url_path        | simple
version         | simple
word            | english_stem

The only difference between the two is that hunspell draws from an additional dictionary.

Not sure which configuration is the default? Run:

SHOW default_text_search_config;

To replace the default with another, run:

ALTER DATABASE postgresql_book 
SET default_text_search_config = 'pg_catalog.english';

This replacement takes place at the database level, but as with most PostgreSQL configuration settings, you can make the change at the server, user, or session levels.

TSVectors

A text column must be vectorized before FTS can search against it. The resultant vector column is a tsvector data type. To create a tsvector from text, you must specify the FTS configuration to use. The vectorization reduces the original text to a set of word skeletons, referred to as lexemes, by removing stop words. For each lexeme, the TSVector records where in the original text it appears. The more frequently a lexeme appears, the higher the weight. Each lexeme therefore is imbued with at least one position, much like a vector in the physical sense.

Use the to_tsvector function to vectorize a blob of text. This function will resort to the default FTS configuration unless you specify another.

Example 5-44 shows how TSVectors differ depending on which FTS configuration was used in their construction.

SELECT
    c.name,
    CASE
        WHEN c.name ='default' THEN to_tsvector(f.t)
        ELSE to_tsvector(c.name::regconfig,f.t)
    END As vect
FROM (
    SELECT 'Just dancing in the rain. I like to dance.'::text) As f(t), (
        VALUES ('default'),('english'),('english_hunspell'),('simple')
    ) As c(name);

name             | vect
-----------------+-----------------------------------------------------------------------------
default          | 'danc':2,9 'like':7 'rain':5
english          | 'danc':2,9 'like':7 'rain':5
english_hunspell | 'dance':2,9 'dancing':2 'like':7 'rain':5
simple           | 'dance':9 'dancing':2 'i':6 'in':3 'just':1 'like':7 
'rain':5 'the':4 'to':8
(4 rows)

Example 5-44 demonstrates how four different FTS configurations result in different vectors. Note how the English and Hunspell configurations remove all stop words, such as just and to. English and Hunspell also convert words to their normalized form as dictated by their dictionaries, so dancing becomes danc and dance, respectively. The simple configuration has no concept of stemming and stop words.

The to_tsvector function returns where each lexeme appears in the text. So, for example, 'danc':2,9 means that dancing and dance appear as the second and the ninth words.

To incorporate FTS into your database, add a tsvector column to your table. You then either schedule the tsvector column to be updated regularly, or add a trigger to the table so that whenever relevant fields update, the tsvector field recomputes.

For our examples, we gathered fictitious movie data. Load the tables from psql using the file.sql script as follows:

\encoding utf8;
\i film.sql

Next, we add and compute a tsvector column to the film table as shown in Example 5-45.

ALTER TABLE film ADD COLUMN fts tsvector;
UPDATE film
SET fts =
    setweight(to_tsvector(COALESCE(title,'')),'A') ||
    setweight(to_tsvector(COALESCE(description,'')),'B');
CREATE INDEX ix_film_fts_gin ON film USING gin (fts);

Example 5-45 vectorizes the title and description columns and stores the vector in a newly created tsvector column. To speed up searches, we add a GIN index on the tsvector column. GIN is a lossless index. You can also add a GiST index on a vector column. GiST is lossy and slower to search but builds quicker and takes up less disk space. We explore indexes in more detail in “Indexes”.

By populating the fts column, we’ve introduced two new constructs, the setweight function and the concatenation operator (||), to tsvector.

To distinguish the relative importance of different lexemes, you could assign a weight to each. The weights must be A, B, C, or D, with A ranking highest in importance. In Example 5-45, we assigned A to lexemes culled from the title and B to lexemes from the description. If our search term matches a lexeme from the title, we deem the match to be more relevant than a match from the description of the movie.

TSVectors can be formed from other tsvectors using the concatenation (||) operator. We used it here to combine the title and description into a single tsvector. This way when we search, we have to contend with only a single column.

Should data change in one of the basis columns forming the tsvector, you must re-vectorize. To avoid having to manually run to_tsvector every time data changes, create a trigger that responds to updates. In the trigger, use the handy tsvector_update_trigger function as shown in Example 5-46.

CREATE TRIGGER trig_tsv_film_iu
BEFORE INSERT OR UPDATE OF title, description ON film FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger(fts,'pg_catalog.english',
title,description);

Example 5-46 reacts to an insert or update in the title or description by revectoring the fts column. One shortcoming though: tsvector_update_trigger does not support weighting.

TSQueries

A FTS, or any text search for that matter, has two components: the searched text and the search terms. For FTS to work, both must be vectorized. We have already seen how to vectorize the searched text to create tsvector columns. We now show you how to vectorize the search terms.

FTS refers to vectorized search terms as tsqueries, and PostgreSQL offers several functions that will convert plain-text search terms to tsqueries: to_tsquery, plainto_tsquery, and phraseto_tsquery. The latter is a new function in 9.6 and takes the ordering of words in the search term into consideration.

tsqueries are normally created on the fly rather than being stored in a table. However, if you are building a system where people can save their queries and run them, you could define a tsquery column in a table.

Example 5-47 shows the output using the to_tsquery functions against two configurations: the default English configuration and the Hunspell configuration.

SELECT to_tsquery('business & analytics');

to_tsquery
-----------------
'busi' & 'analyt'

SELECT to_tsquery('english_hunspell','business & analytics');

to_tsquery
--------------------------------
('business' | 'busy') & 'analyt'

Both examples are akin to searching for text containing the words business and analytics. The and operator (&) means that both words must appear in the searched text. The or operator (|) means one or both of the words must appear in the searched text. If the configuration in use finds multiple stems for a word, they are stitched together by the or operator.

A slight variant of to_tsquery is plain_totsquery. This function automatically inserts the and operator between words for you, saving you a few key clicks. See Example 5-48.

SELECT plainto_tsquery('business analytics');

plainto_tsquery
-----------------
'busi' & 'analyt'

to_tsquery and plainto_tsquery look only at words, not their sequence. So business analytics and analytics business produce the same tsquery. This is a shortcoming because you’re limited to searching by single words only. Version 9.6 addressed this with the function phraseto_tsquery. In Example 5-49, the phraseto_tsquery vectorizes the words, inserting the distance operator between the words. This means that the searched text must contain the words business and analytics in that order, upgrading a word search to a phrase search.

SELECT phraseto_tsquery('business analytics');

phraseto_tsquery
-------------------
'busi' <-> 'analyt'

SELECT phraseto_tsquery('english_hunspell','business analytics');

phraseto_tsquery
---------------------------------------------
'business' <-> 'analyt' | 'busy' <-> 'analyt'

You can also cast text to tsquery without using any functions, as in 'business & analytics'::tsquery. However, with casts, words are not replaced with lexemes and are taken literally.

TSQueries can be combined using the or operator (||) or the and operator (&&). The expression tsquery1 || tsquery2 means matching text must satisfy either tsquery1 or tsquery2. The expression tsquery1 && tsquery2 means matching text must satisfy both tsquery1 and tsquery2.

Examples of each are shown in Example 5-50.

SELECT plainto_tsquery('business analyst') || phraseto_tsquery('data scientist');

tsquery
-------------------------------------------
'busi' & 'analyst' | 'data' <-> 'scientist'

SELECT plainto_tsquery('business analyst') && phraseto_tsquery('data scientist');

tsquery
--------------------------------------------
'busi' & 'analyst' & ('data' <-> 'scientist')

tsqueries and tsvectors have additional operators for doing things like determining if one is a subset of another, and several other functions. All this is detailed in PostgreSQL Manual: Text Search Functions and Operators.

Using Full Text Search

We have created a tsvector from our text; we have created a tsquery from our search terms. Now, we can perform an FTS. We do so by using the @@ operator. Example 5-51 demonstrates it.

SELECT left(title,50) As title, left(description,50) as description
FROM film
WHERE fts @@ to_tsquery('hunter & (scientist | chef)') AND title > '';

title                  | description
-----------------------+---------------------------------------------------
ALASKA PHANTOM         | A Fanciful Saga of a Hunter And a Pastry Chef who
CAUSE DATE             | A Taut Tale of a Explorer And a Pastry Chef who mu
CINCINATTI WHISPERER   | A Brilliant Saga of a Pastry Chef And a Hunter who
COMMANDMENTS EXPRESS   | A Fanciful Saga of a Student And a Mad Scientist w
DAUGHTER MADIGAN       | A Beautiful Tale of a Hunter And a Mad Scientist w
GOLDFINGER SENSIBILITY | A Insightful Drama of a Mad Scientist And a Hunter
HATE HANDICAP          | A Intrepid Reflection of a Mad Scientist And a Pio
INSIDER ARIZONA        | A Astounding Saga of a Mad Scientist And a Hunter
WORDS HUNTER           | A Action-Packed Reflection of a Composer And a Mad
(9 rows)

Example 5-51 finds all films with a title or description containing the word hunter and either the word scientist, or the word chef, or both.

If you are running PostgreSQL 9.6, you can specify the proximity and order of words. See Example 5-52.

SELECT left(title,50) As title, left(description,50) as description
FROM film
WHERE fts @@ to_tsquery('hunter <4> (scientist | chef)') AND title > '';

title            | description
-----------------+---------------------------------------------------
ALASKA PHANTOM   | A Fanciful Saga of a Hunter And a Pastry Chef who
DAUGHTER MADIGAN | A Beautiful Tale of a Hunter And a Mad Scientist w
(2 rows)

Example 5-52 requires that the word hunter precede scientist or chef by exactly four words.

Ranking Results

FTS includes functions for ranking results. These functions are ts_rank and ts_rank_cd. ts_rank considers only the frequency of terms and weights, while ts_rank_cd (cd stands for coverage density) also considers the position of the search term within the searched text. If lexemes are found closer together, the result ranks higher. ts_rank_cd is meaningful only if you have position markers in your tsvector; otherwise, it returns zero. The frequency with which a search term appears also depends on position markers. So the ts_rank function will consider only weights if positional markers are missing. By default, ts_rank and ts_rank_cd apply the weights 0.1, 0.2, 0.4, and 1.0, respectively, for D, C, B, and A. Example 5-53 follows the default order.

SELECT title, left(description,50) As description,
    ts_rank(fts,ts)::numeric(10,3) AS r
FROM film, to_tsquery('english','love  & (wait | indian | mad)') AS ts
WHERE fts @@ ts AND title > ''
ORDER BY r DESC;

title         | description                                        | r
--------------+----------------------------------------------------+------
INDIAN LOVE   | A Insightful Saga of a Mad Scientist And a Mad Sci | 0.999
LAWRENCE LOVE | A Fanciful Yarn of a Database Administrator And a  | 0.252
(2 rows)

Let’s suppose we wish to retrieve a field only if the search terms appear in the title. For this situation we would assign 1 to the title field and 0 to all others. Example 5-54 repeats Example 5-53, passing in an array of weights.

SELECT
    left(title,40) As title,
    ts_rank('{0,0,0,1}'::numeric[],fts,ts)::numeric(10,3) AS r,
    ts_rank_cd('{0,0,0,1}'::numeric[],fts,ts)::numeric(10,3) As rcd
FROM film, to_tsquery('english', 'love  & (wait | indian | mad )') AS ts
WHERE fts @@ ts AND title > ''
ORDER BY r DESC;

title         | r     | rcd
--------------+-------+------
INDIAN LOVE   | 0.991 | 1.000
LAWRENCE LOVE | 0.000 | 0.000
(2 rows)

Notice how in Example 5-54 the second entry has a ranking of zero because the title does not contain all the words to satisfy the tsquery.

Full Text Stripping

By default, vectorization adds markers (location of the lexemes within the vector) and optionally weights (A, B, C, D). If your searches care only whether a particular term can be found, regardless of where it is in the text, how frequently it occurs, or its prominence, you can declutter your vectors using the strip function. This saves disk space and gains some speed. Example 5-55 compares what an unstripped versus stripped vector looks like.

SELECT fts
FROM film
WHERE film_id = 1;

'academi':1A 'battl':15B 'canadian':20B 'dinosaur':2A 'drama':5B 'epic':4B
'feminist':8B 'mad':11B 'must':14B 'rocki':21B 'scientist':12B 'teacher':17B

SELECT strip(fts)
FROM film
WHERE film_id = 1;

'academi' 'battl' 'canadian' 'dinosaur' 'drama' 'epic' 'feminist' 'mad'
'must' 'rocki' 'scientist' 'teacher'

Keep in mind that although a stripped vector is faster to search and takes up less disk space, many operators and functions cannot be used in conjunction with them. For instance, because a stripped vector has no markers, distance operators cannot be used.

Full Text Support for JSON and JSONB

New in version 10 are ts_headline and to_tsvector, which take as input json and jsonb data. The functions work just like the text ones, except they consider only the values of json/jsonb data and not the keys or json markup. Example 5-56 applies the function to the json person column of the table we created in Example 5-28.

SELECT to_tsvector(person)
   FROM persons WHERE id=1;

     to_tsvector
----------------------------------------------------------------------------------
 '-5083':19 '-6719':13 '-722':12 '-852':18 '619':11,17 'alex':3 'azaleah':25
 'brandon':21 'cell':15 'm':23 'ofelia':7 'rafael':5 'sonia':1 'work':9
(1 row)

To apply this function to the jsonb table persons_b, swap out the persons table for persons_b. Similar to the to_tsvector for text, these functions also have a variant that takes the FTS configuration to use as their first argument. To make best use of these functions, create a tsvector column in your table and populate the field using either a trigger or update as needed.

Also available now for json and jsonb is the ts_headline function, which tags as HTML all matching text in the json document. Example 5-57 flags all references to Rafael in the document.

SELECT ts_headline(person->'spouse'->'parents', 'rafael'::tsquery)
FROM persons_b WHERE id=1;

{"father": "<b>Rafael</b>", "mother": "Ofelia"}
(1 row)

Note the bold HTML tags around the matching value.

All Tables Are Custom Data Types

PostgreSQL automatically creates custom types for all tables. For all intents and purposes, you can use custom types just as you would any other built-in type. So we could conceivably create a table that has a column type that is another table’s custom type, and we can go even further and make an array of that type. We demonstrate this “turducken” in Example 5-58.

CREATE TABLE chickens (id integer PRIMARY KEY);
CREATE TABLE ducks (id integer PRIMARY KEY, chickens chickens[]);
CREATE TABLE turkeys (id integer PRIMARY KEY, ducks ducks[]);

INSERT INTO ducks VALUES (1, ARRAY[ROW(1)::chickens, ROW(1)::chickens]);
INSERT INTO turkeys VALUES (1, array(SELECT d FROM ducks d));

We create an instance of a chicken without adding it to the chicken table itself; hence we’re able to repeat id with impunity. We take our array of two chickens, stuff them into one duck, and add it to the ducks table. We take the duck we added and stuff it into the turkeys table.

Finally, let’s see what we have in our turkey:

SELECT * FROM turkeys;

output
--------------------------
id | ducks
---+----------------------
1  | {"(1,\"{(1),(1)}\")"}

We can also replace subelements of our turducken. This next example replaces our second chicken in our first turkey with a different chicken:

UPDATE turkeys SET ducks[1].chickens[2] = ROW(3)::chickens
WHERE id = 1 RETURNING *;

output
--------------------------
id | ducks
---+----------------------
 1 | {"(1,\"{(1),(3)}\")"}

We used the RETURNING clause as discussed in “Returning Affected Records to the User” to output the changed record.

Any complex row or column, regardless of how complex, can be converted to a json or jsonb column like so:

SELECT id, to_jsonb(ducks) AS ducks_jsonb
FROM turkeys;

id | ducks_jsonb
---+------------------------------------------------
 1 | [{"id": 1, "chickens": [{"id": 1}, {"id": 3}]}]
(1 row)

PostgreSQL internally keeps track of object dependencies. The ducks.chickens column is dependent on the chickens table. The turkeys.ducks column is dependent on the ducks table. You won’t be able to drop the chickens table without specifying CASCADE or first dropping the ducks.chickens column. If you do a CASCADE, the ducks.chickens column will be gone, and without warning, your turkeys will have no chickens in their ducks.