Какая команда используется для импорта в postgresql

Какая команда используется для импорта в postgresql

COPY — копировать данные между файлом и таблицей

Синтаксис

COPY имя_таблицы [ ( имя_столбца [, . ] ) ] FROM < 'имя_файла' | PROGRAM 'команда' | STDIN > [ [ WITH ] ( параметр [, . ] ) ] COPY < имя_таблицы [ ( имя_столбца [, . ] ) ] | ( запрос ) > TO < 'имя_файла' | PROGRAM 'команда' | STDOUT > [ [ WITH ] ( параметр [, . ] ) ] Здесь допускается параметр: FORMAT имя_формата OIDS [ boolean ] FREEZE [ boolean ] DELIMITER 'символ_разделитель' NULL 'маркер_NULL' HEADER [ boolean ] QUOTE 'символ_кавычек' ESCAPE 'символ_экранирования' FORCE_QUOTE < ( имя_столбца [, . ] ) | * > FORCE_NOT_NULL ( имя_столбца [, . ] ) FORCE_NULL ( имя_столбца [, . ] ) ENCODING 'имя_кодировки'

Описание

COPY перемещает данные между таблицами PostgreSQL и обычными файлами в файловой системе. COPY TO копирует содержимое таблицы в файл, а COPY FROM — из файла в таблицу (добавляет данные к тем, что уже содержались в таблице). COPY TO может также скопировать результаты запроса SELECT .

Если указывается список столбцов, COPY TO копирует в файл только данные указанных столбцов, а COPY FROM вставляет каждое поле из файла в соответствующий ему по порядку столбец из указанного списка. В случае отсутствия в этом списке каких-либо столбцов таблицы при COPY FROM они получают значения по умолчанию.

COPY с именем файла указывает серверу PostgreSQL читать или записывать непосредственно этот файл. Заданный файл должен быть доступен пользователю PostgreSQL (тому пользователю, от имени которого работает сервер), и путь к файлу должен задаваться с точки зрения сервера. Когда указывается параметр PROGRAM , сервер выполняет заданную команду и читает данные из стандартного вывода программы, либо записывает их в стандартный ввод. Команда должна определяться с точки зрения сервера и быть доступной для исполнения пользователю PostgreSQL . Когда указывается STDIN или STDOUT , данные передаются через соединение клиента с сервером.

Параметры

имя_таблицы

Имя существующей таблицы (возможно, дополненное схемой). имя_столбца

Необязательный список столбцов, данные которых будут копироваться. Если этот список отсутствует, копируются все столбцы таблицы. запрос

Команда SELECT или VALUES , результаты которой будут скопированы. Заметьте, что запрос должен заключаться в скобки. имя_файла

Путь входного или выходного файла. Путь входного файла может быть абсолютным или относительным, но путь выходного должен быть только абсолютным. Пользователям Windows следует использовать формат E» и продублировать каждую обратную черту в пути файла. PROGRAM

Выполняемая команда. COPY FROM читает стандартный вывод команды, а COPY TO записывает в её стандартный ввод.

Заметьте, что команда запускается через командную оболочку, так что если требуется передать этой команде какие-либо аргументы, поступающие из недоверенного источника, необходимо аккуратно избавиться от всех спецсимволов, имеющих особое значение в оболочке, либо экранировать их. По соображениям безопасности лучше ограничиться фиксированной строкой команды или как минимум не позволять пользователям вводить в неё произвольное содержимое. STDIN

Указывает, что данные будут поступать из клиентского приложения. STDOUT

Указывает, что данные будут выдаваться клиентскому приложению. boolean

Включает или отключает заданный параметр. Для включения параметра можно написать TRUE , ON или 1 , а для отключения — FALSE , OFF или 0 . Значение boolean можно опустить, в этом случае подразумевается TRUE . FORMAT

Выбирает формат чтения или записи данных: text (текстовый), csv (значения, разделённые запятыми, Comma Separated Values) или binary (двоичный). По умолчанию выбирается формат text . OIDS

Копирует OID каждой строки. (Если присутствует указание OIDS , но таблица не содержит столбец oid, либо копируется запрос , возникнет ошибка.) FREEZE

Запросы копируют данные с уже замороженными строками, как после выполнения команды VACUUM FREEZE . Это позволяет увеличить производительность при начальном добавлении данных. Строки будут замораживаться, только если загружаемая таблица была создана или опустошена в текущей подтранзакции, с ней не связаны открытые курсоры и в данной транзакции нет других снимков.

Заметьте, что все другие сеансы будут немедленно видеть данные, как только они будут успешно загружены. Это нарушает принятые правила видимости MVCC, так что пользователи, включающие этот режим, должны понимать, какие проблемы это может вызвать. DELIMITER

Задаёт символ, разделяющий столбцы в строках файла. По умолчанию это символ табуляции в текстовом формате и запятая в формате CSV . Задаваемый символ должен быть однобайтовым. Для формата binary этот параметр не допускается. NULL

Определяет строку, задающую значение NULL. По умолчанию в текстовом формате это \N (обратная косая черта и N), а в формате CSV — пустая строка без кавычек. Пустую строку можно использовать и в текстовом формате, если не требуется различать пустые строки и NULL. Для формата binary этот параметр не допускается.

Примечание

При выполнении COPY FROM любые значения, совпадающие с этой строкой, сохраняются как значение NULL, так что при переносе данных важно убедиться в том, что это та же строка, что применялась в COPY TO .

