9 Additional Features

Full-Text Search

Full-text search refers to searching for documents written in natural language and sorting the results by relevance to the search query. In the simplest and most typical case, the query consists of one or more words, and the relevance is defined by the frequency of these words in the document. This is more or less what happens when we type a phrase in Google or Yandex search engines. However, despite SQL’s powerful capabilities, it is not always sufficient for effective data handling. This has become increasingly evident with the rise of Big Data, which is often poorly structured and difficult to parse.

There is a large number of search engines, free and paid, that enable you to index the whole collection of your documents and set up search of quite decent quality. In this case, the index—an essential search tool for speeding up queries—is separate from the database. It means that many valuable database features become unavailable: database synchronization, transaction isolation, accessing and using metadata to limit the search range, setting up access policies, and many more.

Shortcomings of document-oriented database management systems usually have a similar nature: they have rich full-text search functionality, but data security and synchronization features are of low priority. Besides, such databases (for example, MongoDB) are usually NoSQL ones, so by design, they lack all the power of SQL accumulated over years.

However, traditional SQL database systems do have built-in full-text search capabilities. The LIKE operator is part of the standard SQL syntax, but its flexibility is often insufficient. Therefore, developers had to implement their own extensions of the SQL standard. In PostgreSQL, these are comparison operators ILIKE, ~, ~*, but they don’t solve all the problems either, as they don’t take into account grammatical forms, are not suitable for ranking, and work rather slowly.

When talking about the tools of the full-text search itself, it’s important to understand that they are far from being standardized: each database system uses its own approach and syntax. Russian users of PostgreSQL have some advantage here: its full-text search extensions were created by Russian developers, so there is a possibility to contact the experts directly or even attend their lectures to go into low-level details, if required. Here we’ll only provide some simple examples.

To learn about the full-text search capabilities, we are going to create one more table in the demo database. Let it be a lecturer’s draft notes split into chapters by lecture topics:

test=# CREATE TABLE course_chapters(
  c_no text REFERENCES courses(c_no),
  ch_no text,
  ch_title text,
  txt text,
  CONSTRAINT pkt_ch PRIMARY KEY(ch_no, c_no)
);

CREATE TABLE

Now let’s enter the text of the first lectures for our courses CS301 and CS305:

test=# INSERT INTO course_chapters(
 c_no, ch_no, ch_title, txt)
VALUES
('CS301', 'I', 'Databases',
 'We start our acquaintance with ' ||
 'the fascinating world of databases'),
('CS301', 'II', 'First Steps',
 'Getting more fascinated with ' ||
 'the world of databases'),
('CS305', 'I', 'Local Networks',
 'Here we start our adventurous journey ' ||
 'through the intriguing world of networks');
INSERT 0 3

Check the result:

test=# SELECT ch_no AS no, ch_title, txt
FROM course_chapters \gx
-[ RECORD 1 ]-----------------------------------------
no       | I
ch_title | Databases
txt      | We start our acquaintance with the
           fascinating world of databases
-[ RECORD 2 ]-----------------------------------------
no       | II
ch_title | First Steps
txt      | Getting more fascinated with the world of
           databases
-[ RECORD 3 ]-----------------------------------------
no       | I
ch_title | Local Networks
txt      | Here we start our adventurous journey
           through the intriguing world of networks

Now let’s find some information in our database with the help of traditional SQL means (using the LIKE operator):

test=# SELECT ch_no AS no, ch_title, txt
FROM course_chapters
WHERE txt LIKE '%fascination%' \gx

It’s easy to guess the result: 0 rows. The LIKE operator sees no connection between “fascination” and the words “fascinating” and “fascinated” present in the text.

The query

test=# SELECT ch_no AS no, ch_title, txt
FROM course_chapters
WHERE txt LIKE '%fascinated%' \gx

will return the row from chapter II (but not from chapter I, where the adjective “fascinating” is used):

-[ RECORD 1 ]-----------------------------------------
no       | II
ch_title | First Steps
txt      | Getting more fascinated with the world of
           databases

PostgreSQL provides the ILIKE operator, which allows us not to worry about letter cases; otherwise, you would also have to take uppercase and lowercase letters into account. True, there are also regular expressions (search patterns), and setting them up is a truly engaging task, little short of art, but sometimes you just want a tool that can simply do the job. So let’s add an additional column to the course_chapters table; it will have a special type called tsvector:

