RDF Triplestore Foreign Data Wrapper for PostgreSQL (rdf_fdw)

rdf_fdw is a PostgreSQL Foreign Data Wrapper that enables seamless integration with RDF triplestores via SPARQL endpoints. It supports querying RDF data using SQL, with advanced features including pushdown of SQL clauses (WHERE, LIMIT, ORDER BY, DISTINCT), data modification operations (INSERT, UPDATE, DELETE), and built-in implementations of SPARQL 1.1 functions. The extension introduces a custom rdfnode data type for native RDF term handling and provides tools for prefix management, making it easier to work with RDF vocabularies directly from PostgreSQL.

How rdf_fdw works: SQL is translated to SPARQL, sent to the endpoint, and results return as rows

Index

Requirements

PostgreSQL: version 9.5 or higher.
libxml2: version 2.5.0 or higher.
libcurl: version 7.74.0 or higher.
pkg-config: pkg-config 0.29.2 or higher.
gcc and make to compile the code.

In an Ubuntu environment you can install all dependencies with the following command:

apt-get install -y make gcc postgresql-server-dev-18 libxml2-dev libcurl4-gnutls-dev pkg-config

[!NOTE]
postgresql-server-dev-18 only installs the libraries for PostgreSQL 18. Change it if you’re using another PostgreSQL version.

Quick Start

Ensure pg_config is properly set before running make. This executable is typically found in your PostgreSQL installation’s bin directory.

$ cd rdf_fdw
$ make

After compilation, install the Foreign Data Wrapper:

$ make install

Then, create the extension in PostgreSQL:

CREATE EXTENSION rdf_fdw;

To install a specific version, use:

CREATE EXTENSION rdf_fdw WITH VERSION '3.1';

To run the predefined regression tests:

$ make PGUSER=postgres installcheck

This runs the tests that need nothing besides a PostgreSQL server. The remaining groups query triplestores, so they have to be requested explicitly:

Variable	Adds
`INCLUDE_LOCAL_TESTS=1`	tests against the triplestores deployed by `scripts/postgres-env` (Virtuoso, QLever, Fuseki, GraphDB, and a proxy)
`INCLUDE_EXTERNAL_TESTS=1`	tests against public SPARQL endpoints, such as Wikidata and Getty
`INCLUDE_STRESS_TESTS=1`	long running stress tests - these need the local deployment as well
`INCLUDE_DEBUG_TESTS=1`	tests that check debug output - these need the local deployment as well
`INCLUDE_ALL_TESTS=1`	all of the above

$ make PGUSER=postgres INCLUDE_LOCAL_TESTS=1 installcheck

[!WARNING]
rdf_fdw loads all retrieved RDF data into memory before converting it for PostgreSQL. If you expect large data volumes, ensure that PostgreSQL has sufficient memory. When connecting to untrusted or public endpoints, consider setting max_response_size on the SERVER to cap the response body and prevent runaway allocations.

Update Extension

To update the extension’s version you must first build and install the binaries and then run ALTER EXTENSION:

ALTER EXTENSION rdf_fdw UPDATE;

To update to an specific version use UPDATE TO and the full version number, e.g.

ALTER EXTENSION rdf_fdw UPDATE TO '3.1';

Deploy with Docker

A convenient way to build and test rdf_fdw is to create a Docker image that bundles the extension with a matching PostgreSQL base image. The two common approaches are:

Build an image that compiles and installs the extension (reproducible, good for CI).
Mount the source into a container and run make install for iterative development (faster local iterations).

Minimal example Dockerfile for PostgreSQL 18:

FROM postgres:18

RUN apt-get update && \
    apt-get install -y git make gcc postgresql-server-dev-18 libxml2-dev libcurl4-gnutls-dev pkg-config

RUN git clone --branch v3.1 https://github.com/jimjonesbr/rdf_fdw.git && \
    cd rdf_fdw && \
    make -j && \
    make install

Build and run the image:

docker build -t rdf_fdw:18-3.1 .
docker run -d --name rdf_fdw_container -e POSTGRES_HOST_AUTH_METHOD=trust rdf_fdw:18-3.1

Create the extension inside the running container:

docker exec -u postgres rdf_fdw_container psql -d postgres -c "CREATE EXTENSION rdf_fdw;"

To build the latest development version instead of a release, remove --branch v3.1 from the git clone command.

[!NOTE] Do not use POSTGRES_HOST_AUTH_METHOD=trust in production. Check for other POSTGRES_HOST_AUTH_METHOD in the PostgreSQL docker documentation.

Usage

Set up a SERVER that points to a SPARQL endpoint, then declare FOREIGN TABLEs that contain the SPARQL query and map result variables to table columns. Keep foreign-table queries simple to maximise pushdown; see the Pushdown section for details and limitations.

CREATE SERVER

Use CREATE SERVER to register a remote SPARQL endpoint with PostgreSQL - the creator becomes the server owner. The endpoint option (the SPARQL endpoint URL) is required; you may also specify optional connection settings such as timeouts or proxy parameters, if applicable.

Example: register the DBpedia SPARQL endpoint

CREATE SERVER dbpedia
FOREIGN DATA WRAPPER rdf_fdw 
OPTIONS (endpoint 'https://dbpedia.org/sparql');

Server Options

Server Option	Type	Description
`endpoint`	required	SPARQL endpoint URL (required).
`update_url`	optional	URL used for SPARQL UPDATE requests (`INSERT`, `UPDATE`, `DELETE`) when it differs from `endpoint`, e.g. Fuseki’s `/update` service.
`batch_size`	optional	Number of rows to accumulate per SPARQL UPDATE request for DML operations (default `50`). Larger batches reduce network overhead but may exceed endpoint limits. A row does not reach the endpoint until its batch is sent, so anything that reads the table back within the same statement — an `AFTER` row trigger, for instance — needs `batch_size '1'` to see it.
`enable_pushdown`	optional	Enable translation of SQL clauses into SPARQL (default `true`).
`format`	optional	Expected SPARQL result MIME type (default `application/sparql-results+xml`). Set if your endpoint requires a different value.
`http_proxy`	optional	HTTP proxy URL (for authentication, specify `proxy_user` and `proxy_password` in `USER MAPPING`).
`https_proxy`	optional	Same as `http_proxy`, but the connection to the proxy itself is made over TLS. Set one or the other, not both.
`connect_timeout`	optional	Connection timeout in seconds (default `300`).
`request_timeout`	optional	Maximum time in seconds allowed for a complete HTTP request (connect + transfer). `0` disables the limit (default). Unlike `connect_timeout`, this applies to the entire duration of the request, including data transfer.
`connect_retry`	optional	Number of times a request is retried when it gets no response at all, e.g. when the connection fails, or when the endpoint answers HTTP `429`, `502` or `503` to ask to come back later (default `3`). Other HTTP errors are not retried, nor is a proxy refusing the connection with a client error such as `407`. The wait between attempts is what the endpoint asks for in its `Retry-After` header; otherwise it starts at 1 second after a network error and at 5 seconds after an HTTP answer, and doubles each time. A single wait is at most 300 seconds. Modifications are retried as well, which is safe because rdf_fdw only sends `INSERT DATA` and `DELETE DATA`.
`request_max_redirect`	optional	Maximum number of HTTP redirects to follow (default `0`). `0` refuses any redirect; any higher value enables redirection and caps it at that many hops. Credentials are never forwarded to a redirected host.
`request_redirect`	deprecated	Superseded by `request_max_redirect`, which now enables and bounds redirection on its own. Still accepted so that existing servers and dumps keep working: when set to `true` without an explicit `request_max_redirect`, up to `30` redirects are followed. It will be removed in a future major release.
`custom`	optional	Triplestore-specific query parameters appended to the request URL (e.g. `signal_void=on`).
`query_param`	optional	HTTP parameter name that carries the SPARQL query (default `query`).
`prefix_context`	optional	Name of a prefix context whose `PREFIX` entries are prepended to generated SPARQL queries.
`enable_xml_huge`	optional	Enable libxml2’s `XML_PARSE_HUGE` to process very large or deeply nested responses (dangerous; default `false`). It lifts libxml2’s limits on document size and nesting and, before libxml2 2.11, on entity expansion. Use only for trusted endpoints.
`readonly`	optional	Mark the server as read-only (default `false`). When `true`, all `INSERT`, `UPDATE`, and `DELETE` operations on any foreign table backed by this server are rejected before reaching the endpoint. Table-level `readonly` takes precedence over this setting.
`max_response_size`	optional	Maximum allowed HTTP response body size in bytes (default `0` = unlimited). If the response exceeds this limit, the query is aborted with an error. Use this to protect against runaway result sets from untrusted or misbehaving endpoints. Responses are requested compressed (`gzip`, `deflate`, and `br` or `zstd` where libcurl supports them); the limit applies to the decompressed size.
`base_uri`	optional	Base URI used to resolve relative URIs in the `RDF/XML` a `DESCRIBE` returns (default `http://rdf_fdw.postgresql.org/`). The `base_uri` argument of sparql.describe overrides it.
`fetch_size`	optional	Default page size for rdf_fdw_clone_table (default `100`). It does not affect ordinary scans, which read a foreign table’s result in one request. A `fetch_size` on the `FOREIGN TABLE` overrides it, and the procedure’s own `fetch_size` argument overrides both.