Указывает, что файл содержит строку заголовка с именами столбцов. При выводе первая строка файла будет содержать имена столбцов таблицы, а при вводе первая строка просто игнорируется. Этот параметр допускается только для формата CSV . QUOTE

Указывает символ кавычек, используемый для заключения данных в кавычки. По умолчанию это символ двойных кавычек. Задаваемый символ должен быть однобайтовым. Этот параметр поддерживается только для формата CSV . ESCAPE

Задаёт символ, который будет выводиться перед символом данных, совпавшим со значением QUOTE . По умолчанию это тот же символ, что и QUOTE (то есть, при появлении в данных кавычек, они дублируются). Задаваемый символ должен быть однобайтовым. Этот параметр допускается только для режима CSV . FORCE_QUOTE

Принудительно заключает в кавычки все значения не NULL в указанных столбцах. Выводимое значение NULL никогда не заключается в кавычки. Если указано * , в кавычки будут заключаться значения не NULL во всех столбцах. Этот параметр принимает только команда COPY TO и только для формата CSV . FORCE_NOT_NULL

Не сопоставлять значения в указанных столбцах с маркером NULL. По умолчанию, когда маркер пуст, это означает, что пустые значения будут считаны как строки нулевой длины, а не NULL, даже когда они не заключены в кавычки. Этот параметр допускается только в команде COPY FROM и только для формата CSV . FORCE_NULL

Сопоставлять значения в указанных столбцах с маркером NULL, даже если они заключены в кавычки, и в случае совпадения устанавливать значение NULL . По умолчанию, когда этот маркер пуст, пустая строка в кавычках будет преобразовываться в NULL. Этот параметр допускается только в команде COPY FROM и только для формата CSV . ENCODING

Указывает, что файл имеет кодировку имя_кодировки . Если этот параметр опущен, выбирается текущая кодировка клиента. Подробнее об этом говорится ниже, в примечаниях.

Выводимая информация

В случае успешного завершения, COPY возвращает метку команды в виде

COPY число 

Здесь число — количество скопированных записей.

Примечание

psql выводит эту метку, только если выполнялась не команда COPY . TO STDOUT или её аналог в psql , метакоманда \copy . to stdout . Это сделано для того, чтобы метка команды не смешалась с данными, выведенными перед ней.

Замечания

COPY может использоваться только с обычными таблицами, но не с представлениями. Однако, при необходимости можно скопировать представление так: COPY (SELECT * FROM имя_представления ) TO . .

COPY обрабатывает только явно заданную таблицу, дочерние таблицы при копировании данных не затрагиваются. Поэтому, например COPY таблица TO выводит те же данные, что и запрос SELECT * FROM ONLY table . Для выгрузки всех данных в иерархии наследования можно применить COPY (SELECT * FROM table ) TO . .

В таблице, данные которой читает команда COPY TO , требуется иметь право на выборку данных, а в таблице, куда вставляет значения COPY FROM , требуется право на добавление. При этом, если в команде перечисляются избранные столбцы, достаточно иметь права только для них.

Если для таблицы включена защита на уровне строк, соответствующие политики SELECT будут применяться и к операторам COPY таблица TO . Операторы COPY FROM для таблиц с защитой строк в настоящее время не поддерживаются. Вместо них следует использовать равнозначные операторы INSERT .

Файлы, указанные в команде COPY , читаются или записываются непосредственно сервером, не клиентским приложением. Поэтому они должны располагаться на сервере или быть доступными серверу, а не клиенту. Они должны быть доступны на чтение или запись пользователю PostgreSQL (пользователю, от имени которого работает сервер), не клиенту. Аналогично, команда, указанная параметром PROGRAM , выполняется непосредственно сервером, а не клиентским приложением, и должна быть доступна на выполнение пользователю PostgreSQL . Выполнять команду COPY с файлом (или командой) разрешено только суперпользователям базы данных, так как она позволяет прочитать и записать любой файл, к которому имеет доступ сервер.

Не путайте команду COPY с реализованной в psql метакомандой \copy . Метакоманда \copy вызывает COPY FROM STDIN или COPY TO STDOUT , а затем работает с данными в файле, доступном клиенту psql . Таким образом, когда применяется команда \copy , доступность файла и права доступа зависят от клиента, а не от сервера.

Путь файла, указываемый в COPY , рекомендуется всегда задавать как абсолютный, а не относительный. Это обязательное условие для команды COPY TO , но COPY FROM позволяет прочитать файл, заданный и относительным путём. Такой путь будет интерпретироваться относительно рабочего каталога серверного процесса (обычно это каталог данных кластера), а не рабочего каталога клиента.

Выполнение команды в PROGRAM может быть ограничено и другими работающими в ОС механизмами контроля доступа, например SELinux.

COPY FROM вызывает все триггеры и обрабатывает все ограничения-проверки в целевой таблице. Однако правила при загрузке данных не вызываются.

При вводе и выводе данных COPY учитывается DateStyle . Для обеспечения переносимости на другие инсталляции PostgreSQL , в которых могут использоваться нестандартные значения DateStyle , значение DateStyle следует установить равным ISO до вызова COPY TO . Также рекомендуется не выгружать данные с IntervalStyle равным sql_standard , так как сервер с другим значением IntervalStyle может неправильно воспринимать отрицательные интервалы в таких данных.

Входные данные интерпретируются согласно кодировке, заданной параметром ENCODING , или текущей кодировке клиента, а выходные кодируются в кодировке ENCODING или текущей кодировке клиента, даже если данные не проходят через клиента, а считываются или записываются в файл непосредственно сервером.

