PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL "create foreign table" (file_fdw) on files with single quotes in filename generates ERROR

I have a CSV file with a silly filename (for example): ‘test_1111 _2_ABC Hr. Schmidt_2024.csv’

postgres@minisforum:~/16/import$ ll
insgesamt 1212
drwxrwxr-x 3 postgres postgres    4096 Okt 14 19:16  ./
drwxr-xr-x 5 postgres postgres    4096 Okt 13 15:41  ../
-rw-rw-r-- 1 postgres postgres 1228581 Okt 14 19:14 'test_1111 _2_ABC Hr. Schmidt_2024.csv'

After some tries I can create a foreign table on this file:

CREATE FOREIGN TABLE IF NOT EXISTS test_fdw( datum text, pr integer, standort text, ... ) 
SERVER fdw_files OPTIONS (filename '/var/lib/postgresql/16/import/''test_1111 _2_ABC Hr. Schmidt_2024.csv''', format 'csv', header 'true', delimiter ';');
CREATE FOREIGN TABLE

With other combinations I get an error on CREATE.
With det+ you get:

Liste der Fremdtabellen

  Schema  |     Tabelle      |  Server   |                                                                     FDW-Optionen                                                                      | Beschreibung
----------+------------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+--------------
 abc      | test_fdw         | fdw_files | (filename '/var/lib/postgresql/16/import/''test_1111 _2_ABC Hr. Schmidt_2024.csv''', format 'csv', header 'true', delimiter ';')                      |

A correctly declared foreign table! But I have no access on this table:

postgres=# select * from test_fdw;
FEHLER:  konnte Datei »/var/lib/postgresql/16/import/'test_1111 _2_ABC Hr. Schmidt_2024.csv'« nicht zum Lesen öffnen: Datei oder Verzeichnis nicht gefunden
TIP:  Mit COPY FROM liest der PostgreSQL-Serverprozess eine Datei. Möglicherweise möchten Sie Funktionalität auf Client-Seite verwenden, wie zum Beispiel copy in psql.

File or Directory was not found. This is not a file permission problem. Also no success with the COPY command. Does anybody have a hint for me? Did I found a bug in file_fdw?

Tags:

2

Answers


  1. 0

    You defined the file name to contain single quotes, but it doesn’t. Use

    OPTIONS (
   filename '/var/lib/postgresql/16/import/test_1111 _2_ABC Hr. Schmidt_2024.csv',
   format 'csv',
   header 'true',
   delimiter ';'
)
    Login or Signup to reply.
  2. 0

    demo at db<>fiddle

    create extension if not exists file_fdw;
CREATE SERVER fdw_files FOREIGN DATA WRAPPER file_fdw;

copy(select 'datum1' as datum
          , 1 as pr 
          , 'standort1' as standort
)to'/tmp/test_1111 _2_ABC Hr. Schmidt_2024.csv'
with(  format 'csv'
     , header true
     , delimiter ';');

    The create foreign table works regardless of whether the file exists at the time. Neither its existence nor structure is validated until you attempt a read from it:

    CREATE FOREIGN TABLE IF NOT EXISTS 
  test_fdw(  datum text
           , pr integer
           , standort text ) 
SERVER fdw_files 
OPTIONS(  filename '/tmp/''test_1111 _2_ABC Hr. Schmidt_2024.csv'''
        , format 'csv'
        , header 'true'
        , delimiter ';');

    The above works fine, then fails at the first read. ls -l behind the ll alias is adding the single quotes because the file name has spaces in it, not because the name really starts and ends with single quotes, so forcing them in there results in a mismatched file name.

    select*from test_fdw;

    ERROR:  could not open file "/tmp/'test_1111 _2_ABC Hr. Schmidt_2024.csv'" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's copy.

    As immediately pointed out by @Laurenz Albe, it should be enough to just not force the single quotes ll used for wrapping, into the file name – as a SQL string constant it’s already wrapped along with the rest of the path

    CREATE FOREIGN TABLE IF NOT EXISTS 
  test_fdw(  datum text
           , pr integer
           , standort text ) 
SERVER fdw_files 
OPTIONS(  filename '/tmp/test_1111 _2_ABC Hr. Schmidt_2024.csv'
        , format 'csv'
        , header 'true'
        , delimiter ';');

select*from test_fdw;
    datum pr standort
    datum1 1 standort1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search