1

I am trying to import big fixed length files into Postgres and I am using the copy command and a staging table following this guide.

The problem is that this files has a lot of different characters and i don't know which to use as delimiter. In fact I don't need a delimiter at all as I am importing files in a single column table.

0

You cannot use COPY or \copy if there is no character which is certain not to appear in the data. You will either need to pick a character to act as the delimiter, then escape or quote it and the escape/quote character in the input file (perhaps using PROGRAM). Or, not use COPY or \copy, but rather use INSERT instead.

It isn't clear from your question if your files have newlines as record separators or if every file is one record and internal newlines are literal. If the latter you do use COPY or \copy, you will have to escape/quote that as well.

0

Postgres can't handle that format but it can candle CSV and translation is fairly easy.

sed 's/"/""/g;s/^\|$/"/g' will translate a text file to a single column CSV file which can then be read by copy.

so your import might go something like

sed 's/"/""/g;s/^\|$/"/g'_yourfile_ | psql _yourdatabase_ -C copy _yourtable_ from stdin with CSV

sed 's/"/""/g;1 s/^/"/;$ s/$/"/' will translate a text file to a single CSV record should you require that translation instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.