COPY прекращает операцию при первой ошибке. Это не должно приводить к проблемам в случае с COPY TO , но после COPY FROM в целевой таблице остаются ранее полученные строки. Эти строки не будут видимыми и доступными, но будут занимать место на диске. Если сбой происходит при копировании большого объёма данных, это может приводить к значительным потерям дискового пространства. При желании вернуть потерянный объём, это можно сделать с помощью команды VACUUM .

FORCE_NULL и FORCE_NOT_NULL можно применить одновременно к одному столбцу. В результате NULL-значения в кавычках будут преобразованы в NULL, а NULL-значения без кавычек — в пустые строки.

Форматы файлов

Текстовый формат

Когда применяется формат text , читаемые или записываемые данные представляют собой текстовый файл, строка в котором соответствует строке таблицы. Столбцы в строке разделяются символом-разделителем. Значения самих столбцов — текстовые строки, выдаваемые функцией вывода, либо воспринимаемые функцией ввода, соответствующей типу данных столбца. Заданный маркер NULL выводится и считывается вместо столбцов со значением NULL. COPY FROM выдаёт ошибку, если в любой из строк во входном файле оказывается больше или меньше столбцов, чем ожидается. С указанием OIDS значение OID считывается или записывается в первом столбце, предшествующем столбцам с основными данными.

Конец данных может обозначаться одной строкой, содержащей только обратную косую и точку ( \. ). Маркер конца данных не требуется при чтении из файла, так как его роль вполне выполняет конец файла; он необходим только при передаче данных в/из клиентского приложения по протоколу обмена до версии 3.0.

Символы обратной косой черты ( \ ) в данных COPY позволяют экранировать символы данных, которые без них считались бы разделителями строк или столбцов. В частности, предваряться обратной косой должны следующие символы, когда они оказываются в значении столбца: сама обратная косая черта, перевод строки, возврат каретки и текущий разделитель.

Маркер NULL передаётся команде COPY TO как есть, без добавления обратной косой; COPY FROM , со своей стороны, ищет во вводимых данных маркеры NULL до удаления обратных косых. Таким образом, маркер NULL, например такой как \N , отличается от значения \N в данных (оно должно представляться в виде \\N ).

Команда COPY FROM распознаёт следующие спецпоследовательности:

Последовательность	Представляет
\b	Забой (ASCII 8)
\f	Подача формы (ASCII 12)
\n	Новая строка (ASCII 10)
\r	Возврат каретки (ASCII 13)
\t	Табуляция (ASCII 9)
\v	Вертикальная табуляция (ASCII 11)
\ цифры	Обратная косая с последующими 1-3 восьмеричными цифрами представляет символ с заданным числовым кодом
\x цифры	Обратная косая с последующим x и 1-2 шестнадцатеричными цифрами представляет символ с заданным числовым кодом

В настоящее время COPY TO никогда не выводит спецпоследовательности с восьмеричными или шестнадцатеричными кодами, однако выводит другие вышеперечисленные спецпоследовательности вместо управляющих символов.

Любой другой символ после обратной косой, отсутствующий в приведённой выше таблице, будет представлять себя. Однако опасайтесь излишнего добавления обратных косых, так как это может привести к случайному образованию строки, обозначающей маркер конца данных ( \. ) или маркер NULL ( \N по умолчанию). Эти строки будут восприняты прежде, чем обработаются спецпоследовательности с обратной косой.

В приложениях, генерирующих данные для COPY , настоятельно рекомендуется преобразовать символы новой строки и возврата каретки в последовательности \n и \r , соответственно. В настоящее время можно представить возврат каретки в данных как обратная косая и возврат каретки, а перевод строки как обратная косая и перевод строки, однако это может не поддерживаться в будущих версиях. Такие символы также подвержены искажениям, если файл с выводом COPY переносится между разными системами (например, с Unix в Windows и наоборот).

COPY TO завершает каждую строку символом новой строки в стиле Unix ( « \n » ). Серверы, работающие в Microsoft Windows, вместо этого выводят символы возврат каретки/новая строка ( « \r\n » ), но только при выводе COPY в файл на сервере; для согласованности на разных платформах, COPY TO STDOUT всегда передаёт « \n » , вне зависимости от платформы сервера. COPY FROM может воспринимать строки, завершающиеся символами новая строка, перевод каретки, либо возврат каретки+новая строка. Чтобы уменьшить риск ошибки из-за неэкранированных символов новой строки и возврата каретки, которые должны были быть данными, COPY FROM сигнализирует о проблеме, если концы строк во входных данных различаются.

Формат CSV

Этот формат применяется для импорта и экспорта данных в виде списка значений, разделённых запятыми ( CSV ), с которым могут работать многие другие программы, например электронные таблицы. Вместо правил экранирования значений, введённых в PostgreSQL для текстового формата, этот формат использует стандартный механизм экранирования CSV.

Значения в каждой записи разделяются символами DELIMITER . Если значение содержит символ разделителя, символ QUOTE , маркер NULL , символ возврата каретки или перевода строки, то всё значение дополнятся спереди и сзади символами QUOTE , а любое вхождение символа QUOTE или спецсимвола ( ESCAPE ) в данных предваряется спецсимволом. С указанием FORCE_QUOTE в кавычки будут принудительно заключаться любые значения не NULL в указанных столбцах.

В формате CSV отсутствует стандартный способ отличить значение NULL от пустой строки. В PostgreSQL команда COPY решает это с помощью кавычек. Значение NULL выводится в виде строки, задаваемой параметром NULL , и не заключается в кавычки, тогда как значение не NULL , со строкой, задаваемой параметром NULL , заключается. Например, с параметрами по умолчанию NULL записывается в виде пустой строки без кавычек, тогда как пустая строка записывается в двойных кавычках ( «» ). При чтении значений действуют похожие правила. Указание FORCE_NOT_NULL позволяет избежать сравнений на NULL во входных данных в заданных столбцах, а FORCE_NULL — преобразовывать в NULL маркеры NULL, даже заключённые в кавычки.

Так как обратная косая черта не является спецсимволом в формате CSV , маркер конца данных \. может быть и значением данных. Во избежание ошибок интерпретации данные \. , выводимые в виде единственного элемента строки, автоматически заключаются в кавычки при выводе, а при вводе этот маркер, заключённый в кавычки, не воспринимается как маркер конца данных. При загрузке файла, созданного другой программой, в котором в единственном столбце без кавычек оказалось значение \. , потребуется дополнительно заключить это значение в кавычки.

Примечание

В формате CSV все символы являются значимыми. Заключённое в кавычки значение, дополненное пробелами или любыми другими символами, кроме DELIMITER , будет включать и эти символы. Это может приводить к ошибкам при импорте данных из системы, дополняющей строки CSV пробельными символами до некоторой фиксированной ширины. В случае возникновения такой проблемы необходимо обработать файл CSV и удалить из него замыкающие пробельные символы, прежде чем загружать данные из него в PostgreSQL .

Примечание

Обработчик формата CSV воспринимает и генерирует файлы CSV со значениями в кавычках, которые могут содержать символы возврата каретки и перевода строки. Таким образом, число строк в этих файлах не строго равно числу строк в таблице, как в файлах текстового формата.

Примечание

Многие программы генерируют странные и иногда неприемлемые файлы CSV, так что этот формат используется скорее по соглашению, чем по стандарту. Поэтому вам могут встретиться файлы, которые невозможно импортировать, используя этот механизм, а COPY может сформировать такие файлы, что их не смогут обработать другие программы.

Двоичный формат

При выборе формата binary все данные сохраняются/считываются в двоичном, а не текстовом виде. Иногда этот формат обрабатывается быстрее, чем текстовый и CSV , но он может оказаться непереносимым между разными машинными архитектурами и версиями PostgreSQL . Кроме того, двоичный формат сильно зависит от типов данных; например, он не позволяет вывести данные из столбца smallint , а затем прочитать их в столбец integer , хотя с текстовым форматом это вполне возможно.

Формат binary включает заголовок файла, ноль или более записей, содержащих данные строк, и окончание файла. Для заголовков и данных принят сетевой порядок байт.

Примечание

В PostgreSQL до версии 7.4 использовался другой двоичный формат.

Заголовок файла

Заголовок файла содержит 15 байт фиксированных полей, за которыми следует область расширения заголовка переменной длины. Фиксированные поля:

Последовательность из 11 байт PGCOPY\n\377\r\n\0 — заметьте, что нулевой байт является обязательной частью сигнатуры. (Эта сигнатура позволяет легко выявить файлы, испорченные при передаче, не сохраняющей все 8 бит данных. Она изменится при прохождении через фильтры, меняющие концы строк, отбрасывающие нулевые байты или старшие биты, либо добавляющие чётность.) Поле флагов

Маска из 32 бит, обозначающая важные аспекты формата файла. Биты нумеруются от 0 ( LSB ) до 31 ( MSB ). Учтите, что это поле хранится в сетевом порядке байт (наиболее значащий байт первый), как и все целочисленные поля в этом формате. Биты 16-31 зарезервированы для обозначения критичных особенностей формата; обработчик должен прервать чтение, встретив любой неожиданный бит в этом диапазоне. Биты 0-15 зарезервированы для обозначения особенностей, связанных с обратной совместимостью; обработчик может просто игнорировать любые неожиданные биты в этом диапазоне. В настоящее время определён только один битовый флаг, остальные должны быть равны 0:

При 1 в данные включается OID; при 0 — нет

Длина области расширения заголовка

Целое 32-битное число, определяющее длину в байтах остального заголовка, не включая само это значение. В настоящее время содержит 0, и сразу за ним следует первая запись. При будущих изменениях формата в заголовок могут быть добавлены дополнительные данные. Обработчик должен просто пропускать все расширенные данные заголовка, о которых ему ничего не известно.

Область расширения заголовка предусмотрена для размещения последовательности самоопределяемых блоков. Поле флагов не должно содержать указаний о том, что содержится в области расширения. Точное содержимое области расширения может быть определено в будущих версиях.

При таком подходе возможно как обратно-совместимое дополнение заголовка (добавить блоки расширения заголовка или установить младшие биты флагов), так и не обратно-совместимое (установить старшие биты флагов, сигнализирующие о подобном изменении, и добавить вспомогательные данные в область расширения, если это потребуется).

Записи

Каждая запись начинается с 16-битного целого числа, определяющего количество полей в записи. (В настоящее время во всех записях должно быть одинаковое число полей, но так может быть не всегда.) Затем, для каждого поля в записи указывается 32-битная длина поля, за которой следует это количество байт с данными поля. (Значение длины не включает свой размер, и может быть равно нулю.) В качестве особого варианта, -1 обозначает, что в поле содержится NULL. В случае с NULL за длиной не следуют байты данных.

Выравнивание или какие-либо дополнительные данные между полями не вставляются.