test=# ALTER TABLE course_chapters
    ADD txtvector tsvector;
test=# UPDATE course_chapters
    SET txtvector = to_tsvector('english',txt);
test=# SELECT txtvector
FROM course_chapters \gx

-[ RECORD 1 ]-----------------------------------------
txtvector | 'acquaint':4 'databas':10 'fascin':7
            'start':2 'world':9
-[ RECORD 2 ]-----------------------------------------
txtvector | 'databas':8 'fascin':3 'get':1 'world':6
-[ RECORD 3 ]-----------------------------------------
txtvector | 'adventur':5 'intrigu':9 'journey':6
            'network':12 'start':3 'world':10

As we can see, the rows have changed:

• Words are reduced to their root forms (lexemes).

• Numbers have appeared. They indicate the word position in the text.

• There are no prepositions included (and neither there would be any conjunctions or other parts of the sentence that are unimportant for search; they are the so-called stop words).

The search will be even more efficient if it includes chapter titles, which are also given more weight in respect to the rest of the text (it can be done using the setweight function). Let’s modify the table:

test=# UPDATE course_chapters
  SET txtvector =
      setweight(to_tsvector('english',ch_title),'B')
      || ' ' ||
      setweight(to_tsvector('english',txt),'D');
UPDATE 3

test=# SELECT txtvector FROM course_chapters \gx
-[ RECORD 1 ]-----------------------------------------
txtvector | 'acquaint':5 'databas':1B,11 'fascin':8
            'start':3 'world':10
-[ RECORD 2 ]-----------------------------------------
txtvector | 'databas':10 'fascin':5 'first':1B 'get':3
            'step':2B 'world':8

-[ RECORD 3 ]-----------------------------------------
txtvector | 'intrigu':10 'journey':7 'local':1B
            'network':2B,13 'start':5 'world':11

Lexemes are assigned relative weight markers: B and D (possible options are A, B, C, D). We’ll assign real weights when writing queries, which will give us more flexibility.

Fully armed, let’s return to search. The to_tsquery function resembles the to_tsvector function we have seen above: it converts a string to the tsquery data type used in queries.

test=# SELECT ch_title
FROM course_chapters
WHERE txtvector @@
      to_tsquery('english','fascination & database');
  ch_title
-------------
 Databases
 First Steps
( rows)

You can check that the query 'fascinated & database' and its other grammatical variants will return the same result. Here we have used the comparison operator @@, which plays the same role in full-text search as the LIKE operator does in regular search. The syntax of the @@ operator does not allow natural language expressions with spaces, so words in the query are connected by the “and” logical operator.

The english argument indicates the configuration used by PostgreSQL. The configuration defines pluggable dictionaries and the parser, which splits the phrase into separate lexemes.

Despite their name, dictionaries enable all kinds of lexeme transformations. For example, a simple stemmer dictionary like snowball, which is used by default, reduces the word to its unchangeable part; it allows search to ignore word endings in queries. You can also plug in other dictionaries, for example:

• regular dictionaries like ispell, myspell, or hunspell, which can better handle word morphology

• dictionaries of synonyms

• thesaurus

• unaccent, which can remove diacritics from letters

Thanks to assigned weights, the displayed search results are ranked:

test=# SELECT ch_title,
     ts_rank_cd('{0.1, 0.0, 1.0, 0.0}', txtvector, q)
FROM course_chapters,
     to_tsquery('english','Databases') q
WHERE txtvector @@ q
ORDER BY ts_rank_cd DESC;

  ch_title   | ts_rank_cd
-------------+------------
 Databases   |        1.1
 First Steps |        0.1
( rows)

The {0.1, 0.0, 1.0, 0.0} array sets the weights. It is an optional argument of the ts_rank_cd function. By default, array {0.1, 0.2, 0.4, 1.0} corresponds to D, C, B, A. The word’s weight affects ranking of the returned row.

In the final experiment, let’s modify the display format. Suppose we would like to highlight the found words in the html page using the bold type. The ts_headline function specifies the symbols used to highlight words and sets the minimum and maximum number of words displayed in a single line:

test=# SELECT ts_headline(
  'english',
  txt,
  to_tsquery('english', 'world'),
  'StartSel=<b>, StopSel=</b>,
  MaxWords=50, MinWords=5'
)
FROM course_chapters
WHERE to_tsvector('english', txt) @@
      to_tsquery('english', 'world');

-[ RECORD 1 ]-----------------------------------------
ts_headline | with the fascinating <b>world</b> of
              databases
-[ RECORD 2 ]-----------------------------------------
ts_headline | with the <b>world</b> of databases
-[ RECORD 3 ]-----------------------------------------
ts_headline | through the intriguing <b>world</b> of
              networks

To speed up full-text search, special indexes are used: GiST, GIN, and RUM, which are different from regular database indexes. However, like many other useful full-text search features, they fall outside the scope of this guide.

To learn more about full-text search, see the documentation: www.postgrespro.com/doc/textsearch.

Using JSON and JSONB

SQL-based relational databases were originally designed with a strong emphasis on data consistency and security, at a time when data volumes were far smaller than they are today. When NoSQL databases appeared, it caused concern in the community: lack of strict consistency support and a much simpler data structure (at first, it was simply a storage of key–value pairs) provided a remarkable search speedup. Actively using parallel computations, they could process unprecedented volumes of information and were easy to scale.

Once the initial shock had passed, it became clear that such a simple structure was insufficient for most practical tasks. Composite keys were introduced, and then groups of keys appeared. To remain competitive, relational database systems began incorporating features commonly associated with NoSQL.

Since changing the database schema in a relational database incurs high costs, a new JSON data type came in handy. Having a hierarchical structure similar to XML, it was first targeted at JavaScript development (hence JS in the title), including AJAX application development. JSON’s flexibility allows developers to store and manage data with dynamic, unpredictable structures without requiring database schema modifications.

Suppose we need to enter personal data into the students demo database. We conducted a survey and collected the information from professors. Some questions in the questionnaire are optional, while other questions include the“add more information at your discretion” and “other” fields. If we followed the traditional approach, the information that does not fit the current structure would require adding multiple new tables and columns with lots of empty fields. As the dataset grows, the whole database may have to be refactored.

We can solve this problem using json or jsonb types. The jsonb type, which appeared after json, stores data in a compact binary form and, unlike json, supports indexes, which can sometimes speed up search by an order of magnitude.

Let’s create a table with JSON objects:

test=# CREATE TABLE student_details(
  de_id int,
  s_id int REFERENCES students(s_id),
  details json,
  CONSTRAINT pk_d PRIMARY KEY(s_id, de_id)
);

test=# INSERT INTO student_details
  (de_id, s_id, details)
VALUES
(1, 1451,
'{ "merits": "none",
   "flaws":
   "immoderate ice cream consumption",
   "status" : "expelled"
 }'),
 (2, 1432,
'{ "hobbies":
     { "guitarist":
         { "band": "Postgressors",
           "guitars":["Strat","Telec"]
         }
     }
 }'
),
(3, 1556,
'{ "hobbies": "cosplay",
     "merits":
      { "mother-of-five":
          {  "Basil": "m", "Simon": "m", "Lucie": "f",
             "Mark": "m",  "Alex": "unknown"
          }
      }
 }'
);

Let’s verify that the data has been correctly inserted. For convenience, we will join the student_details and students tables using the WHERE clause, as the new table does not contain students’ names:

test=# SELECT s.name, sd.details
FROM student_details sd, students s
WHERE s.s_id = sd.s_id \gx
-[ RECORD 1 ]--------------------------------------
name    | Anna
details | { "merits": "none",                      +
        |   "flaws":                               +
        |   "immoderate ice cream consumption",    +
        |   "status" : "expelled"                  +
        | }
-[ RECORD 2 ]--------------------------------------
name    | Victor
details | { "hobbies":                             +
        |      { "guitarist":                      +
        |          { "band": "Postgressors",       +
        |            "guitars":["Strat","Telec"]   +
        |          }                               +
        |      }                                   +
        | }
-[ RECORD 3 ]--------------------------------------
name    | Nina
details | { "hobbies": "cosplay",                  +
        |   "merits":                              +
        |      { "mother-of-five":                 +
        |          { "Basil": "m",                 +
        |            "Simon": "m",                 +
        |            "Lucie": "f",                 +
        |            "Mark": "m",                  +
        |            "Alex": "unknown"             +
        |          }                               +
        |      }                                   +
        | }

Suppose we are interested in entries that hold information about the students’ merits. Let’s access the values of the “merits” key using a special operator ->>:

test=# SELECT s.name, sd.details
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details ->> 'merits' IS NOT NULL \gx

