#sql #postgresql #csv #copy
#sql #postgresql #csv #Копировать
Вопрос:
Я новичок в PostgreSQL и ищу некоторые рекомендации и наилучшую практику.
Я создал таблицу путем импорта данных из файла CSV. Затем я изменил таблицу, создав несколько сгенерированных столбцов, подобных этому:
ALTER TABLE master
ADD office VARCHAR(50)
GENERATED ALWAYS AS (CASE WHEN LEFT(location,4)='Chic' THEN 'CHI'
ELSE LEFT(location,strpos(location,'_')-1) END) STORED;
Но когда я пытаюсь импортировать новые данные в таблицу, я получаю следующую ошибку:
ERROR: column "office" is a generated column
DETAIL: Generated columns cannot be used in COPY.
Моя цель — иметь возможность каждый день импортировать новые данные в таблицу и автоматически заполнять сгенерированные столбцы, чтобы преобразовать данные так, как я хотел. Как я могу это сделать?
Комментарии:
1. Содержат ли ваши импортируемые данные (файл CSV) столбец «office»? Какую команду вы выполняете при импорте новых данных?
2. @Johnny файл CSV не содержит office, office — это сгенерированный столбец на основе столбца «местоположение» в csv. Я просто использую опцию импорта для импорта новых данных, которые, как я полагаю, используют функцию копирования. я мог бы использовать другую функцию, просто не уверен, как
Ответ №1:
CREATE TEMP TABLE master (location VARCHAR);
ALTER TABLE master
ADD office VARCHAR
GENERATED ALWAYS AS (
CASE
WHEN LEFT(location, 4) = 'Chic' THEN 'CHI'
ELSE LEFT(location, strpos(location, '_') - 1)
END
) STORED;
--INSERT INTO master (location) VALUES ('Chicago');
--INSERT INTO master (location) VALUES ('New_York');
COPY master (location) FROM $$d:cities.csv$$ CSV;
SELECT * FROM master;
Это та структура и поведение, которые вы ожидаете? Если нет, пожалуйста, предоставьте более подробную информацию о структуре вашей таблицы, ваших импортируемых данных и ваших командах импорта.
Кроме того, возможно, при попытке импортировать файл CSV столбцы не связаны должным образом или, возможно, разделитель установлен неправильно. Попробуйте указать каждый столбец в точном порядке, который отображается в вашем файле CSV.
https://www.postgresql.org/docs/12/sql-copy.html
Примечание: d:cities.csv содержит:
Chicago
New_York
Редактировать:
Если позиции столбцов перепутаны между таблицей и csv, может пригодиться следующая операция:
1. create temporary table tmp (csv_column1 <data_type>, csv_column_2 <data_type>, ...); (including ALL csv columns)
2. copy tmp from '/path/to/file.csv';
3. insert into master (location, other_info, ...) select csv_column_3 as location, csv_column_7 as other_info, ... from tmp;
Импорт данных с использованием промежуточной таблицы может немного замедлить работу, но дает вам большую гибкость.
Комментарии:
1. Спасибо! Я думаю, что это близко, но у меня много столбцов в этом csv, местоположение — только один из них. Существуют также дополнительные сгенерированные столбцы. Так должен ли я вызывать каждый столбец конкретно? После попытки выше я получаю следующую ошибку: ОШИБКА: дополнительные данные после последнего ожидаемого контекста столбца: КОПИРОВАНИЕ посещений, строка 1: «col1, col2, col3»
2. Эта ошибка может возникнуть из-за того, что не все столбцы CSV были указаны в команде КОПИРОВАНИЯ или количество столбцов CSV не согласовано во всех строках. Если позиции столбцов перепутаны (сравнение таблицы и файла csv), то я рекомендую выполнить эту операцию в три четких шага, используя временные таблицы (я отредактировал ответ с подробностями). Кроме того, для отладки я бы рекомендовал протестировать csv, тщательно проверив всего несколько строк (1-2), потому что, возможно, CSV содержит строки, неожиданно неправильно отформатированные.
Ответ №2:
Я получал ту же ошибку при импорте в PG из CSV — я обнаружил, что, хотя мой столбец был сгенерирован, он все равно должен был присутствовать в импортированных данных, просто оставил его пустым. Работал нормально, когда имя столбца было там и сопоставлялось с именем моего каталога DB.