В настоящее время предполагается, что все значения данных в файле двоичного формата содержатся в двоичном формате (формате под кодом 1). Возможно, в будущем расширении в заголовок будет добавлено поле, позволяющее задавать другие коды форматов для разных столбцов.

Чтобы определить подходящий двоичный формат для фактических данных, обратитесь к исходному коду PostgreSQL , в частности, к функциям *send и *recv для типов данных каждого столбца (обычно эти функции находятся в каталоге src/backend/utils/adt/ в дереве исходного кода).

Если в файл включается OID, поле OID следует немедленно за числом, определяющим количество полей. Это поле не отличается от других ничем, кроме того, что оно не учитывается в количестве полей. В частности, для него также задаётся длина — это позволяет обрабатывать и четырёх- и восьмибайтовые OID без особых сложностей, и даже вывести OID, равный NULL, если возникнет потребность в этом.

Окончание файла

Окончание файла состоит из 16-битного целого, содержащего -1. Это позволяет легко отличить его от счётчика полей в записи.

Обработчик, читающий файл, должен выдать ошибку, если число полей в записи не равно -1 или ожидаемому числу столбцов. Это обеспечивает дополнительную проверку синхронизации данных.

Примеры

В следующем примере таблица передаётся клиенту с разделителем полей «вертикальная черта» ( | ):

COPY country TO STDOUT (DELIMITER '|');

Копирование данных из файла в таблицу country :

COPY country FROM '/usr1/proj/bray/sql/country_data';

Копирование в файл только данных стран, название которых начинается с ‘A’:

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

Для копирования данных в сжатый файл можно направить вывод через внешнюю программу сжатия:

COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';

Пример данных, подходящих для копирования в таблицу из STDIN :

AF AFGHANISTAN AL ALBANIA DZ ALGERIA ZM ZAMBIA ZW ZIMBABWE

Примечание: пробелы в каждой строке на самом деле обозначают символы табуляции.

Ниже приведены те же данные, но выведенные в двоичном формате. Данные показаны после обработки Unix-утилитой od -c . Таблица содержит три столбца; первый имеет тип char(2) , второй — text , а третий — integer . Последний столбец во всех строках содержит NULL.

0000000 P G C O P Y \n 377 \r \n \0 \0 \0 \0 \0 \0 0000020 \0 \0 \0 \0 003 \0 \0 \0 002 A F \0 \0 \0 013 A 0000040 F G H A N I S T A N 377 377 377 377 \0 003 0000060 \0 \0 \0 002 A L \0 \0 \0 007 A L B A N I 0000100 A 377 377 377 377 \0 003 \0 \0 \0 002 D Z \0 \0 \0 0000120 007 A L G E R I A 377 377 377 377 \0 003 \0 \0 0000140 \0 002 Z M \0 \0 \0 006 Z A M B I A 377 377 0000160 377 377 \0 003 \0 \0 \0 002 Z W \0 \0 \0 \b Z I 0000200 M B A B W E 377 377 377 377 377 377

Совместимость

Оператор COPY отсутствует в стандарте SQL.

До версии PostgreSQL 9.0 использовался и по-прежнему поддерживается следующий синтаксис:

COPY имя_таблицы [ ( имя_столбца [, . ] ) ] FROM < 'имя_файла' | STDIN > [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'символ_разделитель' ] [ NULL [ AS ] 'маркер_NULL' ] [ CSV [ HEADER ] [ QUOTE [ AS ] 'символ_кавычек' ] [ ESCAPE [ AS ] 'символ_экранирования' ] [ FORCE NOT NULL имя_столбца [, . ] ] ] ] COPY < имя_таблицы [ ( имя_столбца [, . ] ) ] | ( запрос ) > TO < 'имя_файла' | STDOUT > [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'символ_разделитель' ] [ NULL [ AS ] 'маркер_NULL' ] [ CSV [ HEADER ] [ QUOTE [ AS ] 'символ_кавычек' ] [ ESCAPE [ AS ] 'символ_экранирования' ] [ FORCE QUOTE < имя_столбца [, . ] | * > ] ] ]

Заметьте, что в этом синтаксисе ключевые слова BINARY и CSV обрабатываются как независимые, а не как аргументы параметра FORMAT .

До версии PostgreSQL 7.3 использовался и по-прежнему поддерживается следующий синтаксис:

COPY [ BINARY ] имя_таблицы [ WITH OIDS ] FROM < 'имя_файла' | STDIN > [ [USING] DELIMITERS 'символ_разделитель' ] [ WITH NULL AS 'маркер_NULL' ] COPY [ BINARY ] имя_таблицы [ WITH OIDS ] TO < 'имя_файла' | STDOUT > [ [USING] DELIMITERS 'символ_разделитель' ] [ WITH NULL AS 'маркер_NULL' ]

Пред.	Наверх	След.
COMMIT PREPARED	Начало	CREATE AGGREGATE

Импорт данных из файлов различных типов в таблицы PostgreSQL

Каким образом можно осуществить импорт информации из файлов Excel/Access/CSV/… (список можно продолжить) в базу данных PostgreSQL? Этот вопрос с завидным постоянством появляется на форумах, конференциях и в списках рассылки, посвященных данной СУБД. Ответы на вопросы, касающиеся импорта данных в PostgreSQL, чаще всего содержат рекомендации по использованию различных (зачастую не опробованных на практике) SQL-скриптов, применению технологии ODBC совместно с приложением, в котором исходный файл был создан, или же советы воспользоваться разнообразными программными инструментами для преобразования данных с последующим вызовом утилиты pgsql. Эти рекомендации могут помочь решить задачу, связанную с импортом данных в БД PostgreSQL, но только в том случае, если исходный файл имеет простую структуру, объем импортируемой информации невелик, а пользователи могут подключаться к серверу напрямую.

