0

I was looking for the STDIN option, to use FOREIGN TABLE in similar way as COPY... And discovery a "bug" in the Guide: there are no documentation about options at official sql-create-foreign-table Guide. No link, nothing:

OPTIONS ( option 'value' [, ...] )

Options to be associated with the new foreign table or one of its columns. ...

So, to lack of information transformed this question in two:

  1. It is possible to use STDIN with FOREIGN TABLE?

  2. Where the "OPTIONS" documentation?

edit to add example

CREATE FOREIGN TABLE t1 (
   aa text,
   bb bigint
) SERVER files OPTIONS (
   filename '/tmp/bigBigdata.csv',
   format 'csv',
   header 'true'
;

Is a classic ugly PostgreSQL limitation on use filesystem, so I need a terminal solution ... Imagine on shell something with pipes, as

psql  -c "ALTER FOREIGN TABLE t1 ...  STDIN; CREATE TABLE t2 AS SELECT trim(aa) as aa, bb+1 as bb FROM t WHERE bb>999" < /thePath/bigBigdata.csv

Is a kind of "no direct copy, only filtering a stream of data", and creating a final table t2 from this filtered stream.

Peter Krauss
  • 13,174
  • 24
  • 167
  • 304

1 Answers1

3

I think you are confused about foreign tables, I'll try to explain.

  1. The data of a foreign table do not reside in PostgreSQL, but in an external data source (a file, a different database, etc.).

    The foreign table is just a way to access these data from PostgreSQL as if they were a PostgreSQL table.

    You can COPY to a foreign table FROM STDIN if the foreign data wrapper supports it, but that has nothing to with CREATE FOREIGN TABLE. CREATE FOREIGN TABLE defines how PostgreSQL should locate the external data and what the format of the data is.

  2. There is no documentation of the options in CREATE FOREIGN TABLE because they depend on the foreign data wrapper you are using.

    Look at the documentation of the foreign data wrapper.

Your example makes clear that what you need is not a foreign table, but a temporary table into which you can COPY the raw data which you later want to modify. You cannot use file_fdw for data that resides on the client machine.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Hi Laurenz, sorry, I was looking for a file-permission solution in CSV bigbig files, I edited to illustrate... About the "bug of the Guide", the lack of `*_fdw` modules citation (and no link) **is a bug**. Thanks to the clues, now I can see that there are *nothing about STDIN* at [`file_fdw` module](https://www.postgresql.org/docs/current/static/file-fdw.html). My workaround is to use a "foreign language" (as [PL/Python](https://www.postgresql.org/docs/current/static/plpython.html) to interact with the filesystem without ugly permission barriers) instead the *foreigin table* approach. – Peter Krauss Aug 13 '18 at 18:59
  • 1
    Most of the foreign data wrappers are not part of the PostgreSQL project, so they have no place in the documentation. Look at the PostgreSQL Wiki for a comprehensive list. Your use case is still not well described; perhaps `COPY ... FROM PROGRAM`is what you need. – Laurenz Albe Aug 14 '18 at 05:48
  • Hi @LaurenzAlbe, I was navegating and see, 2 years after, a better example for you: [**ingestion** from relative path](https://stackoverflow.com/a/41464864/287948)... In ETL/bigdata terminology "ingestion" describes a class of problems to be solved. The dream for PostgreSQL is to *ingest* any CSV file of a fixed structure (`my_foreign_stdin` or a projection from it)... In the dream the terminal command would be something as `psql _etc_ -c "INSERT INTO mytable SELECT column1::bigint, trim(column2) FROM my_foreign_stdin WHERE column1>10" < ./relative_path/file.csv` – Peter Krauss Feb 18 '20 at 21:57
  • Replace `INSERT` with `COPY`, and you are almost there. v12 offers a `WHERE` clause for `COPY`. All you need are some pre-processing steps (`sed`?) or post-processing (unlogged table, `UPDATE`, `ALTER TABLE ... SET LOGGED`). – Laurenz Albe Feb 19 '20 at 06:48