-[ RECORD 1 ]--------------------------------------
name    | Anna
details | { "merits": "none",                     +
        |   "flaws":                              +
        |   "immoderate ice cream consumption",   +
        |   "status" : "expelled"                 +
        | }
-[ RECORD 2 ]--------------------------------------
name    | Nina
details | { "hobbies": "cosplay",                 +
        |   "merits":                             +
        |     { "mother-of-five":                 +
        |         { "Basil": "m",                 +
        |           "Simon": "m",                 +
        |           "Lucie": "f",                 +
        |           "Mark": "m",                  +
        |           "Alex": "unknown"             +
        |         }                               +
        |     }                                   +
        | }

We have seen that the two entries are related to Anna and Nina’s merits, but the result is misleading, as Anna’s merits are actually “none.” Let’s fix the query:

test=# SELECT s.name, sd.details
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details ->> 'merits' IS NOT NULL
AND   sd.details ->> 'merits' != 'none';

The new query only returns Nina, whose merits are real.

However, this method is not always effective. Let’s attempt to retrieve the guitars that Victor plays:

test=# SELECT sd.de_id, s.name, sd.details
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details ->> 'guitars' IS NOT NULL \gx

This query won’t return anything. It’s because the corresponding key–value pair is located inside the JSON hierarchy, nested into the pairs of a higher level:

name    | Victor
details | { "hobbies":                             +
        |      { "guitarist":                      +
        |          { "band": "Postgressors",       +
        |            "guitars":["Strat","Telec"]   +
        |          }                               +
        |      }                                   +
        | }

To retrieve the list of guitars, let’s use the #> operator and go down the hierarchy, starting from “hobbies”:

test=# SELECT sd.de_id, s.name,
      sd.details #> '{hobbies,guitarist,guitars}'
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details #> '{hobbies,guitarist,guitars}'
      IS NOT NULL;

We can see that Victor is a fan of Fender:

 de_id |  name  |     ?column?
-------+--------+-------------------
       | Victor | ["Strat","Telec"]

The json type has a younger sibling: jsonb. The letter “b” implies the binary (rather than text) format of data storage and structure, which can often result in faster search. Nowadays, jsonb is used much more frequently than json.

test=# ALTER TABLE student_details
ADD details_b jsonb;

test=# UPDATE student_details
SET details_b = to_jsonb(details);
test=# SELECT de_id, details_b
FROM student_details \gx
-[ RECORD 1 ]--------------------------------------
de_id     | 1
details_b | {"flaws": "immoderate ice cream
            consumption", "merits": "none",
            "status": "expelled"}
-[ RECORD 2 ]--------------------------------------
de_id     | 2
details_b | {"hobbies": {"guitarist": {"guitars":
            ["Strat", "Telec"], "band":
            "Postgressors"}}}
-[ RECORD 3 ]--------------------------------------
de_id     | 3
details_b | {"hobbies": "cosplay", "merits":
            {"mother-of-five": {"Basil": "m",
            "Lucie": "f", "Alex": "unknown",
            "Mark": "m", "Simon": "m"}}}

Besides the notation differences, the order of values has changed: Alex, who lacks additional information, now appears before Mark. It’s not a disadvantage of jsonb as compared to json, it’s simply its data storage specifics.

The jsonb type is supported by a larger number of operators than json. A most useful one is the “contains” operator @>. It works similar to the #> operator for json.

For example, let’s find the entry that mentions Lucie, one of the mother-of-five’s children:

test=# SELECT s.name,
      jsonb_pretty(sd.details_b) json
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details_b @>
      '{"merits":{"mother-of-five":{}}}' \gx

-[ RECORD 1 ]-------------------------------------
name | Nina
json | {                                          +
     |     "merits": {                            +
     |         "mother-of-five": {                +
     |             "Alex": "unknown",             +
     |             "Mark": "m",                   +
     |             "Basil": "m",                  +
     |             "Lucie": "f",                  +
     |             "Simon": "m"                   +
     |         }                                  +
     |     },                                     +
     |     "hobbies": "cosplay"                   +
     | }

We have used the jsonb_pretty() function, which formats the output of the jsonb type.

Alternatively, you can use the jsonb_each() function, which expands key–value pairs:

test=# SELECT s.name,
      jsonb_each(sd.details_b)
FROM  student_details sd, students s
WHERE s.s_id = sd.s_id
AND   sd.details_b @>
      '{"merits":{"mother-of-five":{}}}' \gx