Но что если исходный файл с информацией имеет формат Word 2007 или HTML? Или это TXT файл, содержащий Unicode данные? Или же CSV файл, размером несколько сотен мегабайт и имеющий достаточно большое число столбцов? В этой ситуации решения, приведенные выше, нередко не могут дать нужного результата – процесс импорта данных заканчивается ошибкой, исходные данные искажены и перенесены не в полном объеме, при этом сама процедура импорта занимает значительное время.

Простое и эффективное решение задачи импорта данных в PostgreSQL

В данной статье мы рассмотрим программный продукт, специально предназначенный для решения основных задач, связанных с импортом информации в PostgreSQL — EMS Data Import for PostgreSQL. Программа позволяет быстро импортировать данные в таблицы PostgreSQL из файлов MS Excel 97-2007, MS Access, DBF, XML, TXT, CSV, RTF, MS Word 2007, ODF и HTML. Пользователю предоставляется широкий набор возможностей, таких как определение разнообразных параметров импорта для каждого исходного файла в отдельности, осуществление импорта данных в одну или несколько таблиц либо представлений (views), расположенных в одной и той же или различных БД, выбор необходимого режима импортирования. Утилита позволяет использовать специальный режим пакетной вставки для максимально быстрого импорта данных, поддерживает Unicode и все последние версии СУБД PostgreSQL, имеет дружественный и гибкий пользовательский интерфейс, оформленный в виде мастера, который проведет Вас через все шаги импорта информации, а также обладает множеством других полезных возможностей.

При использовании EMS Data Import for PostgreSQL для импорта данных, у пользователя программы существует возможность указать логическое соответствие между столбцами исходного файла и столбцами целевой таблицы, расположенной в БД PostgreSQL, при этом учитывая формат исходного файла. Более того, для большинства форматов исходных файлов программа способна определить такое соответствие автоматически, в случае если исходный файл и целевая таблица имеют сходный порядок столбцов или строк. При настройке процесса импорта пользователь может указать, если это необходимо, индивидуальный формат для каждого импортируемого поля. Это очень полезная возможность программы, когда требуется, например, определить значения для одного или некоторых исходных столбцов в виде констант или же в процессе импорта следует произвести автоматическую замену фрагмента текста в исходных данных на заданное значение. К другой полезной особенности EMS Data Import for PostgreSQL следует отнести возможность определить набор SQL команд, выполняемых непосредственно до или после процесса импорта.

Data Import for PostgreSQL позволяет полностью настроить пользовательский интерфейс под Ваши потребности, а также обладает многоязыковой поддержкой. В случае если сервер PostgreSQL расположен за сетевым брандмауэром и к нему нет возможности подключиться напрямую, утилита способна использовать для подключения SSH или HTTP туннели, при этом для SSH соединений, если это требуется по соображениям безопасности, можно указать открытый и личный криптографический ключ.

Если требуется выполнять импорт данных из файлов в БД PostgreSQL на периодической основе, то Вам достаточно настроить необходимые параметры в программе всего один раз и сохранить конфигурацию в виде специального файла-шаблона. В дистрибутив Data Import for PostgreSQL, помимо программы с графическим интерфейсом, входит консольная утилита, которую можно вызывать по расписанию, и тем самым автоматизировать процесс импорта. Имя ранее сохраненного файла с конфигурацией передается данной консольной утилите в виде параметра командной строки.

Для решения задач, связанных с импортом информации из файлов различных форматов в таблицы БД PostgreSQL, существует большое количество разнообразных программных продуктов, разработанные как на основе open source, так и коммерческие проекты с закрытым исходным кодом. Однако лишь некоторые из этих программ способны предложить пользователю полный набор функций, необходимых для успешного выполнения процесса импорта. EMS Data Import for PostgreSQL – один из немногих программных инструментов, позволяющий решить все основные вопросы, возникающие при решении задачи по импорту данных в БД PostgreSQL.

Следует заметить, что импорт данных – это малая часть из повседневных задач, с которыми сталкиваются администраторы PostgreSQL в их повседневной работе. EMS SQL Management Studio for PostgreSQL поможет Вам значительно упростить задачи, связанные с разработкой баз данных PostgreSQL, администрированием серверов этой СУБД, созданием эффективных SQL запросов, разграничением доступа к данным, сравнением и синхронизацией данных и схем БД, и многие другие.

Импорт и экспорт данных в PostgreSQL, гайд для начинающих

В процессе обучения аналитике данных у человека неизбежно возникает вопрос о миграции данных из одной среды в другую. Поскольку одним из необходимых навыков для аналитика данных является знание SQL, а одной из наиболее популярных СУБД является PostgreSQL, предлагаю рассмотреть импорт и экспорт данных на примере этой СУБД.

В своё время, столкнувшись с импортом и экспортом данных, обнаружилось, что какой-то более-менее структурированной инфы мало: этот момент обходят на всяких там курсах по аналитике, подразумевая, что это очень простые моменты, которым не следует уделять внимание.

В данной статье приведены примеры импорта в PostgreSQL непосредственно самой базы данных в формате sql, а также импорта и экспорта данных в наиболее простом и распространенном формате .csv, в котором в настоящее время хранятся множество существующих датасетов. Формат .json хоть и является также очень распространенным, рассмотрен не будет, поскольку, по моему скромному мнению, с ним все-таки лучше работать на Python, чем в SQL.

