Overview

This code block demonstrates how to create a foreign table over Google Cloud Storage.

CREATE FOREIGN DATA WRAPPER <wrapper_name>
HANDLER gcs_fdw_handler
VALIDATOR gcs_fdw_validator;

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER <wrapper_name>;

-- Replace the dummy schema with the actual schema
CREATE FOREIGN TABLE <table_name> ("x" INT)
SERVER <server_name>
OPTIONS (path '<path>', extension '<extension>');
wrapper_name
required

Foreign data wrapper name. Can be any string.

server_name
required

Foreign server name. Can be any string.

table_name
required

Foreign table name. Can be any string.

Options for CREATE SERVER

endpoint
The endpoint used by GCS.
default_storage_class

The default storage class for GCS. Options are STANDARD, NEARLINE, COLDLINE, and ARCHIVE.

predefined_acl

The predefined ACL for GCS. Options are authenticatedRead, bucketOwnerFullControl, bucketOwnerRead, private, projectPrivate, and projectPrivate.

Options for CREATE FOREIGN TABLE

path
required

Must start with gs:// and point to the location of your file. The path should end in a / if it points to a directory of partitioned Parquet files.

extension
required

One of avro, csv, json, and parquet.

format

For now, only delta is supported for Delta Lake (Iceberg is coming soon). If omitted, no table format is assumed.

Google Cloud Credentials

CREATE USER MAPPING is used to pass in credentials for private buckets.

-- Get the name of the current user
SELECT current_user;
 current_user
--------------
 myuser

-- Run this before CREATE FOREIGN TABLE
CREATE USER MAPPING FOR <current_user>
SERVER <server_name>
OPTIONS (
  credential '<credential>'
);

-- Now, run CREATE FOREIGN TABLE
current_user
required

The name of the Postgres user. If set to public, these credentials will be applied to all users.

server_name
required

Foreign server name.

Options for CREATE USER MAPPING

credential

A Base64 hashed credentials string. This string should be generated from the contents of your credentials .json file.

credential_path

The path to the credentials .json file. Mostly useful for testing, as this file must be present on the same machine as the Postgres instance. Only one of credential or credential_path should be provided.

scope

The GCS service scope. Defaults to https://www.googleapis.com/auth/devstorage.read_write.

service_account

The name of the GCS service account.