Load data into postgresql from file with trailing delimiter

Question

Context

I receive a number of data files that I'd like to load on a monthly basis into Postgresql (currently running 11.4). However, some of the files each month are formatted such that they have a trailing delimiter at the end of each line.

For example, if I have the following table:

CREATE TABLE t1 (
  id integer,
  make varchar(50),
  model varchar(50)
);

The corresponding file looks like:

1|Ford|Taurus|
2|Honda|Civic|
3|Toyota|Camry|

Note that while the | character is used as a delimiter, there is an "extra" one at the end of each line -- which causes a load to fail:

=> BEGIN TRANSACTION;
BEGIN
=> TRUNCATE TABLE t1;
TRUNCATE TABLE
=> \copy t1 (id, make, model) FROM t1.dat WITH FREEZE NULL '' DELIMITER '|';
ERROR:  extra data after last expected column
CONTEXT:  COPY t1, line 1: "1|Ford|Taurus|"
=> COMMIT;
ROLLBACK

Solutions I've thought of so far:

1. Load into a temp table that has an extra dummy column, then INSERT AS SELECT; however I believe that loses the speed/efficiency of COPY WITH FREEZE, plus you touch the data twice which I'm guessing is bad as the size of it grows.
2. Add a dummy column onto each table; don't know a priori which files/tables need this.
3. Ask the data provider not to do this; it comes from a branch of the US Government, so a very long shot...
4. Pre-process the files to remove any / a single trailing delimiters; again don't know a priori which files will need this and it could change month to month.

Anyone have any other ideas or recommendations on how to solve this?

Answer 1

You could also pre-process the file as suggested by Vérace:

Or sed s'/.$//'

from How to shave off last character using sed? by codaddict on Stack Overflow.

Or sed -i ... (inplace - I tend to avoid this, because in case of a mistake, you've lost your original file) or sed .... > table.txt.

Answer 2

Your option 1 is really just a subclass of option 4, in which you are using PostgreSQL to do the preprocessing. That is my prefered option. If you preprocess with something else, you have to worry about how that something else deals with escaping and quoting and multi-line strings. Better to worry about that stuff with one thing rather than two things. I wouldn't worry about the performance impact until I've measured it.

Are you really using COPY...WITH FREEZE? (From your edit now I see that you are) That only works if the table was created or truncated in the same transaction as the COPY. So it only works for replacing data, not appending it. If that is what you are doing, then a variant of option 2 looks favorable, drop the table, create the table with the dummy column, load, then drop the dummy column after the load.

Option 3 is not mutually exclusive. You can make the request, and then proceed on the assumption that the answer will eventually be 'no'.

I don't understand your objection to option 4. Remove exactly one | from the end. Don't remove all | from the end. You said that only some of the files have this. Is it always consistent throughout any given file in any given month or does it vary from line to line?

You could use the PROGRAM option for \copy to do this on the fly, once you know whether it needs to be done.

\copy t1 (id, make, model) FROM PROGRAM 'perl -pe "s/\|$// or die" t1.dat' WITH FREEZE NULL '' DELIMITER '|';

One benefit of that is that you don't have to save and clean up temp files. The downside is you don't get good error detection, if the perl scripts exits abnormally it doesn't cause the transaction the \copy is in to fail.

Answer 3

Pre-process the file. A simple

perl -pe 's/(.*\|.*\|.*)(\|)$/$1/'

should do the trick.