1. Импорт базы данных в формате в PostgreSQL

Скачиваем (получаем из внутреннего корпоративного источника) файл с базой данных в выбранную папку. В данном случае путь:

Имя файла: demo-big-20170815

Далее понадобиться командная строка windows или SQL shell (psql). Для примера воспользуемся cmd. Переходим в каталог, где находится скачанная БД, командой cd C:\Users\User-N\Desktop\БД :

Далее выполняем команду для загрузки БД из sql-файла:

«C:\Program Files\PostgreSQL\10\bin\psql» -U postgres -f demo-big-20170815.sql

Где сначала указывается путь, по которому установлен PostgreSQL на компьютере, -U – имя пользователя, -f — название файла БД.

Отметим, что в зависимости от размера базы данных загрузка может занимать до нескольких десятков минут. Конец загрузки будет отмечен следующим видом:

Заходим в pgAdmin и наблюдаем там импортированную БД:

2. Импорт данных из csv-файла

Предполагается, что у вас уже есть необходимый .csv-файл, и первое, что нужно сделать, это перейти pgAdmin и создать там новую базу данных. Ну или воспользоваться уже существующей, в зависимости от текущих нужд. В данном случае была создана БД airtickets.

В выбранной БД создается таблица с полями, типы которых должны соответствовать «колонкам» в выбранном .csv-файле.

Далее воспользуемся SQL shell (psql) для подключения к нужной БД и для подачи команд на импорт данных. При открытии SQL shell (psql) она стандартно спросит про имя сервера, имя подключаемой БД, порт и пользователя. Ввести нужно только имя БД и пароль пользователя, всё остальное проходим нажатием ентра. Создается подключение к нужной БД – airtickets.

Ну и вводим команды на импорт данных из файла:

\COPY tickets FROM ‘C:\Users\User-N\Desktop\CSV\ticket_dataset_MOW.csv’ DELIMITER ‘,’ CSV HEADER;

Где tickets – название созданной в БД таблицы, из – путь, где хранится .csv-файл, DELIMITER ‘,’ – разделитель, используемый в импортируемом .csv-файле, сам формат файла и HEADER , указывающий на заголовки «колонок».

Один интересный момент. Написание команды COPY строчными (маленькими) буквами привело к тому, что psql ругнулся, выдал ошибку и предложил написать команду прописными буквами.

Заходим в pgAdmin и удостоверяемся, что данные были загружены.

3. Экспорт данных в .csv-файл

Предположим, нам надо сохранить таблицу airports_data из уже упоминаемой выше БД demo.

Для этого подключимся к БД demo через SQL shell (psql) и наберем команду, указав уже знакомые параметры разделителя, типа файла и заголовка:

\COPY airports_data TO ‘C:\Users\User-N\Desktop\CSV\airports.csv’ DELIMITER ‘,’ CSV HEADER;

Существует и другой способ экспорта через pgAdmin: правой кнопкой мыши по нужной таблице – экспорт – указание параметров экспорта в открывшемся окне.

4. Экспорт данных выборки в .csv-файл

Иногда возникает необходимость сохранить в .csv-файл не полностью всю таблицу, а лишь некоторые данные, соответствующие некоторому условию. Например, нам нужно из БД demo таблицы flights выбрать поля flight_id, flight_no, departure_airport, arrival_airport, где departure_airport = ‘SVO’. Данный запрос можно вставить сразу в команду psql:

\COPY (SELECT flight_id, flight_no, departure_airport, arrival_airport FROM flights WHERE departure_airport = ‘SVO’) TO ‘C:\Users\User-N\Desktop\CSV\flights_SVO.csv’ CSV HEADER DELIMITER ‘,’;

Вот такой небольшой гайд получился.

• Импорт экспорт данных в PostgreSQL
• импорт и экспорт в csv
• psql команда copy

SQL Базовый №4. Импорт и экспорт данных

Если ваши данные находятся в текстовых CSV-файлах, то их можно разом импортировать в базу данных. В PostgreSQL для этого есть команда COPY. Этой командой можно как импортировать данные, так и экспортировать.

3 шага для импорта данных из CSV:

1. Подготовить CSV файл
2. Создать таблицу в базе данных
3. Выполнить импорт данных из CSV файла в заготовленную таблицу с использованием команды COPY

Работа с CSV-файлами

Многие приложения хранят данные в своих собственных уникальных форматах. Такие форматы сложно прочитать и конвертировать в нужный вам формат. К счастью, большинство программных продуктов позволяют экспортировать данные в формат CSV.

Каждая строка CSV-файла — это строка таблицы. В каждой строке значения столбцов разделены каким-то символом. Это может быть любой символ. В России в роли разделителя чаще всего используется двоеточие. На западе чаще всего применяется запятая.

Обычная строка CSV-файла выглядит примерно так:

1,Assumption Cathedral,Central Administrative District,Tver district,Kremlin,Lenin's Library,Sokolnica line,(495) 695-37-76,assumption-cathedral.kreml.ru,"37,617071","55,751012"

Разделители отделяют данные разных столбцов друг от друга. Используется одна запятая без пробела после нее.

Кавычки

Значения разных столбцов разделены запятыми. А что делать, если само значение содержит запятые? Например, в таблице есть столбцы широты и долготы, в которых целые части от дробных отделены запятыми. Если столбец содержит разделитель, то все его значения должны начинаться и заканчиваться специальным символом text qualifier. Чаще всего это двойные кавычки.