[!NOTE] To view server options in psql use the meta-command \des[+].

CREATE USER MAPPING

CREATE USER MAPPING associates a PostgreSQL user with credentials for a specific SERVER. Provide a mapping when the SPARQL endpoint requires authentication; omit it for anonymous access.

Example (map local postgres to remote admin):

CREATE SERVER graphdb
FOREIGN DATA WRAPPER rdf_fdw
OPTIONS (endpoint 'http://192.168.178.27:7200/repositories/myrepo');

CREATE USER MAPPING FOR postgres
SERVER graphdb OPTIONS (user 'admin', password 'secret');

Proxy Authentication

If the SPARQL endpoint must be accessed through an HTTP proxy, configure the proxy URL in the SERVER using the http_proxy option. When the proxy requires authentication, provide the proxy credentials (proxy_user and proxy_password) in the USER MAPPING.

Example with proxy authentication:

CREATE SERVER fuseki
FOREIGN DATA WRAPPER rdf_fdw
OPTIONS (
  endpoint 'http://fuseki:3030/sparql',
  http_proxy 'http://my.proxy.im:3128'
);

CREATE USER MAPPING FOR postgres
SERVER fuseki
OPTIONS (
  user 'admin',
  password 'secret',
  proxy_user 'proxyuser',
  proxy_password 'proxypass'
);

Token Authentication

Some SPARQL endpoints support token-based authentication via an HTTP Authorization: Bearer header (RFC 6750). Use the token option instead of user/password in these cases.

Example with Bearer token:

CREATE SERVER qlever
FOREIGN DATA WRAPPER rdf_fdw
OPTIONS (endpoint 'http://qlever:7001/sparql');

CREATE USER MAPPING FOR postgres
SERVER qlever OPTIONS (token 'mysecrettoken');

Options:

Option	Type	Description
`user`	optional	Remote username for HTTP Basic Authentication.
`password`	optional	Remote user’s password (used with `user`).
`token`	optional	Bearer token for HTTP token-based authentication (RFC 6750). When set, sends `Authorization: Bearer <token>`. Cannot be combined with `user`/`password`.
`proxy_user`	optional	Proxy username (when `http_proxy` is set on the server).
`proxy_password`	optional	Proxy password (when `http_proxy` is set on the server).

Authentication: when user is supplied, rdf_fdw uses HTTP Basic Authentication. When token is supplied, it sends an Authorization: Bearer header per RFC 6750. Other schemes (OAuth flows, client certificates, etc.) are not supported.

[!WARNING] Basic Authentication and Bearer tokens are only protected by the connection they travel on. Use an https:// endpoint (and update_url) whenever credentials are set, and an https:// proxy URL for proxy credentials: over plain http:// they are sent in cleartext.

[!NOTE] To list mappings in psql use \deu[+] or query pg_user_mappings.

CREATE FOREIGN TABLE

Foreign tables act as a gateway to a SPARQL endpoint: the table’s sparql option supplies the query and the SPARQL variables map to PostgreSQL columns. Keep foreign-table definitions focused and avoid embedding complex logic in SQL; simpler queries increase pushdown success and performance.

Table options

Table options:

Option	Type	Description
`sparql`	required	Raw SPARQL query executed for `SELECT` operations.
`log_sparql`	optional	Log the exact SPARQL sent to the endpoint (default `false`).
`enable_pushdown`	optional	Override server-level pushdown for this table (default: server value).
`sparql_update_pattern`	optional	SPARQL triple pattern template used to build `INSERT DATA`, `DELETE DATA`, and `UPDATE` statements (required for DML). Each SPARQL variable in the pattern must be mapped to a table column.
`readonly`	optional	Mark this foreign table as read-only (default `false`). When `true`, `INSERT`, `UPDATE`, and `DELETE` are rejected for this table regardless of the server-level `readonly` setting. When `false`, explicitly overrides a server-level `readonly 'true'`, allowing writes on this table even when the server is read-only.
`fetch_size`	optional	Default page size for rdf_fdw_clone_table when cloning this table (default: the `SERVER` value, or `100`). It does not affect ordinary scans. The procedure’s own `fetch_size` argument overrides it.

Column types

Columns must be declared as rdfnode. Support for native PostgreSQL types (e.g. text, int, date) is deprecated and will be removed in a future version. Use rdfnode to preserve full RDF semantics (IRIs, language tags, datatypes).

Column options

Option	Type	Description
`variable`	required	SPARQL variable mapped to this column (e.g. `?name`). In `OPTIONS` write the variable with a leading `?` or `$`, but those characters are not part of the stored name.
`expression`	optional	Deprecated. SPARQL expression to evaluate for this column. Only valid for native PostgreSQL type columns, which are deprecated.
`language`	optional	Deprecated. Language tag to apply when comparing or formatting literals (e.g. `en`). Use `*` to ignore language. Only valid for native PostgreSQL type columns, which are deprecated.
`literal_type`	optional	Deprecated. Expected XSD datatype for the literal (e.g. `xsd:date`). Use `*` to ignore type. Only valid for native PostgreSQL type columns, which are deprecated. Also accepted spelled `literaltype`.
`nodetype`	optional	Deprecated. Hint whether the value is `literal` or `iri` (default `literal`). Only valid for native PostgreSQL type columns, which are deprecated.

Examples

Example (minimal rdfnode table):

