Postgres filenames with german umlaut

Question

I would like to read some rtf files I received from an outside source into a table with Postgres. I am using the pg_stat_file() function to check whether or not the file exists and aim to read its content with pg_read_file(). If the filename contains an umlaut, the function fails.

SELECT pg_stat_file('C:\datafolder\ctköln.rtf', true);

ERROR:  could not open file "C:\datafolder\ctköln.rtf" for reading: No such file or directory 
SQL state: 58P01

Is there an option to read the file without changing its name? The underlying system is a Windows server.

The file contents will be eventually read into a table.

Have you check the database encoding? or use the Unicode escape sequence. For example> SELECT pg_stat_file('C:\datafolder\ctk\u00F6ln.rtf', true); — dtc348
– dtc348, Commented Feb 18 at 13:03
As a manual workaround, you might be able to reference the file by its short file name / 8.3 alias. This isn't easily automatable though, because you can't reliably predict the alias which will be assigned to a new file. — IMSoP
– IMSoP, Commented Feb 18 at 13:16
I don't know exactly what's required but — did you one or another Unicode normalization for the file string, either composition or decomposition? The problem is: two canonically equivalent Unicode strings are recognized as different strings if one form is NFC and another one is NFD. Just in case, please try and tell us what happens. — Sergey A Kryukov
– Sergey A Kryukov, Commented Feb 18 at 13:17
I'd rename the file. And tell the creator of the file to avoid non-English letters the next time. — jarlh
– jarlh, Commented Feb 18 at 13:27
@dtc348 I like your idea but you can't just toss in a unicode escape sequence to a regular string constant. You need a (U&'C:\\Users\\Public\\ctk\00F6ln.txt - duplicate other backslashes and prefix the string with U& or the C-style E'' version. — Zegarek
– Zegarek, Commented Feb 18 at 22:32

Laurenz Albe · Accepted Answer · 2025-02-18 13:44:56Z

2

If your database encoding is UTF8 as it should be, you won't be able to read this file using an SQL command. So to read the file with pg_read_file(), you'd have to rename the file.

answered Feb 18 at 13:44

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres filenames with german umlaut

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related