«ОШИБКА: дополнительные данные после последнего ожидаемого столбца» при использовании PostgreSQL COPY

Пустая таблица не подойдет. Вам нужна таблица, которая соответствует структуре входных данных. Что-то вроде:

CREATE TABLE raw_data (
  col1 int
, col2 int
  ...
);

Вам не нужно объявлять tab как DELIMITER так как это по умолчанию:

COPY raw_data FROM '/home/Projects/TestData/raw_data.txt';

800 колонок говорите? Такое количество столбцов обычно указывает на проблему с вашим дизайном. В любом случае, есть способы CREATE TABLE скрипт CREATE TABLE .

автоматизация

Предполагая упрощенные необработанные данные

1   2   3   4  -- first row contains "column names"
1   1   0   1  -- tab separated
1   0   0   1
1   0   1   1

Определите другой DELIMITER (тот, который вообще отсутствует в данных импорта) и импортируйте его во временную промежуточную таблицу с одним text столбцом:

CREATE TEMP TABLE tmp_data (raw text);

COPY tmp_data FROM '/home/Projects/TestData/raw_data.txt' WITH (DELIMITER '§');

Этот запрос создает скрипт CREATE TABLE :

SELECT 'CREATE TABLE tbl (col' || replace (raw, E'\t', ' bool, col') || ' bool)'
FROM   (SELECT raw FROM tmp_data LIMIT 1) t;

Более общий и безопасный запрос:

SELECT 'CREATE TABLE tbl('
    ||  string_agg(quote_ident('col' || col), ' bool, ' ORDER  BY ord)
    || ' bool);'
FROM  (SELECT raw FROM tmp_data LIMIT 1) t
     , unnest(string_to_array(t.raw, E'\t')) WITH ORDINALITY c(col, ord);

Возвращает:

CREATE TABLE tbl (col1 bool, col2 bool, col3 bool, col4 bool);

Выполнить после проверки правильности или выполнить динамически, если вы доверяете результату:

DO
$$BEGIN
EXECUTE (
   SELECT 'CREATE TABLE tbl (col' || replace(raw, ' ', ' bool, col') || ' bool)'
   FROM  (SELECT raw FROM tmp_data LIMIT 1) t
   );
END$$;

Затем INSERT данные с помощью этого запроса:

INSERT INTO tbl
SELECT (('(' || replace(replace(replace(
                  raw
                , '1',   't')
                , '0',   'f')
                , E'\t', ',')
             || ')')::tbl).*
FROM   (SELECT raw FROM tmp_data OFFSET 1) t;

Или проще с translate() :

INSERT INTO tbl
SELECT (('(' || translate(raw, E'10\t', 'tf,') || ')')::tbl).*
FROM   (SELECT raw FROM tmp_data OFFSET 1) t;

Строка преобразуется в литерал строки, приводится к вновь созданному типу строки таблицы и декомпозируется с помощью (row).* .

Все сделано.

Вы можете поместить все это в функцию plpgsql, но вам нужно будет защититься от внедрения SQL. (Здесь есть несколько связанных с этим решений. Попробуйте поиск.

дБ <> скрипка здесь
Старая SQL скрипка