-[ RECORD 1 ]-------------------------------------
name       | Nina
jsonb_each | (hobbies,"""cosplay""")
-[ RECORD 2 ]-------------------------------------
name       | Nina
jsonb_each | (merits,"{""mother-of-five"":
             {""Alex"": ""unknown"", ""Mark"":
             ""m"", ""Basil"": ""m"", ""Lucie"":
             ""f"", ""Simon"": ""m""}}")

Note that the name of Nina’s child is replaced by an empty space {} in the query. This syntax adds flexibility to the process of application development.

But what’s more important, jsonb allows you to create indexes that support the @> operator, its inverse <@, and many other ones (the GIN index is typically the most efficient). The json type does not support indexes, so for high-load applications it is usually recommended to use jsonb.

To learn more about json and jsonb data types and their associated functions, see the PostgreSQL documentation at postgrespro.com/doc/datatype-json and postgrespro.com/doc/functions-json.

When the SQL:2016 standard was published, which included the SQL/JSON Path language, Postgres Professional developed its implementation, providing the jsonpath type. It was later committed to PostgreSQL 12.

• $.a.b.c replaces the 'a'->'b'->'c' syntax that had to be used in PostgreSQL 11 or lower.

• The $ symbol represents the current context element, that is, the JSON fragment that has to be parsed.

• @ represents the current context element in filter expressions. All the paths available in the $ expression are searched.

• * is a wildcard symbol. In expressions with $ and @ it denotes any value of the path taking the hierarchy into account.

• ** is a wildcard that can denote any part of the path in expressions with $ or @, without taking the hierarchy into account. It comes in handy if you don’t know the exact nesting level of the elements.

• The ? operator is used to create a filter similar to WHERE. For example: $.a.b.c ? (@.x > 10).

To find cosplay enthusiasts using the jsonb_path_query() function, you can write the following query:

test=# SELECT s_id, jsonb_path_query(
  details::jsonb,
  '$.hobbies ? (@ == "cosplay")'
)
FROM student_details;

 s_id | jsonb_path_query
------+------------------
 1556 | "cosplay"
(1 row)

This query searches only through the JSON branch that begins with the “hobbies” key, checking whether the corresponding value equals “cosplay.” But if we replace “cosplay” with “guitarist,” the query won’t return anything because “guitarist” is used in our table as a key, not as a value of the nested element.

Queries can operate on two hierarchical levels: one inside the path expression, which defines the search area, and the other inside the filter expression, which matches the results with the specified condition. It means there are different ways to reach the same goal.

For example, the query

test=# SELECT s_id, jsonb_path_query(
  details::jsonb,
  '$.hobbies.guitarist.band?(@=="Postgressors")'
)
FROM student_details;

and the query

test=# SELECT s_id, jsonb_path_query(
  details::jsonb,
  '$.hobbies.guitarist?(@.band=="Postgressors").band'
)
FROM student_details;

return the same result:

 s_id | jsonb_path_query
------+------------------
 1432 | "Postgressors"
(1 row)

In the first example, we defined a filter expression for each entry within the “hobbies.guitarist.band” branch. If we take a look at the JSON itself, we can see that this branch has only one value: “Postgressors.” So there was actually nothing to filter out. In the second example, the filter is applied one step higher, so we have to specify the path to the“group”within the filter expression; otherwise, the filter won’t find any values. If we use such syntax, we have to know the JSON hierarchy in advance. But what if we don’t know the hierarchy?

In this case, we can use the ** wildcard. It is an extremely useful feature! Suppose we are not sure what a “Strat” is: whether it’s a high-altitude balloon, a guitar, or a member of the highest social stratum. But we have to find out whether we have this word in our table at all. Previously, it would have required a complex search through the JSON document (unless we converted jsonb to text). Now you can simply run the following query:

test=# SELECT s_id, jsonb_path_exists(
  details::jsonb, '$.** ? (@ == "Strat")'
)
FROM student_details;

 s_id | jsonb_path_exists
------+-------------------
 1451 | f
 1432 | t
 1556 | f
 1451 | f
(4 rows)

