# Additional Features > Try PostgreSQL full-text search, JSON and JSONB, and foreign data wrappers with worked SQL examples. --- LLMS index: [llms.txt](/en/llms.txt) --- ## Full-Text Search {#intro-section-9-1} 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: ```psql 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) ); ``` ```sql CREATE TABLE ``` Now let’s enter the text of the first lectures for our courses CS301 and CS305: ```psql 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: ```psql 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): ```psql 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 ```psql 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): ```text -[ 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`: ```psql 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 ``` ```text -[ 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: ```psql test=# UPDATE course_chapters SET txtvector = setweight(to_tsvector('english',ch_title),'B') || ' ' || setweight(to_tsvector('english',txt),'D'); UPDATE 3 ``` ```psql 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 ``` ```text -[ 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. ```psql 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: ```psql 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; ``` ```text 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: ```psql test=# SELECT ts_headline( 'english', txt, to_tsquery('english', 'world'), 'StartSel=, StopSel=, MaxWords=50, MinWords=5' ) FROM course_chapters WHERE to_tsvector('english', txt) @@ to_tsquery('english', 'world'); ``` ```text -[ RECORD 1 ]----------------------------------------- ts_headline | with the fascinating world of databases -[ RECORD 2 ]----------------------------------------- ts_headline | with the world of databases -[ RECORD 3 ]----------------------------------------- ts_headline | through the intriguing world 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 {#intro-section-9-2} 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: ```psql 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) ); ``` ```psql 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: ```psql 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 `->>`: ```psql 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 ``` ```text -[ 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: ```psql 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: ```psql 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: ```text 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”: ```psql 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: ```text 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`. ```psql test=# ALTER TABLE student_details ADD details_b jsonb; ``` ```psql 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: ```psql 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 ``` ```text -[ 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: ```psql 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 ``` ```text -[ 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: ```psql test=# SELECT s_id, jsonb_path_query( details::jsonb, '$.hobbies ? (@ == "cosplay")' ) FROM student_details; ``` ```text 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 ```psql test=# SELECT s_id, jsonb_path_query( details::jsonb, '$.hobbies.guitarist.band?(@=="Postgressors")' ) FROM student_details; ``` and the query ```psql test=# SELECT s_id, jsonb_path_query( details::jsonb, '$.hobbies.guitarist?(@.band=="Postgressors").band' ) FROM student_details; ``` return the same result: ```text 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: ```psql test=# SELECT s_id, jsonb_path_exists( details::jsonb, '$.** ? (@ == "Strat")' ) FROM student_details; ``` ```text 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. ```psql test=# CREATE TABLE tv_series( se_id int, ``` `s_id int REFERENCES students(s_id),` ```text se_details jsonb, CONSTRAINT pk_se PRIMARY KEY(s_id, se_id) ); ``` ```psql test=# INSERT INTO tv_series (se_id, s_id, se_details) VALUES (1, 1451, '{"title": ["Game of Thrones", "Peaky Blinders"]}' ), ``` ```text (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: ```psql text=# SELECT jsonb_array_elements( se_details->'title' ) FROM tv_series; ``` ```text 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`: ```psql text=# SELECT se_id, se_details FROM tv_series WHERE se_details->'title' IS NOT JSON ARRAY \gx ``` ```text -[ RECORD 1 ]---------------------------------- se_id | 2 se_details | {"title": "Babylon 5"} ``` We can fix the type with `jsonb_set`, which can modify `jsonb` structure: ```psql text=# UPDATE tv_series SET se_details = jsonb_set( se_details, '{title}', '["Babylon 5"]' ) WHERE se_id = 2; ``` Now: ```psql test=# SELECT DISTINCT jsonb_array_elements( se_details->'title' ) FROM tv_series; ``` ```text 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 {#intro-section-9-3} 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 {#intro-subsection-9-3-1} 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`: ```psql test=# SELECT name, default_version FROM pg_available_extensions WHERE name = 'oracle_fdw' \gx ``` ```text -[ RECORD 1 ]---+----------- name | oracle_fdw default_version | 1.2 ``` ### Oracle {#intro-subsection-9-3-2} First, let’s create an extension, which will add a foreign data wrapper: ```psql test=# CREATE EXTENSION oracle_fdw; ``` ```sql CREATE EXTENSION ``` Check that the corresponding wrapper has been added: ```psql test=# \dew ``` ```text 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. ```psql test=# CREATE SERVER oracle_srv FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//localhost:1521/orcl'); ``` ```sql CREATE SERVER ``` The PostgreSQL user `postgres` will be connecting to the Oracle instance as `scott`. ```psql test=# CREATE USER MAPPING FOR postgres SERVER oracle_srv OPTIONS (user 'scott', password 'tiger'); ``` ```sql CREATE USER MAPPING ``` We’ll import foreign tables into a separate schema. Let’s create it: ```psql test=# CREATE SCHEMA oracle_hr; ``` ```sql CREATE SCHEMA ``` Now let’s import some foreign tables. We’ll do it for just two popular tables, `dept` and `emp`: ```psql test=# IMPORT FOREIGN SCHEMA "SCOTT" LIMIT TO (dept, emp) FROM SERVER oracle_srv INTO oracle_hr; ``` ```text 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: ```psql test=# \det oracle_hr.* ``` ```text 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: ```psql test=# SELECT * FROM oracle_hr.emp LIMIT 1 \gx ``` ```text -[ 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: ```psql test=# INSERT INTO oracle_hr.dept(deptno, dname, loc) VALUES (50, 'EDUCATION', 'MOSCOW'); ``` ```sql INSERT 0 1 ``` ```psql test=# SELECT * FROM oracle_hr.dept; ``` ```text deptno | dname | loc --------+------------+---------- 10 | ACCOUNTING | NEW YORK 20 | RESEARCH | DALLAS 30 | SALES | CHICAGO 40 | OPERATIONS | BOSTON 50 | EDUCATION | MOSCOW (5 rows) ``` ### MySQL {#intro-subsection-9-3-3} Create an extension for the required foreign data wrapper: ```psql test=# CREATE EXTENSION mysql_fdw; ``` ```sql CREATE EXTENSION ``` The foreign server for the external instance is defined by the `host` and `port` parameters: ```psql 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: ```psql 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: ```psql 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: ```psql 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 {#intro-subsection-9-3-4} Create an extension for the required foreign data wrapper: ```psql test=# CREATE EXTENSION tds_fdw; CREATE EXTENSION ``` Create a foreign server: ```psql 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: ```psql 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: ```psql test=# CREATE SCHEMA sqlserver_hr; CREATE SCHEMA ``` Import the whole `HumanResources` schema into the created PostgreSQL schema: ```psql 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: ```psql 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: ```psql 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 {#intro-subsection-9-3-5} Create an extension and a wrapper: ```psql 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. ```psql 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: ```psql test=# CREATE USER MAPPING FOR postgres SERVER postgres_srv OPTIONS (user 'postgres'); CREATE USER MAPPING ``` Import all tables and views of the `bookings` schema: ```psql test=# IMPORT FOREIGN SCHEMA bookings FROM SERVER postgres_srv INTO public; IMPORT FOREIGN SCHEMA ``` Check the result: ```psql test=# SELECT * FROM bookings LIMIT 3; ``` ```text 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.]()