При импорте база данных поймет, что значение в кавычках — это одно значение не смотря на то, что оно содержит разделитель. PostgreSQL по умолчанию игнорирует разделители, которые находятся внутри кавычек.

Строка заголовка

В CSV-файле обычно присутствует заголовок. Это строка, в которой перечислены имена столбцов. Выглядит она примерно так:

ID,Name,AdmArea,District,Address,MetroStation,MetroLine,PublicPhone,WebSite,Longitude_WGS84,Latitude_WGS84

Некоторые СУБД сверяют имя столбца из файла CSV с названием столбца в таблице базы данных. В PostgreSQL такого функционала нет. Чтобы избежать ошибок нужно пропустить строку заголовка, если такая имеется. Для этого используется ключевое слово HEADER.

Импорт данных с помощью COPY

Чтобы импортировать данные из CSV-файла сначала нужно проверить сам источник, потом создать таблицу в базе данных. Далее нужно выполнить простой код из трех строк.

copy имя_таблицы_в_которую_импортируются_данные from 'путь_к_файлу_из_которого_копируются' with (format CSV, header);

После ключевого слова WITH указываются параметры импорта. В данном случае указано, что формат файла источника — это CSV, в первой строке которого находятся заголовки. Параметров бывает много. Чаще всего используются следующие:

• Формат файла. Параметром format имя_формата указывается какой формат файла читается или пишется. Названия форматов: CSV, TXT, BINARY. Чаще всего применятся формат CSV. В файле TXT обычно в роли разделителя выступает табуляция.
• Строка заголовка. Параметр header означает, что в файле в первом столбце находятся заголовки. Этот параметр говорит базе данных, что импортировать данные нужно со второй строки.
• Разделитель. Параметр delimiter ‘символ_разделитель’ указывает какой символ в файле выступает разделителем. Разделителем может быть только 1 символ. Например, если в файле значения столбцов разделяются точкой с запятой, то параметр выглядит так: delimiter ‘;’.
• Символ кавычек. Двойные кавычки говорят о том, что данные между ними нужно считать одним значением. Вместо кавычек в CSV-файле может использоваться другой символ. В таком случае нужно воспользоваться параметром quote ‘символ_quote_qualifier’

Создаем таблицу

Создадим таблицу, в которую загрузим данные из CSV-файла с перечнем всех православных храмов Москвы.

create table religion ( id smallint, church_name varchar(300), adm_area varchar(50), district varchar(50), address varchar(100), metro_station varchar(40), metro_line varchar(40), phone varchar(100), site varchar(200), longitude numeric(8, 6), latitude numeric(8, 6) )

copy religion from 'c:\Users\user\Desktop\sql_training\churches.csv' with (format csv, header, delimiter ';', encoding 'WIN1251')

Импорт некоторых столбцов

Если в вашем CSV-файле есть данные только для некоторых столбцов вы все равно можете выполнить импорт. Нужно будет указать какие столбцы есть в данных.

Добавим в нашу таблицы данные по мечетям. В CSV-файле с данными о мечетях нет столбцов MetroStation, MetroLine, Longitude, Latitude. Если попытаться импортировать данные из этого файла в таблицу religion, то вернется ошибка SQL Error [22P04]: ОШИБКА: нет данных для столбца «site».

Названия столбцов в CSV-файле не совпадают с названиями столбцов в базе данных. В таком случае импорт делает в несколько шагов:

1. Создается временная таблица
2. Во временную таблицу импортируются данные из CSV-файла
3. Из временной таблицы в основную таблицу с помощью insert into копируются нужные столбцы
4. Временна таблица удаляется

-- Создание временной таблицы create temporary table mosques ( id smallint, object_name varchar(300), adm_area varchar(50), district varchar(50), address varchar(100), phone varchar(100), email varchar(40), site varchar(200) ) -- Импортируем данные во временную таблицу copy mosques from 'c:\Users\user\Desktop\sql_training\mosques.csv' with (format csv, header, delimiter ';', encoding 'WIN1251') -- Копирование нужных столбцов из временной таблицы insert into religion (id, object_name, adm_area, district, address, phone, site) select id, object_name, adm_area, district, address, phone, site from mosques; -- Удаляем временную таблицу drop table mosques;

Экспорт с помощью COPY

Командой COPY можно не только импортировать данные, но и экспортировать. Разница в том, что теперь вместо ключевого слова FROM используется TO.

Есть 3 варианта экспорта:

1. Таблица целиком
2. Экспорт отдельных столбцов
3. Экспорт результата запроса

-- Экспорт таблицы целиком copy religion to 'c:\Users\user\Desktop\sql_training\full_export.csv' with (FORMAT csv, header, delimiter ';'); -- Экспорт выбранных столбцов copy religion (object_name, district, address) to 'c:\Users\user\Desktop\sql_training\certain_cols_export.csv' with (FORMAT csv, header, delimiter ';'); -- Экспорт результата запроса -- Выбираем столбцы -- Оставляем только храмы из южного района copy (select object_name, district, address, metro_station, metro_line, longitude, latitude from religion where adm_area ilike '%southern%') to 'c:\Users\user\Desktop\sql_training\query_export.csv' with (FORMAT csv, header, delimiter ';');

Экспорт с помощью UI

Вся таблица целиком

Чтобы экспортировать всю таблицу целиком найдите ее в панели Базы данных — Правый клик — Экспорт данных.

Определенные строки и столбцы

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