You can learn more about SQL/JSON Path capabilities in the documentation (postgrespro.com/doc/datatype-json#DATATYPE-JSONPATH) and in the article “JSONPath in PostgreSQL: committing patches and selecting apartments” (habr.com/en/company/postgrespro/blog/500440/).

The SQL:2016 Standard defines a few more convenient features and expressions for working with JSON. Eventually, they make their way into PostgreSQL.We will discuss some of them here; for the full list, see the documentation: postgrespro.com/doc/functions-json.

Let’s get back to our friends Anna, Victor and Nina. They took part in a poll to determine what TV shows the younger generation likes. The results are stored in a table.

test=# CREATE TABLE tv_series(
  se_id int,

s_id int REFERENCES students(s_id),

  se_details jsonb,
  CONSTRAINT pk_se PRIMARY KEY(s_id, se_id)
);

test=# INSERT INTO tv_series (se_id, s_id, se_details)
VALUES
(1, 1451,
'{"title": ["Game of Thrones",
            "Peaky Blinders"]}'
),

(2, 1432,
'{"title": "Babylon 5"}'
),
(3, 1556,
'{"title": ["Twin Peaks",
            "Peaky Blinders"]}'
);

Database engineers analyze the results. They’ve got a powerful toolkit: aggregation functions, check constraints, an assortment of json(b) tools.

The arrays should first be split into separate elements (unnest). It can be done with jsonb_array_elements, but there’s an issue:

text=# SELECT jsonb_array_elements(
  se_details->'title'
)
FROM tv_series;

ERROR:  cannot extract elements from a scalar

Naturally, the data from the questionnaires has been put into the database carelessly: Student 1432 does not have a corresponding array (of one element only, but still). Just to make sure, use IS [NOT] JSON ARRAY:

text=# SELECT se_id, se_details
FROM tv_series
WHERE se_details->'title' IS NOT JSON ARRAY \gx

-[ RECORD 1 ]----------------------------------
se_id      | 2
se_details | {"title": "Babylon 5"}

We can fix the type with jsonb_set, which can modify jsonb structure:

text=# UPDATE tv_series
SET se_details = jsonb_set(
  se_details,
  '{title}',
  '["Babylon 5"]'
)
WHERE se_id = 2;

Now:

test=# SELECT DISTINCT jsonb_array_elements(
  se_details->'title'
)
FROM tv_series;

 jsonb_array_elements
----------------------
 "Twin Peaks"
 "Babylon 5"
 "Peaky Blinders"
 "Game of Thrones"
(4 rows)

Here is our final list. Note that IS JSON ARRAY belongs to a group of predicates, together with IS JSON SCALAR, IS JSON OBJECT and the more general IS JSON. See the documentation for more: postgrespro.com/doc/functions-json.

Integration with External Systems

Real-world applications are not isolated, and they often have to send data to each other. Such interactions can be implemented at the application level, for example, using web services or file exchange, or you can rely on the database functionality for this purpose.

PostgreSQL supports the ISO/IEC 9075-9 standard (SQL/MED, Management of External Data), which defines access to external data sources from SQL via a special mechanism of foreign data wrappers.

The idea is to access external (foreign) data as if it were located in regular PostgreSQL tables. It requires creating foreign tables, which do not contain any data themselves and only redirect all queries to an external data source. This approach facilitates application development, as it allows to abstract from specifics of a particular external source.

Creating a foreign table involves several steps.

1. The CREATE FOREIGN DATA WRAPPER command plugs in a library for working with a particular data source.

2. The CREATE SERVER command defines a foreign server. You should usually specify such connection parameters as host name, port number, and database name.

3. The CREATE USER MAPPING command provides username mapping since different PostgreSQL users can connect to one and the same foreign source on behalf of different external users.

4. The CREATE FOREIGN TABLE command creates foreign tables for the specified external tables and views, while IMPORT FOREIGN SCHEMA allows to import descriptions of some or all tables from the external schema.

Below we’ll discuss PostgreSQL integration with the most popular databases: Oracle, MySQL, SQL Server, and PostgreSQL itself. But first we need to install the libraries required for working with these databases.

Installing Extensions

The PostgreSQL distribution includes two foreign data wrappers: postgres_fdw and file_fdw. The first one is designed for working with external PostgreSQL databases, while the second one works with files on a server. Besides, the community develops and supports various libraries that provide access to many popular databases. To get the full list, take a look at pgxn.org/tag/fdw.

Foreign data wrappers for Oracle, MySQL, and SQL Server are available as extensions:

• Oracle — github.com/laurenz/oracle_fdw

• MySQL — github.com/EnterpriseDB/mysql_fdw

• SQL Server — github.com/tds-fdw/tds_fdw

Follow the instructions on these web pages to build and install these extensions. If all goes well, you will see the corresponding foreign data wrappers in the list of available extensions. For example, for oracle_fdw:

