Postgresql – Load data into postgresql from file with trailing delimiter

postgresql

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:

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.
Add a dummy column onto each table; don't know a priori which files/tables need this.
Ask the data provider not to do this; it comes from a branch of the US Government, so a very long shot…
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?

Best Answer

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.

Related Solutions

Postgresql – SQL to read XML from file into PostgreSQL database

Similar to this answer to a previous question, and if you don't want the restrictions of pg_read_file() (in short: pg_read_file can't read files outside the database directory, and reads text in the current session's character encoding).

This function works for any path, but needs to be created as superuser:

create or replace function stack.bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function stack.bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

select convert_from(stack.bytea_import('/tmp/test.xml'), 'utf8')::xml;

Postgresql – Reloading .sql file from java into PostgreSQL – Windows

This is probably not the best way to do what you want to do. The "proper" way would probably be to execute the SQL commands in your file via jdbc.

That said, that's a long way from where you are now, and we can probably make what you are trying to do work.

The likely problem is that there is a space in the path to psql.exe.

When you use that particular format of exec, java uses a vanilla string tokenizer to split your string into an array, each entry being separated by a space. So it's going to be trying to execute a command "c:/Program" and pass it parameters, the first of which is "Files/PostgreSQL/9.2/bin/psql.exe" and, um, that isn't going to go so well.

Use exec (String[]) instead: http://docs.oracle.com/javase/6/docs/api/java/lang/Runtime.html#exec(java.lang.String[])

This method doesn't apply the tokenizer, because it's accepting an array, so it doesn't think it needs to.

Try this:

String path = "H:/Data/samplescript_postgres.sql";
final String [] cmd = { "C:/Program Files/PostgreSQL/9.2/bin/psql.exe",
       "-U", "postgres",
       "-d", "sampledb",
       "-f", path 
     };

try {
    reloadProcess = Runtime.getRuntime().exec(cmd);
    if(null!=reloadProcess){
        if(reloadProcess.waitFor()==0){
            System.out.println("Reloaded");
        }
    }
} catch (IOException e) {
    e.printStackTrace();
} catch (InterruptedException e) {
    e.printStackTrace();
}

You can see that I've created the array manually. For readability, I've put the option "-U" and the value "postgres" on the same line, but they're separated by a comma.