file_fdw
file_fdw
file_fdw : foreign-data wrapper for flat file access
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 8980 | file_fdw | file_fdw | 1.0 | FDW | PostgreSQL | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d-- | No | Yes | No | Yes | no | no |
| Relationships | |
|---|---|
| Need By | pg_sqlog |
| See Also | log_fdw wrappers sqlite_fdw aws_s3 pg_bulkload multicorn hdfs_fdw postgres_fdw |
Packages
| PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|
1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
This is a built-in contrib extension ship with the PostgreSQL kernel
Install
Create this extension with:
CREATE EXTENSION file_fdw;Usage
Create Server
CREATE EXTENSION file_fdw;
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;Read a CSV File
CREATE FOREIGN TABLE csv_data (
id integer,
name text,
value numeric
)
SERVER file_server
OPTIONS (filename '/path/to/data.csv', format 'csv', header 'true');
SELECT * FROM csv_data;Read PostgreSQL CSV Logs
CREATE FOREIGN TABLE pglog (
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
backend_type text,
leader_pid integer,
query_id bigint
)
SERVER file_server
OPTIONS (filename 'log/pglog.csv', format 'csv');Read Program Output
CREATE FOREIGN TABLE process_list (
pid text,
command text
)
SERVER file_server
OPTIONS (program 'ps aux | tail -n +2', format 'text', delimiter ' ');Table Options
| Option | Description |
|---|---|
filename | File path (relative to data directory). Required unless program is used |
program | Shell command whose stdout is read. Required unless filename is used |
format | Data format: csv, text, or binary (same as COPY) |
header | true if file has a header row |
delimiter | Column delimiter character |
quote | Quote character |
escape | Escape character |
null | String representing NULL values |
encoding | Data encoding |
on_error | Error handling during type conversion |
reject_limit | Maximum tolerated errors |
Column Options
| Option | Description |
|---|---|
force_not_null | Do not match column values against the null string |
force_null | Match quoted values against the null string and return NULL |
CREATE FOREIGN TABLE films (
code char(5) NOT NULL,
title text NOT NULL,
rating text OPTIONS (force_null 'true')
)
SERVER file_server
OPTIONS (filename '/data/films.csv', format 'csv');file_fdw is read-only. Changing table-level options requires superuser privileges or the pg_read_server_files / pg_execute_server_program role.
Last updated on