test=# SELECT name, default_version
FROM pg_available_extensions
WHERE name = 'oracle_fdw' \gx

-[ RECORD 1 ]---+-----------
name            | oracle_fdw
default_version | 1.2

Oracle

First, let’s create an extension, which will add a foreign data wrapper:

test=# CREATE EXTENSION oracle_fdw;

CREATE EXTENSION

Check that the corresponding wrapper has been added:

test=# \dew

List of foreign-data wrappers
-[ RECORD 1 ]-------------------
Name      | oracle_fdw
Owner     | postgres
Handler   | oracle_fdw_handler
Validator | oracle_fdw_validator

The next step is setting up a foreign server. In the OPTIONS clause, you have to specify the dbserver option, which defines connection parameters for the Oracle instance: server name, port number, and instance name.

test=# CREATE SERVER oracle_srv
  FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver '//localhost:1521/orcl');

CREATE SERVER

The PostgreSQL user postgres will be connecting to the Oracle instance as scott.

test=# CREATE USER MAPPING FOR postgres
  SERVER oracle_srv
  OPTIONS (user 'scott', password 'tiger');

CREATE USER MAPPING

We’ll import foreign tables into a separate schema. Let’s create it:

test=# CREATE SCHEMA oracle_hr;

CREATE SCHEMA

Now let’s import some foreign tables. We’ll do it for just two popular tables, dept and emp:

test=# IMPORT FOREIGN SCHEMA "SCOTT"
  LIMIT TO (dept, emp)
  FROM SERVER oracle_srv
  INTO oracle_hr;

IMPORT FOREIGN SCHEMA

Note that Oracle data dictionary stores object names in uppercase, while PostgreSQL system catalog saves them in lowercase. When working with external data in PostgreSQL, you have to double-quote uppercase Oracle schema names to avoid their conversion to lowercase.

Let’s view the list of foreign tables:

test=# \det oracle_hr.*

     List of foreign tables
  Schema   | Table |   Server
-----------+-------+------------
oracle_hr  | dept  | oracle_srv
oracle_hr  | emp   | oracle_srv
(2 rows)

Now run the following queries on the foreign tables to access the external data:

test=# SELECT * FROM oracle_hr.emp LIMIT 1 \gx

-[ RECORD 1 ]-------------------
empno    | 7369
ename    | SMITH
job      | CLERK
mgr      | 7902
hiredate | 1980-12-17
sal      | 800.00
comm     |
deptno   | 20

Write operations on external data are also allowed:

test=# INSERT INTO oracle_hr.dept(deptno, dname, loc)
  VALUES (50, 'EDUCATION', 'MOSCOW');

INSERT 0 1

test=# SELECT * FROM oracle_hr.dept;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | EDUCATION  | MOSCOW
(5 rows)

MySQL

Create an extension for the required foreign data wrapper:

test=# CREATE EXTENSION mysql_fdw;

CREATE EXTENSION

The foreign server for the external instance is defined by the host and port parameters:

test=# CREATE SERVER mysql_srv
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host 'localhost', port '3306');
CREATE SERVER

We are going to establish connections on behalf of a MySQL superuser:

test=# CREATE USER MAPPING FOR postgres
  SERVER mysql_srv
  OPTIONS (username 'root', password 'p@ssw0rd');
CREATE USER MAPPING

The wrapper supports the IMPORT FOREIGN SCHEMA command, but we can also create a foreign table manually:

test=# CREATE FOREIGN TABLE employees (
  emp_no      int,
  birth_date  date,
  first_name  varchar(14),
  last_name   varchar(16),
  gender      varchar(1),
  hire_date   date)
SERVER mysql_srv
  OPTIONS (dbname 'employees',
           table_name 'employees');
CREATE FOREIGN TABLE

Check the result:

test=# SELECT * FROM employees LIMIT 1 \gx
-[ RECORD 1 ]----------
emp_no     | 10001
birth_date | 1953-09-02
first_name | Georgi
last_name  | Facello
gender     | M
hire_date  | 1986-06-26

Just like the Oracle wrapper, mysql_fdw allows both read and write operations.

SQL Server

Create an extension for the required foreign data wrapper:

test=# CREATE EXTENSION tds_fdw;
CREATE EXTENSION

Create a foreign server:

test=# CREATE SERVER sqlserver_srv
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (servername 'localhost', port '1433',
           database 'AdventureWorks');