CREATE FOREIGN TABLE hbf (
  p rdfnode OPTIONS (variable '?p'),
  o rdfnode OPTIONS (variable '?o')
)
SERVER linkedgeodata OPTIONS (
  sparql 
    'SELECT ?p ?o 
     WHERE {<http://linkedgeodata.org/triplify/node376142577> ?p ?o}');

[!WARNING] Native PostgreSQL column types (text, int, date, …) together with the column options nodetype, literal_type, language and expression are deprecated. Existing tables keep working for now, but new tables should use rdfnode columns.

[!NOTE] Use \d[+] or \det[+] in psql to view foreign table columns and options.

RDF Node Handling

The rdf_fdw extension introduces a custom data type called rdfnode that represents full RDF nodes exactly as they appear in a triplestore — preserving their full structure, including datatype IRIs and language tags. It supports all RDF term categories:

IRIs (e.g., <http://example.org/resource>)
Plain literals (e.g., "42")
Literals with language tags (e.g., "foo"@es)
Typed literals (e.g., "42"^^xsd:integer)
Blank nodes (e.g., _:b1)

rdfnode is a container, not a validator. Ill-typed literals — where the lexical form falls outside the value space of the declared datatype (e.g., "foo"^^xsd:date) — are accepted without error. Datatype conformance is the triplestore’s responsibility; rdfnode stores whatever the endpoint returns.

Casting Between rdfnode and Native PostgreSQL Types

When you need to work with the underlying value rather than the full RDF term, rdfnode can be cast to standard PostgreSQL types. The cast strips the datatype annotation and parses the lexical form. If the lexical form is invalid for the target type (e.g., casting "foo"^^xsd:date to date), the cast raises an error at runtime.

-- rdfnode to PostgreSQL
SELECT CAST('"42"^^xsd:int'::rdfnode AS int);                                  -- 42
SELECT CAST('"42.73"^^xsd:float'::rdfnode AS numeric);                         -- 42.73
SELECT CAST('"2025-05-16"^^xsd:date'::rdfnode AS date);                        -- 2025-05-16
SELECT CAST('"2025-05-16T06:41:50"^^xsd:dateTime'::rdfnode AS timestamp);      -- 2025-05-16 06:41:50

-- PostgreSQL to rdfnode
SELECT CAST('"foo"^^xsd:string' AS rdfnode);   -- "foo"^^<http://www.w3.org/2001/XMLSchema#string>
SELECT CAST(42.73 AS rdfnode);                 -- "42.73"^^<http://www.w3.org/2001/XMLSchema#decimal>
SELECT CAST(422892987223 AS rdfnode);          -- "422892987223"^^<http://www.w3.org/2001/XMLSchema#long>
SELECT CAST(DATE '2025-05-16' AS rdfnode);     -- "2025-05-16"^^<http://www.w3.org/2001/XMLSchema#date>
SELECT CAST(TIMESTAMPTZ '2025-05-16 06:41:50.221129+00' AS rdfnode);
-- "2025-05-16T06:41:50.221129Z"^^<http://www.w3.org/2001/XMLSchema#dateTime>

Comparison Operators with rdfnode

rdfnode supports the comparison operators =, !=, <>, <, <=, >, >=, just like SPARQL. Comparisons follow SPARQL 1.1 RDFterm-equal rules. Value-space comparison is supported for xsd:boolean, xsd:date, xsd:dateTime, xsd:time, xsd:duration, xsd:string, and all numeric XSD types (xsd:integer, xsd:int, xsd:decimal, xsd:float, xsd:double, etc.). Literals of unrecognised datatypes fall back to lexical comparison. An rdfnode can also be compared with PostgreSQL types such as int, numeric, date or timestamp.

SELECT '"foo"@en'::rdfnode = '"foo"@fr'::rdfnode;                                 -- f
SELECT '"foo"^^xsd:string'::rdfnode < '"foobar"^^xsd:string'::rdfnode;           -- t
SELECT '"42"^^xsd:int'::rdfnode = '"42"^^xsd:short'::rdfnode;                     -- t
SELECT '"73.42"^^xsd:float'::rdfnode < '"100"^^xsd:short'::rdfnode;               -- t
SELECT '"42"^^xsd:int'::rdfnode = 42;                                             -- t
SELECT '"42.73"^^xsd:decimal'::rdfnode < 42.99;                                   -- t
SELECT '"2010-01-08"^^xsd:date'::rdfnode < '2020-12-30'::date;                    -- t
SELECT '"2025-05-19T10:45:42Z"^^xsd:dateTime'::rdfnode = '2025-05-19 10:45:42'::timestamp; -- t

Arithmetic

+, -, * and / combine two numeric rdfnodes, following the SPARQL 1.1 operator mapping. The result takes the wider of the two datatypes (xsd:integer < xsd:decimal < xsd:float < xsd:double), except that dividing two xsd:integers gives an xsd:decimal. A term that is not a numeric literal raises an error.

The other operand may also be a PostgreSQL smallint, int, bigint, real, double precision or numeric. It is treated as the term its cast to rdfnode produces (e.g. numeric → xsd:decimal), and the result is again an rdfnode.

SELECT '"1"^^xsd:integer'::rdfnode + '"2.5"^^xsd:decimal'::rdfnode;  -- "3.5"^^<http://www.w3.org/2001/XMLSchema#decimal>
SELECT '"1"^^xsd:integer'::rdfnode / '"2"^^xsd:integer'::rdfnode;    -- "0.5"^^<http://www.w3.org/2001/XMLSchema#decimal>
SELECT '"1"^^xsd:integer'::rdfnode + 1;                              -- "2"^^<http://www.w3.org/2001/XMLSchema#integer>
SELECT '"0.1"^^xsd:decimal'::rdfnode * 3.0;                          -- "0.3"^^<http://www.w3.org/2001/XMLSchema#decimal>

Arithmetic with a real or double precision operand is evaluated locally rather than pushed down: SPARQL has no bare literal syntax for those types, so the constant would reach the endpoint as an xsd:decimal and change the result’s datatype. Integer and numeric operands are pushed down as usual.

Comparing, sorting and grouping

=, <>, <, <=, >= and > compare the RDF values two terms denote, which is what a WHERE clause means by them.

Sorting and grouping compare the terms as they are written, because a B-tree needs a total order and RDF value comparison is not one: terms of unlike kinds are incomparable, NaN is not equal to itself, and numeric type promotion makes equality intransitive. So ORDER BY, GROUP BY, DISTINCT, UNION and unique constraints treat "1"^^xsd:integer and "01"^^xsd:integer as two terms, where = reports one value:

SELECT '"01"^^xsd:integer'::rdfnode = '"1"^^xsd:integer'::rdfnode AS same_value;
 same_value 
------------
 t
(1 row)

Group or order by a cast where the value’s ordering is the one wanted, for example GROUP BY term::numeric.

An index on an rdfnode is ordered the same way, so it can answer a comparison written with one of the term operators — ~=, ~<~, ~<=~, ~>=~, ~>~ — while a value comparison is applied as a filter to the rows the scan returns.

The same holds for joins: ON a.iri = b.iri can only be evaluated in a nested loop, while ON a.iri ~= b.iri allows a merge join, which is much faster on large tables. For IRIs and blank nodes both return the same rows. For literals they do not, as ~= compares how the terms are written: "1"^^xsd:integer ~= "01"^^xsd:integer is false.

ALTER FOREIGN TABLE and ALTER SERVER

Use ALTER FOREIGN TABLE and ALTER SERVER to add, change, or remove options on a foreign table or server. Changes take effect for subsequent queries.

Add options

ALTER FOREIGN TABLE hbf
  OPTIONS (ADD enable_pushdown 'false', ADD log_sparql 'true');

ALTER SERVER dbpedia
  OPTIONS (ADD enable_pushdown 'false');

Change existing options

ALTER FOREIGN TABLE hbf
  OPTIONS (SET enable_pushdown 'false');

ALTER SERVER dbpedia
  OPTIONS (SET enable_pushdown 'true');

Remove options

ALTER FOREIGN TABLE hbf
  OPTIONS (DROP enable_pushdown, DROP log_sparql);

ALTER SERVER dbpedia
  OPTIONS (DROP enable_pushdown);

Privileges: You must own the object or be a superuser to alter a SERVER or FOREIGN TABLE.

Pushdown

Pushdown means translating SQL so that operations like filtering, sorting and limiting are executed by the remote triplestore (SPARQL endpoint) rather than in PostgreSQL. Running these operations remotely reduces the amount of data transferred and can greatly improve performance.

If a clause such as LIMIT is not pushed down, the triplestore may produce the full result set and send it to PostgreSQL, which then applies the limit — causing unnecessary work and network traffic. rdf_fdw tries to convert SQL into SPARQL where possible, but SQL and SPARQL differ in expressive power and semantics, so not every construct can be pushed down.

To maximize pushdown and performance, keep queries against foreign tables simple. rdf_fdw supports pushdown for most SPARQL 1.1 built-in functions and for several PostgreSQL constructs (for example, DISTINCT, LIMIT and IN/NOT IN).

[!NOTE]
Aggregate pushdown is currently not supported. While aggregate functions (such as sparql.sum, sparql.avg, sparql.min, sparql.max, sparql.group_concat, sparql.sample, etc.) are implemented and usable in SQL queries, they are always evaluated locally by PostgreSQL. The SPARQL endpoint does not perform aggregation for these queries! If you need remote aggregation, you must embed the aggregate in the foreign table’s SPARQL option or run the query directly against the endpoint.

LIMIT

LIMIT clauses are pushed down only when the remote LIMIT selects exactly the rows the SQL query asks for. That means all conditions in the WHERE clause must be translatable to SPARQL, and the query must not sort, join, group or aggregate. ORDER BY ... LIMIT n in particular is never pushed: see Remote Limit in EXPLAIN and Diagnostics for why.

SQL	SPARQL
`LIMIT x`	`LIMIT x`
`FETCH FIRST x ROWS`	`LIMIT x`
`FETCH FIRST ROW ONLY`	`LIMIT 1`

[!NOTE] OFFSET pushdown is not supported, meaning that OFFSET is applied locally in PostgreSQL. When combined with a pushable LIMIT, the endpoint is asked for LIMIT + OFFSET rows, e.g. OFFSET 5 LIMIT 10 sends LIMIT 15.

Example:

SELECT p, o FROM rdbms
WHERE 
  p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND
  sparql.lang(o) = 'pt'
FETCH FIRST 5 ROWS ONLY;

INFO:  SPARQL query sent to 'wikidata':

SELECT ?p ?o 
{
      ?s wdt:P31 wd:Q3932296 .
      ?s ?p ?o

 ## rdf_fdw pushdown conditions ##
 FILTER(?p = <http://www.w3.org/2000/01/rdf-schema#label>)
 FILTER(LANG(?o) = "pt")
}
LIMIT 5

INFO:  SPARQL returned 5 records.

                      p                       |             o             
----------------------------------------------+---------------------------
 <http://www.w3.org/2000/01/rdf-schema#label> | "Ingres"@pt
 <http://www.w3.org/2000/01/rdf-schema#label> | "Vectorwise"@pt
 <http://www.w3.org/2000/01/rdf-schema#label> | "PostgreSQL"@pt
 <http://www.w3.org/2000/01/rdf-schema#label> | "Microsoft SQL Server"@pt
 <http://www.w3.org/2000/01/rdf-schema#label> | "Firebird"@pt
(5 rows)

WHERE expressions and FETCH FIRST 5 ROWS ONLY was pushed (as LIMIT 5 in the SPARQL query).

ORDER BY

ORDER BY is pushed down when the involved expressions can be represented in SPARQL. rdf_fdw translates common SQL orderings to SPARQL ORDER BY using ASC() and DESC().

SQL	SPARQL
`ORDER BY x` or `ORDER BY x ASC`	`ORDER BY ASC(x)`
`ORDER BY x DESC`	`ORDER BY DESC(x)`

Ordering across multiple columns is preserved when each expression is pushdown-capable. If an expression cannot be translated, ordering is applied locally in PostgreSQL.

Example (pushdown):

SELECT o FROM rdbms
WHERE 
  p = '<http://www.w3.org/2000/01/rdf-schema#label>' AND
  sparql.langmatches(sparql.lang(o), 'es')
ORDER BY p ASC, o DESC
FETCH FIRST 5 ROWS ONLY;

INFO:  SPARQL query sent to 'wikidata':

SELECT ?p ?o 
{
      ?s wdt:P31 wd:Q3932296 .
      ?s ?p ?o

 ## rdf_fdw pushdown conditions ##
 FILTER(?p = <http://www.w3.org/2000/01/rdf-schema#label>)
 FILTER(LANGMATCHES(LANG(?o), "es"))
}
ORDER BY  ASC (?p)  DESC (?o)

INFO:  SPARQL returned 38 records.

        o        
-----------------
 "Xeround"@es
 "WxSQLite3"@es
 "Vectorwise"@es
 "TimesTen"@es
 "Tibero"@es
(5 rows)

The WHERE and ORDER BY expressions were pushed down. FETCH FIRST 5 ROWS ONLY was applied locally, because a LIMIT is not pushed together with an ORDER BY (see LIMIT).

DISTINCT

DISTINCT is pushed down to the SPARQL SELECT when possible. If the foreign table’s sparql option already contains DISTINCT or REDUCED, rdf_fdw will not push SQL DISTINCT again. There is no SPARQL equivalent of DISTINCT ON, so that PostgreSQL feature is always evaluated locally.

A DISTINCT describes what the statement returns, which is not what the scan beneath it reads. It is therefore not sent when an aggregate, a window function, a GROUP BY or a HAVING sits in between and counts the rows: removing duplicates before counting them would change the count. SELECT DISTINCT col FROM t is pushed down as before.

Example:

SELECT DISTINCT p, o FROM rdbms
WHERE 
  p = '<http://www.w3.org/2000/01/rdf-schema#label>' AND
  sparql.langmatches(sparql.lang(o), 'de');
INFO:  SPARQL query sent to 'wikidata':

SELECT DISTINCT ?p ?o 
{wd:Q192490 ?p ?o
 ## rdf_fdw pushdown conditions ##
 FILTER(?p = <http://www.w3.org/2000/01/rdf-schema#label>)
 FILTER(LANGMATCHES(LANG(?o), "de"))
}
ORDER BY  ASC (?p)  ASC (?o)

INFO:  SPARQL returned 1 record.

                      p                       |        o        
----------------------------------------------+-----------------
 <http://www.w3.org/2000/01/rdf-schema#label> | "PostgreSQL"@de
(1 row)

WHERE

The rdf_fdw extension supports pushdown of many SQL expressions in the WHERE clause. When applicable, these expressions are translated into SPARQL FILTER clauses, allowing filtering to occur directly at the RDF data source.

The following expressions in the WHERE clause are eligible for pushdown:

Comparisons involving PostgreSQL data types (e.g., integer, text, boolean) or the custom type rdfnode, when used with the supported operators:

Supported Data Types and Operators

Data type	Operators
`rdfnode`	`=`, `!=`,`<>`, `>`, `>=`, `<`, `<=`
`text`, `char`, `varchar`, `name`	`=`, `<>`, `!=`, `~~`, `!~~`, `~~`,`!~~`
`date`, `timestamp`, `timestamp with time zone`	`=`, `<>`, `!=`, `>`, `>=`, `<`, `<=`
`smallint`, `int`, `bigint`, `numeric`, `double precision`	`=`, `<>`, `!=`, `>`, `>=`, `<`, `<=`
`boolean`	`IS`, `IS NOT`

IN/NOT IN and ANY constructs with constant lists.

SQL IN and ANY constructs are translated into the SPARQL IN operator, which will be placed in a FILTER evaluation, as long as the list has the supported data types.
AND and OR, when every operand can be pushed down, e.g. WHERE sparql.contains(label, 'Berlin') OR sparql.contains(label, 'Bonn') becomes FILTER((CONTAINS(?label, "Berlin")) || (CONTAINS(?label, "Bonn"))).
Nearly all supported SPARQL functions are pushdown-capable, including:
- LANG(), DATATYPE(), STR(), isBLANK(), isIRI(), etc.
- Numeric, string, and datetime functions such as ROUND(), STRLEN(), YEAR(), and others.

[!NOTE]
Due to their volatile nature, the SPARQL functions sparql.rand() and sparql.now() cannot be pushed down. Because their results cannot be reproduced consistently by PostgreSQL, any rows returned from the endpoint would be filtered out locally during re-evaluation.

Conditions That Prevent Pushdown

A WHERE condition will not be pushed down if:

The option enable_pushdown is set to false.
The underlying SPARQL query includes:
- a GROUP BY clause
- solution modifiers like OFFSET, ORDER BY, LIMIT, DISTINCT, or REDUCED
- subqueries or federated queries
The condition includes an unsupported data type or operator.
The condition is an OR with an operand that cannot be pushed down. An OR is pushed down only as a whole.
The condition compares an rdfnode with a date, time, time with time zone, timestamp or timestamp with time zone. SPARQL only compares temporal terms of the same datatype and takes the timezone offset into account, while the PostgreSQL operator does neither, and endpoints disagree on what to do with terms that are not comparable. Evaluating these conditions locally keeps the result independent of the triplestore. Comparisons between two rdfnodes (e.g. o > '"1996-01-01T00:00:00"^^xsd:dateTime'), and between a column declared with a temporal type and a value of that type, are pushed down.

Pushdown Examples

For the examples in this section consider this SERVER and FOREIGN TABLE setting (Wikidata):

CREATE SERVER wikidata
FOREIGN DATA WRAPPER rdf_fdw 
OPTIONS (endpoint 'https://query.wikidata.org/sparql');

CREATE FOREIGN TABLE rdbms (
  s rdfnode OPTIONS (variable '?s'),
  p rdfnode OPTIONS (variable '?p'),
  o rdfnode OPTIONS (variable '?o')
)
SERVER wikidata OPTIONS (
  sparql 
    'SELECT * {
      ?s wdt:P31 wd:Q3932296 .
      ?s ?p ?o
     }'
);

1. Pushdown of WHERE conditions involving rdfnode values and = and != operators

All conditions are pushed as FILTER expressions.

SELECT s, o FROM rdbms
WHERE 
  o = '"PostgreSQL"@es' AND
  o <> '"Oracle"@es';
INFO:  SPARQL query sent to 'wikidata':

SELECT ?s ?o 
{
      ?s wdt:P31 wd:Q3932296 .
      ?s ?p ?o

 ## rdf_fdw pushdown conditions ##
 FILTER(?o = "PostgreSQL"@es)
 FILTER(?o != "Oracle"@es)
}

INFO:  SPARQL returned 1 record.

                    s                     |        o        
------------------------------------------+-----------------
 <http://www.wikidata.org/entity/Q192490> | "PostgreSQL"@es
(1 row)

2. Pushdown of WHERE conditions involving rdfnode values and =, >, and < operators

All conditions are pushed down as FILTER expressions. The bounds are written as xsd:dateTime literals: comparing an rdfnode with a PostgreSQL date or timestamp (e.g. o > '1996-01-01'::timestamp) also works, but it is evaluated locally (see Conditions That Prevent Pushdown).

SELECT s, o FROM rdbms
WHERE 
  p = '<http://www.wikidata.org/prop/direct/P577>' AND
  o > '"1996-01-01T00:00:00"^^xsd:dateTime' AND
  o < '"1996-12-31T00:00:00"^^xsd:dateTime';
INFO:  SPARQL query sent to 'wikidata':

SELECT ?s ?p ?o 
{
      ?s wdt:P31 wd:Q3932296 .
      ?s ?p ?o

 ## rdf_fdw pushdown conditions ##
 FILTER(?p = <http://www.wikidata.org/prop/direct/P577>)
 FILTER(?o > "1996-01-01T00:00:00"^^<http://www.w3.org/2001/XMLSchema#dateTime>)
 FILTER(?o < "1996-12-31T00:00:00"^^<http://www.w3.org/2001/XMLSchema#dateTime>)
}

INFO:  SPARQL returned 1 record.

                    s                     |                                  o                                  
------------------------------------------+---------------------------------------------------------------------
 <http://www.wikidata.org/entity/Q192490> | "1996-07-08T00:00:00Z"^^<http://www.w3.org/2001/XMLSchema#dateTime>
(1 row)

3. Pushdown of WHERE conditions with IN and ANY constructs

All conditions are pushed down as FILTER expressions.

SELECT p, o FROM rdbms
WHERE 
  p = '<http://www.w3.org/2000/01/rdf-schema#label>' AND
  o IN ('"PostgreSQL"@en', '"IBM Db2"@fr', '"MySQL"@es');
INFO:  SPARQL query sent to 'wikidata':

SELECT ?p ?o 
{
      ?s wdt:P31 wd:Q3932296 .
      ?s ?p ?o

 ## rdf_fdw pushdown conditions ##
 FILTER(?p = <http://www.w3.org/2000/01/rdf-schema#label>)
 FILTER(?o IN ("PostgreSQL"@en, "IBM Db2"@fr, "MySQL"@es))
}

INFO:  SPARQL returned 2 records.

                      p                       |        o        
----------------------------------------------+-----------------
 <http://www.w3.org/2000/01/rdf-schema#label> | "PostgreSQL"@en
 <http://www.w3.org/2000/01/rdf-schema#label> | "IBM Db2"@fr
(2 rows)

SELECT p, o FROM rdbms
WHERE 
  p = '<http://www.w3.org/2000/01/rdf-schema#label>' AND
  o = ANY(ARRAY['"PostgreSQL"@en'::rdfnode,'"IBM Db2"@fr'::rdfnode,'"MySQL"@es'::rdfnode]);

INFO:  SPARQL query sent to 'wikidata':

SELECT ?p ?o 
{
      ?s wdt:P31 wd:Q3932296 .
      ?s ?p ?o

 ## rdf_fdw pushdown conditions ##
 FILTER(?p = <http://www.w3.org/2000/01/rdf-schema#label>)
 FILTER(?o IN ("PostgreSQL"@en, "IBM Db2"@fr, "MySQL"@es))
}

INFO:  SPARQL returned 2 records.

                      p                       |        o        
----------------------------------------------+-----------------
 <http://www.w3.org/2000/01/rdf-schema#label> | "PostgreSQL"@en
 <http://www.w3.org/2000/01/rdf-schema#label> | "IBM Db2"@fr
(2 rows)

4. Pushdown of WHERE conditions involving SPARQL functions

The function calls for LANGMATCHES(), LANG(), and STRENDS() are pushed down in FILTER expressions.

SELECT s, o FROM rdbms
WHERE 
  p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND
  sparql.langmatches(sparql.lang(o), 'de') AND
  sparql.strends(o, sparql.strlang('SQL','de'));

INFO:  SPARQL query sent to 'wikidata':

SELECT ?s ?p ?o 
{
      ?s wdt:P31 wd:Q3932296 .
      ?s ?p ?o

 ## rdf_fdw pushdown conditions ##
 FILTER(?p = <http://www.w3.org/2000/01/rdf-schema#label>)
 FILTER(LANGMATCHES(LANG(?o), "de"))
 FILTER(STRENDS(?o, "SQL"@de))
}

INFO:  SPARQL returned 3 records.

                     s                     |        o        
-------------------------------------------+-----------------
 <http://www.wikidata.org/entity/Q192490>  | "PostgreSQL"@de
 <http://www.wikidata.org/entity/Q5014224> | "CSQL"@de
 <http://www.wikidata.org/entity/Q6862049> | "Mimer SQL"@de
(3 rows)

5. Pushdown of WHERE conditions with OR

An OR is pushed down as a single FILTER with || when all of its operands can be pushed down.

SELECT s, o FROM rdbms
WHERE
  p = '<http://www.w3.org/2000/01/rdf-schema#label>' AND
  sparql.lang(o) = 'en' AND
  (sparql.strstarts(sparql.str(o), 'Postgre') OR sparql.contains(sparql.str(o), 'Maria'));

INFO:  SPARQL query sent to 'wikidata':

SELECT ?s ?p ?o 
{
      ?s wdt:P31 wd:Q3932296 .
      ?s ?p ?o

 ## rdf_fdw pushdown conditions ##
 FILTER(?p = <http://www.w3.org/2000/01/rdf-schema#label>)
 FILTER(LANG(?o) = "en")
 FILTER((STRSTARTS(STR(?o), "Postgre")) || (CONTAINS(STR(?o), "Maria")))
}

INFO:  SPARQL returned 4 records.

                     s                      |        o         
--------------------------------------------+------------------
 <http://www.wikidata.org/entity/Q192490>   | "PostgreSQL"@en
 <http://www.wikidata.org/entity/Q787177>   | "MariaDB"@en
 <http://www.wikidata.org/entity/Q30100408> | "Postgres-XL"@en
 <http://www.wikidata.org/entity/Q30100559> | "Postgres-XC"@en
(4 rows)

Prefix Management

To simplify the reuse and sharing of common SPARQL prefixes, rdf_fdw provides a prefix management system based on two catalog tables and a suite of helper functions.

This feature enables you to register prefix contexts (named groups of prefixes), and to associate specific prefix -> URI mappings under those contexts. This is especially useful when working with multiple RDF vocabularies or endpoint configurations.

Prefix Context

The prefix context is a named container for a set of SPARQL prefixes. It is represented by the table sparql.prefix_contexts:

Column	Type	Description
`context`	`text`	Primary key; name of the context.
`description`	`text`	Optional description of the context.
`modified_at`	`timestamptz`	Timestamp of the last update.

Prefix Context Functions

Adding a new context sql sparql.add_context(context_name text, context_description text DEFAULT NULL, override boolean DEFAULT false)

Registers a new context. If override is set to true updates the context if it already exists, otherwise, raises an exception on conflict.

Deleting an existing context

sparql.drop_context(context_name text, cascade boolean DEFAULT false)

Deletes a context. If cascade is set to true deletes all associated prefixes, otherwise raises an exception if dependent prefixes exist.

Prefix

A prefix maps a shorthand identifier to a full URI within a context. These are stored in sparql.prefixes:

Column	Type	Description
`prefix`	`text`	The SPARQL prefix label.
`uri`	`text`	The fully qualified URI.
`context`	`text`	Foreign key to `prefix_contexts`.
`modified_at`	`timestamptz`	Timestamp of the last update.

Prefix Functions

Adding a new PREFIX sql sparql.add_prefix(context_name text, prefix_name text, uri text, override boolean DEFAULT false) Adds a prefix to a context. If override is set to true updates the URI if the prefix already exists. Otherwise raises an exception on conflict.

Deleting an existing PREFIX

sparql.drop_prefix(context_name text, prefix_name text)

Removes a prefix from a context. Raises an exception if the prefix does not exist.

Examples

-- Create a context
SELECT sparql.add_context('default', 'Default SPARQL prefix context');

-- Add prefixes to it
SELECT sparql.add_prefix('default', 'rdf',  'http://www.w3.org/1999/02/22-rdf-syntax-ns#');
SELECT sparql.add_prefix('default', 'rdfs', 'http://www.w3.org/2000/01/rdf-schema#');
SELECT sparql.add_prefix('default', 'owl',  'http://www.w3.org/2002/07/owl#');
SELECT sparql.add_prefix('default', 'xsd',  'http://www.w3.org/2001/XMLSchema#');

Once registered, these prefixes can be automatically included in generated SPARQL queries for any rdf_fdw foreign server that references the associated context.

Data Modification

The rdf_fdw extension supports data modification operations (INSERT, UPDATE, DELETE) on foreign tables connected to RDF triplestores. These operations allow you to add, modify, or remove RDF triples directly through SQL statements.

[!NOTE]
Data modification operations require the sparql_update_pattern option to be set on the FOREIGN TABLE. This option specifies a SPARQL triple pattern template that defines how rows from PostgreSQL are converted into SPARQL UPDATE statements.

General Requirements

To use data modification operations, your FOREIGN TABLE must:

Define the sparql_update_pattern option - A template specifying the RDF triple pattern(s) to be modified.
Map all required columns - Each variable used in sparql_update_pattern must be mapped to an existing table column via the column’s OPTIONS (e.g., OPTIONS (variable '?s')).
Use rdfnode columns - Data modification operations only support rdfnode type columns; PostgreSQL native types are not supported.

[!IMPORTANT]
SPARQL endpoints do not support PostgreSQL transactions. Modified rows are sent to the triplestore in batches of batch_size (see CREATE SERVER) while the statement runs, and each request is committed by the triplestore as soon as it arrives, regardless of PostgreSQL’s transaction state (BEGIN/COMMIT/ROLLBACK).

NULL Value Handling

NULL values are not allowed - Any attempt to insert or update a triple with a NULL component will raise an error. This prevents incomplete or invalid triples from being written to the triplestore.

INSERT

The INSERT statement allows you to add new RDF triples to a triplestore. Each row inserted into a FOREIGN TABLE is converted into a SPARQL INSERT DATA statement and sent to the remote SPARQL endpoint.

INSERT INTO ft (subject, predicate, object) VALUES
  ('<https://www.uni-muenster.de>',
  '<http://www.w3.org/2000/01/rdf-schema#label>',
  '"Westfälische Wilhelms-Universität Münster"@de');

UPDATE

The UPDATE statement allows you to modify existing RDF triples in a triplestore. Since SPARQL does not have a direct UPDATE syntax, each row update is implemented as a combination of DELETE DATA (removing old triples) followed by INSERT DATA (adding new triples).

UPDATE ft SET
  object = '"University of Münster"@en'
WHERE subject = '<https://www.uni-muenster.de>'
  AND predicate = '<http://www.w3.org/2000/01/rdf-schema#label>';

DELETE

The DELETE statement allows you to remove RDF triples from a triplestore. Each row deleted from a FOREIGN TABLE is converted into a SPARQL DELETE DATA statement and sent to the remote SPARQL endpoint.

DELETE FROM ft 
WHERE object = '"Westfälische Wilhelms-Universität Münster"@de'::rdfnode;

Update Examples

Full example: insert, update and delete triples in Fuseki

CREATE SERVER fuseki
FOREIGN DATA WRAPPER rdf_fdw 
OPTIONS (
  endpoint   'http://fuseki:3030/dt/sparql',
  update_url 'http://fuseki:3030/dt/update');

CREATE FOREIGN TABLE ft (
  subject   rdfnode OPTIONS (variable '?s'),
  predicate rdfnode OPTIONS (variable '?p'),
  object    rdfnode OPTIONS (variable '?o') 
)
SERVER fuseki OPTIONS (
  log_sparql 'false',
  sparql 
    $$
     SELECT * 
     WHERE {
       ?s ?p ?o .
       ?s <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <https://dbpedia.org/resource/University>      
     }
    $$,
  sparql_update_pattern 
    $$
      ?s ?p ?o .
      ?s <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <https://dbpedia.org/resource/University>
    $$
);

CREATE USER MAPPING FOR postgres
SERVER fuseki OPTIONS (user 'admin', password 'secret');

INSERT INTO ft (subject, predicate, object)
VALUES  ('<https://www.uni-muenster.de>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"University of Münster"@en');

SELECT * FROM ft;
            subject            |                     predicate                     |                  object                   
-------------------------------+---------------------------------------------------+-------------------------------------------
 <https://www.uni-muenster.de> | <http://www.w3.org/2000/01/rdf-schema#label>      | "University of Münster"@en
 <https://www.uni-muenster.de> | <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> | <https://dbpedia.org/resource/University>
(2 rows)

UPDATE ft SET
  object = '"Westfälische Wilhelms-Universität Münster"@de'
WHERE subject = '<https://www.uni-muenster.de>'
  AND predicate = '<http://www.w3.org/2000/01/rdf-schema#label>';

SELECT * FROM ft;
            subject            |                     predicate                     |                     object                     
-------------------------------+---------------------------------------------------+------------------------------------------------
 <https://www.uni-muenster.de> | <http://www.w3.org/2000/01/rdf-schema#label>      | "Westfälische Wilhelms-Universität Münster"@de
 <https://www.uni-muenster.de> | <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> | <https://dbpedia.org/resource/University>
(2 rows)

DELETE FROM ft
WHERE subject = '<https://www.uni-muenster.de>';

SELECT * FROM ft;
 subject | predicate | object 
---------+-----------+--------
(0 rows)

rdf_fdw_version

text rdf_fdw_version();

Description

Returns version information for rdf_fdw, PostgreSQL, compiler, and all dependencies (libxml, libcurl) in a single formatted string.

Usage

SELECT rdf_fdw_version();
                                            rdf_fdw_version                                             
--------------------------------------------------------------------------------------------------------
 rdf_fdw 3.1 (PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2), compiled by gcc, libxml 2.9.14, libcurl 8.14.1)
(1 row)

rdf_fdw_settings

VIEW rdf_fdw_settings(component text, version text);

Description

A system view that provides detailed version information for rdf_fdw and all its dependencies, including core libraries (PostgreSQL, libxml, libcurl) and optional components (SSL, zlib, libSSH, nghttp2), along with compiler and build information. Returns individual component names and their corresponding versions for convenient programmatic access.

Usage

SELECT * FROM rdf_fdw_settings;
 component  |            version            
------------+-------------------------------
 rdf_fdw    | 3.1
 PostgreSQL | 18.6 (Debian 18.6-1.pgdg13+2)
 libxml     | 2.9.14
 libcurl    | 8.14.1
 ssl        | GnuTLS/3.8.9
 zlib       | 1.3.1
 libSSH     | libssh2/1.11.1
 nghttp2    | 1.64.0
 compiler   | gcc
 built      | 2026-10-25 09:12:44 UTC
(10 rows)

rdf_fdw_clone_table

void rdf_fdw_clone_table(
  foreign_table text,
  target_table text,
  begin_offset int,
  fetch_size int,
  max_records int,
  orderby_column text,
  sort_order text,
  create_table boolean,
  verbose boolean,
  commit_page boolean
)

PostgreSQL 11+ only

Description

This procedure is designed to copy data from a FOREIGN TABLE to an ordinary TABLE. It provides the possibility to retrieve the data set in batches, so that known issues related to triplestore limits and client’s memory don’t bother too much.

Parameters

foreign_table (required): FOREIGN TABLE from where the data has to be copied.

target_table (required): heap TABLE where the data from the FOREIGN TABLE is copied to.

begin_offset: starting point in the SPARQL query pagination. Default 0.

fetch_size: size of the page fetched from the triplestore. Default is the value set at fetch_size in either SERVER or FOREIGN TABLE. In case SERVER and FOREIGN TABLE do not set fetch_size, the default will be set to 100.

max_records: maximum number of records that should be retrieved from the FOREIGN TABLE. Default 0, which means no limit.

orderby_column: ordering column used for the pagination - just like in SQL ORDER BY. Default '', which means that the procedure chooses the ordering column on its own: the first column of the FOREIGN TABLE (for tables with deprecated native-type columns, the first column with nodetype 'iri' is preferred). If you do not wish to have an ORDER BY clause at all, set this parameter to NULL.

sort_order: ASC or DESC to sort the data returned in ascending or descending order, respectively. Default ASC.

create_table: creates the table set in target_table before harvesting the FOREIGN TABLE. Default false.

verbose: prints debugging messages in the standard output. Default false.

commit_page: commits the inserted records immediately or only after the transaction is finished. Useful for those who want records to be discarded in case of an error - following the principle of everything or nothing. Default true, which means that all inserts will be committed immediately.

Usage Example

CREATE SERVER dbpedia
FOREIGN DATA WRAPPER rdf_fdw 
OPTIONS (endpoint 'https://dbpedia.org/sparql');

CREATE FOREIGN TABLE public.dbpedia_cities (
  uri       rdfnode OPTIONS (variable '?city'),
  city_name rdfnode OPTIONS (variable '?name'),
  elevation rdfnode OPTIONS (variable '?elevation')
)
SERVER dbpedia OPTIONS (
  sparql '
    PREFIX dbo:  <http://dbpedia.org/ontology/>
    PREFIX foaf: <http://xmlns.com/foaf/0.1/>
    PREFIX dbr:  <http://dbpedia.org/resource/>
    SELECT *
    {?city a dbo:City ;
      foaf:name ?name ;
      dbo:federalState dbr:North_Rhine-Westphalia ;
      dbo:elevation ?elevation
    }
');

/*
 * Materialize all records from the FOREIGN TABLE 'public.dbpedia_cities' in 
 * the table 'public.t1_local'. 
 */ 
CALL rdf_fdw_clone_table(
      foreign_table => 'dbpedia_cities',
      target_table  => 't1_local',
      create_table => true);

SELECT DISTINCT uri, elevation FROM t1_local ORDER BY uri;
                             uri                             |                     elevation                      
-------------------------------------------------------------+----------------------------------------------------
 <http://dbpedia.org/resource/Gelsenkirchen>                 | "60.0"^^<http://www.w3.org/2001/XMLSchema#double>
 <http://dbpedia.org/resource/Hagen>                         | "106.0"^^<http://www.w3.org/2001/XMLSchema#double>
 <http://dbpedia.org/resource/Herne,_North_Rhine-Westphalia> | "65.0"^^<http://www.w3.org/2001/XMLSchema#double>
 <http://dbpedia.org/resource/Krefeld>                       | "39.0"^^<http://www.w3.org/2001/XMLSchema#double>
 <http://dbpedia.org/resource/Mönchengladbach>               | "70.0"^^<http://www.w3.org/2001/XMLSchema#double>
 <http://dbpedia.org/resource/Mülheim>                       | "26.0"^^<http://www.w3.org/2001/XMLSchema#double>
(6 rows)

EXPLAIN and Diagnostics

The rdf_fdw extension provides detailed diagnostics in PostgreSQL EXPLAIN output to help users understand which SQL clauses are pushed down to the remote SPARQL endpoint.

The plan output includes FDW-specific lines for each Foreign Scan node: * Foreign Server: shows the foreign server related to the queried foreign table. * Pushdown: reports what the scan does: enabled when the query was rewritten, disabled when the enable_pushdown option is off, and unsupported SPARQL when the query given in the table’s sparql option cannot be rewritten - for instance because it already carries its own LIMIT, ORDER BY, GROUP BY, UNION or MINUS. In the latter two cases the query is sent exactly as it was supplied and every SQL clause is evaluated locally, so none of the Remote lines below are shown. * Remote Filter: shows the SPARQL FILTER expression(s) generated from SQL WHERE clauses.
If the WHERE clause cannot be translated to SPARQL FILTER expressions, the plan will display Remote Filter: not pushable to indicate that filtering is performed locally in PostgreSQL. * Remote Sort Key: shows the SPARQL ORDER BY clause if sorting is pushed down. * Remote Limit: shows the SPARQL LIMIT clause if limiting is pushed down. A LIMIT is only sent when it selects the same rows the SQL query asks for, which rules out queries that also sort, join, group or aggregate. For sorting, the reason is that SPARQL leaves the relative order of many terms to the implementation, and implementations disagree: asked to sort the same eight terms, Fuseki returns IRIs first, Virtuoso strings first and QLever booleans first. The endpoint’s first n rows may therefore not be PostgreSQL’s first n, so ORDER BY ... LIMIT n fetches the result set and takes the first n locally. * Remote Select: shows the SPARQL SELECT clause generated for the query.

Examples:

Filter and sort are pushed down, while the LIMIT is applied locally because the query also sorts:

EXPLAIN (COSTS OFF)
SELECT p, o FROM ft
WHERE sparql.isnumeric(o) AND o > 42
ORDER BY o DESC
FETCH FIRST 3 ROWS ONLY;
                         QUERY PLAN                          
-------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: o DESC
         ->  Foreign Scan on ft
               Foreign Server: wikidata
               Pushdown: enabled
               Remote Select: ?p ?o 
               Remote Filter: ((ISNUMERIC(?o)) && (?o > 42))
               Remote Sort Key:   DESC (?o)
(9 rows)

Without the ORDER BY the LIMIT is pushed down as well:

EXPLAIN (COSTS OFF)
SELECT p, o FROM ft
WHERE sparql.isnumeric(o) AND o > 42
FETCH FIRST 3 ROWS ONLY;
                      QUERY PLAN                       
-------------------------------------------------------
 Limit
   ->  Foreign Scan on ft
         Foreign Server: wikidata
         Pushdown: enabled
         Remote Select: ?p ?o 
         Remote Filter: ((ISNUMERIC(?o)) && (?o > 42))
         Remote Limit: LIMIT 3
(7 rows)

SPARQL Functions

rdf_fdw implements most of the SPARQL 1.1 built-in functions as SQL functions in the sparql schema, so they don’t clash with PostgreSQL built-ins such as round or replace. They follow SPARQL semantics for literals, language tags, datatypes and unbound values, and are pushed down to the endpoint when used in a WHERE clause (except sparql.rand() and sparql.now()). Aggregates are always evaluated locally.

The SPARQL function reference documents each function with its signature and examples.

Category	Functions
Aggregates	`sum` `avg` `min` `max` `group_concat` `sample`
Functional forms	`bound` `coalesce` `sameterm`
RDF terms	`isiri`/`isuri` `isblank` `isliteral` `isnumeric` `str` `lang` `datatype` `iri`/`uri` `bnode` `strdt` `strlang` `uuid` `struuid`
Strings	`strlen` `substr` `ucase` `lcase` `strstarts` `strends` `contains` `strbefore` `strafter` `encode_for_uri` `concat` `langmatches` `replace`
Numerics	`abs` `round` `ceil` `floor` `rand`
Dates and times	`now` `year` `month` `day` `hours` `minutes` `seconds` `timezone` `tz`
Hash	`md5`
Non-standard	`lex`

-- German labels ending in "SQL" (rdbms table from the pushdown examples);
-- all three conditions are sent to the endpoint as FILTER expressions
SELECT s, sparql.lex(o) AS label
FROM rdbms
WHERE p = '<http://www.w3.org/2000/01/rdf-schema#label>'
  AND sparql.langmatches(sparql.lang(o), 'de')
  AND sparql.strends(o, '"SQL"@de');

[!WARNING] Triplestores often deviate from SPARQL 1.1 when handling language-tagged or typed literals. As pushed-down conditions are re-checked locally, such differences make rows disappear silently. See Triplestores differ before relying on pushdown of string functions.

SPARQL describe

sparql.describe(server text, query text, base_uri text DEFAULT '') → SETOF triple

Description

The sparql.describe function executes a SPARQL DESCRIBE query against a specified RDF triplestore SERVER. It retrieves RDF triples describing a resource (or resources) identified by the query and returns them as a table with three columns: subject, predicate, and object. This function is useful for exploring RDF data by fetching detailed descriptions of resources from a triplestore.

[!NOTE] sparql.describe requires the SPARQL endpoint to return DESCRIBE results in application/rdf+xml format. This is the default behaviour of most established triplestores (Fuseki, GraphDB, Virtuoso, Blazegraph). Endpoints that do not support RDF/XML for graph responses will receive an HTTP 406 error.

Parameters

server (required): The name of the foreign server (defined via CREATE SERVER) that specifies the SPARQL endpoint to query. This must correspond to an existing rdf_fdw server configuration. Cannot be empty or NULL.

query (required): A valid SPARQL DESCRIBE query string (e.g., DESCRIBE <http://example.org/resource>). Cannot be empty or NULL.

base_uri: The base URI used to resolve relative URIs in the RDF/XML response from the triplestore. If empty, the SERVER option base_uri is used, or http://rdf_fdw.postgresql.org/ if that is not set either. Useful for ensuring correct URI resolution in the parsed triples.

Literals are always returned with their datatype or language tag. Use sparql.lex or sparql.str to get only the lexical value.

Return Value

Returns a table with the following rdfnode columns: * subject: The subject of each RDF triple, typically a URI or blank node identifier. * predicate: The predicate (property) of each RDF triple, always a URI. * object: The object of each RDF triple, which may be a URI, blank node, or literal value.

Usage Example

CREATE SERVER wikidata
FOREIGN DATA WRAPPER rdf_fdw 
OPTIONS (endpoint 'https://query.wikidata.org/sparql');

SELECT subject, predicate, object
FROM sparql.describe('wikidata', 'DESCRIBE <http://www.wikidata.org/entity/Q61308849>')
WHERE predicate::text LIKE '<http://www.wikidata.org/prop/direct/%'
ORDER BY predicate::text COLLATE "C";

                  subject                   |                  predicate                  |                                     object                                      
--------------------------------------------+---------------------------------------------+---------------------------------------------------------------------------------
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P127>  | <http://www.wikidata.org/entity/Q349450>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P131>  | <http://www.wikidata.org/entity/Q2007>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P17>   | <http://www.wikidata.org/entity/Q16>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P2044> | "4.27"^^<http://www.w3.org/2001/XMLSchema#decimal>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P31>   | <http://www.wikidata.org/entity/Q846837>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P3999> | "2015-01-01T00:00:00Z"^^<http://www.w3.org/2001/XMLSchema#dateTime>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P4150> | <http://commons.wikimedia.org/data/main/Data:Weather.gc.ca/Almanac/2203912.tab>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P4150> | <http://commons.wikimedia.org/data/main/Data:Weather.gc.ca/Monthly/2203912.tab>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P6242> | "2203912"^^<http://www.w3.org/2001/XMLSchema#string>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P625>  | "Point(-133.03 69.43)"^^<http://www.opengis.net/ont/geosparql#wktLiteral>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P6339> | <http://www.wikidata.org/entity/Q59657037>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P6339> | <http://www.wikidata.org/entity/Q59657036>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P6339> | <http://www.wikidata.org/entity/Q59657010>
 <http://www.wikidata.org/entity/Q61308849> | <http://www.wikidata.org/prop/direct/P729>  | "1970-01-01T00:00:00Z"^^<http://www.w3.org/2001/XMLSchema#dateTime>
(14 rows)

GIS integration

Foreign tables can feed GIS tools like any other PostgreSQL table. Two walkthroughs are in docs/gis-integration.md: turning DBpedia coordinates into PostGIS geometries for QGIS, and publishing a Wikidata foreign table as a WFS layer in GeoServer.

For testers and developers

Think you’re cool enough? Try compiling the latest commits from source: see Deploy with Docker for a Dockerfile, and Quick Start for running the regression tests.

If you’ve found a bug or have general comments, do not hesitate to open an issue. Any feedback is much appreciated!

PGXN

PostgreSQL Extension Network

Contents

RDF Triplestore Foreign Data Wrapper for PostgreSQL (rdf_fdw)

Index

Requirements

Quick Start

Update Extension

Deploy with Docker

Usage

CREATE SERVER

CREATE USER MAPPING

CREATE FOREIGN TABLE

Table options

Column types

Column options

Examples

RDF Node Handling

Arithmetic

Comparing, sorting and grouping

ALTER FOREIGN TABLE and ALTER SERVER

Pushdown

LIMIT

ORDER BY

DISTINCT

WHERE

Pushdown Examples

Prefix Management

Prefix Context

Prefix Context Functions

Prefix

Prefix Functions

Data Modification

General Requirements

NULL Value Handling

INSERT

UPDATE

DELETE

Update Examples

rdf_fdw_version

rdf_fdw_settings

rdf_fdw_clone_table

EXPLAIN and Diagnostics

SPARQL Functions

SPARQL describe

GIS integration

For testers and developers