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

Пожалуйста, потерпите меня, так как это мой первый пост.

Я пытаюсь запустить команду COPY в PostgreSQL-9.2, чтобы добавить таблицу с разделителями табуляции из файла.txt в базу данных PostgreSQL, например:

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

Я уже создал пустую таблицу с именем "raw_data" в базе данных с помощью команды SQL:

CREATE TABLE raw_data ();

Я продолжаю получать следующее сообщение об ошибке при попытке запустить COPY команда:

ERROR:  extra data after last expected column
CONTEXT:  COPY raw_data, line 1: "  1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34  35  36  ..."

(Числа здесь должны быть заголовками столбцов)

Я не уверен, что это потому, что я не указал столбцы таблицы при создании таблицы БД, но я стараюсь не вводить вручную 800 или столбцы.

Любые предложения о том, как это исправить?

Вот пример того, как выглядит файл.txt:

        1   2   3   4   5   6   7   8   9
binary1 1   1   0   1   1   1   1   1   1
binary2 1   0   0   1   0   1   1   0   0
binary3 1   0   1   1   1   0   0   1   0
binary4 1   1   1   1   0   1   0   1   0
Источник

2 ответа

Решение

Пустая таблица не подойдет.

CREATE TABLE raw_data ();

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

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

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

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

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

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

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

1   2   3   4  -- first row is meant as "column name"
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   tmp_data
WHERE  raw ~~ E'1\t2\t3\t4%';   -- criteria to identify row with col names

Возвращает:

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

Выполните это (после проверки действительности).
затем INSERT данные с этим запросом:

INSERT INTO tbl
SELECT (('(' || replace(replace(replace(
                  raw
                , '1',   't')
                , '0',   'f')
                , E'\t', ',')
             || ')')::tbl).*
FROM   tmp_data
WHERE  raw !~~ E'1\t2\t3\t4%';  -- criteria to exclude row with col names

Или короче / быстрее с translate():

INSERT INTO tbl
SELECT (('(' || translate(raw, E'10\t', 'tf,') || ')')::tbl).*
FROM   tmp_data
WHERE  raw !~~ E'1\t2\t3\t4%';

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

Все сделано.

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

-> Демоверсия SQLfiddle

Источник

Вы можете создать таблицу непосредственно из команды копирования, проверьте опцию HEADER в COPY, например: COPY FROM '/path/to/csv/SourceCSVFile.csv' DELIMITERS ',' CSV HEADER

Источник
Другие вопросы по тегам