I know COPY doesn't support importing files with fixed column widths,
i.e. we can't say field1 is the first 30 characters, and field2 is the
rest of the line. We need a unique delimiter at column 31. (Commercial
Ingres does support this ability.)
I know we tell most people to use sed, Perl, or an ETL tool to convert
files into a format COPY understands, and I think that is a reasonable
answer. However, the file_fdw also reads our COPY format, and in that
case, the data file might be updated regularly and running an ETL
process on it every time it is read is inconvenient.
I asking because I am playing around with file_fdw and I have some log
files with a Unix 'date' prefix, a colon, then some text that might also
contain a colon. It would be ideal if I could read in first 28
characters into field1, then the rest of the line into field2. The only
idea I have is to create a single-column foreign table that reads the
entire line as one field, then a view on top of that the parses the line
into fields, but that seems kind of complex.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ Everyone has their own god. +