Пожалуйста, несите меня, поскольку это мой первый пост.
Я пытаюсь запустить команду 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 ..."
(Числа здесь должны быть заголовками столбцов)
Я не уверен, что это потому, что я не указывал столбцы таблицы при создании таблицы db, но я стараюсь не вводить вручную 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
Пустая таблица не подойдет. Вам нужна таблица, которая соответствует структуре входных данных. Что-то вроде:
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 скрипка
вы можете создать таблицу из команды копирования напрямую, проверить опцию HEADER в COPY, например: COPY FROM '/path/to/csv/SourceCSVFile.csv DELIMITERS', 'CSV HEADER