CREATE SERVER

The required connection information is the same: you have to provide the host name, the port number, and the database name. But the OPTIONS clause takes different parameters as compared to oracle_fdw and mysql_fdw.

We are going to establish connections on behalf of an SQL Server superuser:

test=# CREATE USER MAPPING FOR postgres
  SERVER sqlserver_srv
  OPTIONS (username 'sa', password 'p@ssw0rd');
CREATE USER MAPPING

Let’s create a separate schema for foreign tables:

test=# CREATE SCHEMA sqlserver_hr;
CREATE SCHEMA

Import the whole HumanResources schema into the created PostgreSQL schema:

test=# IMPORT FOREIGN SCHEMA HumanResources
  FROM SERVER sqlserver_srv
  INTO sqlserver_hr;
IMPORT FOREIGN SCHEMA

You can display the list of imported tables using the \det command, or find them in the system catalog by running the following query:

test=# SELECT ft.ftrelid::regclass AS "Table"
FROM pg_foreign_table ft;
                 Table
----------------------------------------
sqlserver_hr.Department
sqlserver_hr.Employee
sqlserver_hr.EmployeeDepartmentHistory
sqlserver_hr.EmployeePayHistory
sqlserver_hr.JobCandidate
sqlserver_hr.Shift
(6 rows)

Object names are case-sensitive, so they should be enclosed in double quotes in PostgreSQL queries:

test=# SELECT "DepartmentID", "Name", "GroupName"
FROM sqlserver_hr."Department"
LIMIT 4;
 DepartmentID |    Name     |        GroupName
--------------+-------------+-------------------------
            1 | Engineering | Research and Development
            2 | Tool Design | Research and Development
            3 | Sales       | Sales and Marketing
            4 | Marketing   | Sales and Marketing
(4 rows)

Currently tds_fdw supports only reading; write operations are not allowed.

PostgreSQL

Create an extension and a wrapper:

test=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

We are going to connect to another database of the same server instance, so we only have to provide the dbname parameter when creating a foreign server. Other parameters (such as host, port, etc.) can be omitted.

test=# CREATE SERVER postgres_srv
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'demo');
CREATE SERVER

There is no need to specify the password if you create a user mapping within a single cluster:

test=# CREATE USER MAPPING FOR postgres
  SERVER postgres_srv
  OPTIONS (user 'postgres');
CREATE USER MAPPING

Import all tables and views of the bookings schema:

test=# IMPORT FOREIGN SCHEMA bookings
  FROM SERVER postgres_srv
  INTO public;
IMPORT FOREIGN SCHEMA

Check the result:

test=# SELECT * FROM bookings LIMIT 3;

 book_ref |       book_date        | total_amount
----------+------------------------+--------------
 000004   | 2015-10-12 14:40:00+03 | 55800.00
 00000F   | 2016-09-02 02:12:00+03 | 265700.00
 000010   | 2016-03-08 18:45:00+03 | 50900.00
 000012   | 2017-07-14 09:02:00+03 | 37900.00
 000026   | 2016-08-30 11:08:00+03 | 95600.00
(5 rows)

To learn more about postgres_fdw, see the documentation: postgrespro.com/doc/postgres-fdw.

Foreign data wrappers are also worth mentioning as the community considers them to be the basis for built-in sharding in PostgreSQL. Sharding is similar to partitioning: they both use a particular criterion to split a table into several parts that are stored independently. The difference is that partitions are stored on the same server, while shards are located on different ones. Partitioning has been available in PostgreSQL for quite a long time. Starting from version 10, this mechanism is being actively developed, and many useful features have already been added: declarative syntax, dynamic partition pruning, support for parallel operations, and other miscellaneous enhancements. You can also use foreign tables as partitions, which virtually turns partitioning into sharding.

However, significant work remains before sharding is fully practical:

• Consistency is not guaranteed: external data is accessed in separate local transactions rather than in a single distributed one.

• You can’t duplicate the same data on different servers to enhance fault tolerance.

• All actions required to create tables on shards and the corresponding foreign tables have to be done manually.

These challenges are already addressed in Shardman, a new tool developed by Postgres Professional (postgrespro.ru/products/shardman).

Another extension included into the distribution for working with PostgreSQL databases is dblink. It allows you to explicitly manage connections (to connect and disconnect), execute queries, and get the results asynchronously: postgrespro.com/doc/dblink.