Create a foreign table in Postgres, to directly access data in a text file

Source: R/postgres.R

The use case for this function is as follows: You get a large file (csv, tab-delimited) and would rather wrangle such data in postgres. Instead of go ahead and copy all the data into a table, let's create a foreign table that you can query just like a regular table.

pg_create_foreign_table(
  con,
  file_path,
  table,
  sep = ";",
  nrows = 10000,
  header = TRUE,
  na.strings = c("", "NA", "NULL"),
  colClasses = NULL,
  col.names,
  encoding = "UTF-8",
  ...,
  drop_table = FALSE,
  cascade = FALSE,
  if_not_exists = FALSE,
  create_foreign_server = TRUE,
  execute = FALSE,
  quote = "\b",
  escape = quote,
  null = "NULL"
)

Arguments

con

A database connection.

file_path

path to a text file (csv, tab-delimited, etc.)

table

The table name, passed on to dbQuoteIdentifier(). Options are:

  • a character string with the unquoted DBMS table name, e.g. "table_name",

  • a call to Id() with components to the fully qualified table name, e.g. Id(schema = "my_schema", table = "table_name")

  • a call to SQL() with the quoted and fully qualified table name given verbatim, e.g. SQL('"my_schema"."table_name"')

sep

The separator between columns. Defaults to the character in the set [,\t |;:] that separates the sample of rows into the most number of lines with the same number of fields. Use NULL or "" to specify no separator; i.e. each line a single character column like base::readLines does.

nrows

The maximum number of rows to read. Unlike read.table, you do not need to set this to an estimate of the number of rows in the file for better speed because that is already automatically determined by fread almost instantly using the large sample of lines. nrows=0 returns the column names and typed empty columns determined by the large sample; useful for a dry run of a large file or to quickly check format consistency of a set of files before starting to read any of them.

header

Does the first data line contain column names? Defaults according to whether every non-empty field on the first data line is type character. If so, or TRUE is supplied, any empty column names are given a default name.

na.strings

A character vector of strings which are to be interpreted as NA values. By default, ",," for columns of all types, including type character is read as NA for consistency. ,"", is unambiguous and read as an empty string. To read ,NA, as NA, set na.strings="NA". To read ,, as blank string "", set na.strings=NULL. When they occur in the file, the strings in na.strings should not appear quoted since that is how the string literal ,"NA", is distinguished from ,NA,, for example, when na.strings="NA".

colClasses

As in utils::read.csv; i.e., an unnamed vector of types corresponding to the columns in the file, or a named vector specifying types for a subset of the columns by name. The default, NULL means types are inferred from the data in the file. Further, data.table supports a named list of vectors of column names or numbers where the list names are the class names; see examples. The list form makes it easier to set a batch of columns to be a particular class. When column numbers are used in the list form, they refer to the column number in the file not the column number after select or drop has been applied. If type coercion results in an error, introduces NAs, or would result in loss of accuracy, the coercion attempt is aborted for that column with warning and the column's type is left unchanged. If you really desire data loss (e.g. reading 3.14 as integer) you have to truncate such columns afterwards yourself explicitly so that this is clear to future readers of your code.

col.names

A vector of optional names for the variables (columns). The default is to use the header column if present or detected, or if not "V" followed by the column number. This is applied after check.names and before key and index.

encoding

default is "unknown". Other possible options are "UTF-8" and "Latin-1". Note: it is not used to re-encode the input, rather enables handling of encoded strings in their native encoding.

...

Other arguments used by individual methods.

drop_table

logical, whether to drop the table before creating it

cascade

logical, whether to add CASCADE to the DROP statement

if_not_exists

logical, to add IF NOT EXISTS to the query

create_foreign_server

logical, to create the extension and server for the foreign data wrapper (https://www.postgresql.org/docs/10/file-fdw.html)

execute

logical, whether to execute the query using con

quote

By default ("\""), if a field starts with a double quote, fread handles embedded quotes robustly as explained under Details. If it fails, then another attempt is made to read the field as is, i.e., as if quotes are disabled. By setting quote="", the field is always read as if quotes are disabled. It is not expected to ever need to pass anything other than \"\" to quote; i.e., to turn it off.

escape

"character that should appear before a data character that matches the QUOTE value"

null

"Specifies the string that represents a null value."

Value

invisibly returns a character vector (length 1) with the sql statement

Details

Some notes:

  • The file must be accesible to the postgres server. This function tries to edit file's privileges to make sure is readable from the server. However, if the file is in a network share, that may not be enough because the server should be able to access the network resource. It is likely the default user running the server cannot access network shared resources, so the workaround is to change the user running the server

See Postgres documentation: