Как скопировать таблицу из одной схемы в другую [PostgreSQL] ?
Имеется база с двумя схемами — temp и public.
В public находится пустая таблица TableOriginal. В схеме temp подобная таблица отсутсвует.
Необходимо в схему temp скопировать таблицу TableOriginal.
Переименовывать схемы нельзя, поскольку набор таблиц и данных в них различаются.
В SQL Shell (psql) после ввода команды:
CREATE TABLE temp.TableOriginal AS SELECT * FROM public.TableOriginalникаких сообщений или ошибок не показалось, но таблица не создалась.
Подскажите, пожалуйста, как скопировать таблицу?
- Вопрос задан более трёх лет назад
- 13142 просмотра
Как скопировать данные из одной таблицы в другую в PostgreSQL?
как вы определили, что у вас «не получается»? Какие у вас происходят ошибки? Что ожидается получить? Какую конкретно версию PostgreSQL вы используете?
3 фев 2016 в 6:59
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
Для копирования данных между таблицами надо использовать конструкцию
INSERT INTO table_dest (fields_list) SELECT fields_list FROM table_src Это позволяет не только копировать данные, но при необходимости и модифицировать их
Отслеживать
ответ дан 3 фев 2016 в 7:40
3,531 1 1 золотой знак 12 12 серебряных знаков 26 26 бронзовых знаков
Чтобы создать новую таблицу на основе данных запроса, воспользуйтесь конструкцией CREATE TABLE AS . Синтаксис тут
Для вашего примера получится так:
CREATE TABLE "SC_Tag"."tagdata_2015-01_new" ("id_tag", "f_time", "value") AS SELECT "F_TagName_ID", "F_Date", "F_ConvertedValue" FROM "SC_Tag"."tagdata_2015-01" SQL-Ex blog
В этой статье мы обсудим функциональность копирования базы данных PostgreSQL, которое является одним из наиболее важных и часто используемых на практике действий. Имеется два варианта оператора ‘COPY’, копирование базы данных и копирование таблицы. Мы обсудим оба эти варианта.
Сначала мы обсудим процесс копирования базы данных PostgreSQL на том же сервере или с одного сервера на другой, а затем поговорим о различных способах копирования существующей таблицы в новую таблицу на PostgreSQL.
Копирование базы данных в пределах одного и того же сервера
Иногда требуется создать точную копию существующей базы данных на том же сервере с целью разработки и тестирования. PostgreSQL помогает достичь этого с помощью оператора CREATE DATABASE, как показано ниже:
Базовый синтаксис:
CREATE DATABASE target_database
WITH TEMPLATE source_database;
Этот оператор копирует source_database в target_database. Для объяснения сценария давайте рассмотрим приведенный ниже запрос.
CREATE DATABASE demo2
WITH TEMPLATE demo;
Этот запрос дает указание ядру PostgreSQL создать новую базу данных с именем ‘demo2’, используя шаблон существующей базы данных ‘demo’. База данных копируется с сохранением внутренней структуры; это означает, что копируются все схемы и их таблицы и другие объекты, которые становятся доступными в новой базе данных.
Рисунки ниже дают подробное объяснение этого сценария.
До копирования базы данных ‘demo’. База данных ‘demo’ содержит пару схем, что показано выше на первом рисунке.
После выполнения запроса копирования создается база данных ‘demo2’ той же структуры.
Копирование базы данных с одного сервера на другой
Ниже объясняется наиболее эффективный способ копирования базы данных с одного сервера на другой. Процедура состоит из 4 шагов. Давайте рассмотрим эти шаги и соответствующие им команды.
1. Выполнение дампа исходной базы данных в файл SQL.
pg_dump -U postgres -d sourcedb -f sourcedb.sql
2. Копирование файла на целевой сервер посредством putty или любого другого процесса передачи файлов.
3. Создание новой базы данных на целевом сервере.
CREATE DATABASE targetdb;
4. Восстановление файла дампа на целевом сервере.
psql -U postgres -d targetdb -f sourcedb.sql
PostgreSQL — копирование таблицы
Функциональность копирования таблицы помогает скопировать существующую таблицу со всеми ее записями. Давайте рассмотрим базовый синтаксис всех возможных способов сделать это:
Копирование таблицы со всеми данными
CREATE TABLE new_table AS TABLE existing_table;
Пример
Рисунок показывает текущее состояние базы данных. Затем мы выполним следующий запрос копирования, чтобы создать новую таблицу из существующей таблицы books.
CREATE TABLE books2 AS TABLE books;
На этом рисунке мы можем увидеть новую таблицу с именем books2, которая имеет такую же структуру и тот же набор данных.
Копирование таблицы без данных
Также имеется возможность скопировать только структуру, но не данные. Этот сценарий особенно важен для тестирования приложения в разных средах.
CREATE TABLE new_table AS TABLE existing_table
WITH NO DATA;
Давайте создадим новую таблицу с именем books3 из существующей таблицы books для лучшего понимания.
CREATE TABLE books3 AS TABLE books
WITH NO DATA;
Тут важно отметить ответ ядра PostgreSQL после создания таблицы. В предыдущем сценарии ответом был текст SELECT 2′, говорящий о том, что в новую таблицу было вставлено 2 строки, а в этом примере ответ системы отличается — это текст ‘CREATE TABLE AS’, что означает создание таблицы без данных.
Копирование таблицы с частью данных
В этом последнем сценарии мы увидим, как можно создать новую таблицу из существующей с частью данных. Здесь внутренний запрос используется совместно с предложением WHERE для получения желаемого результата.
CREATE TABLE new_table AS
SELECT * FROM existing_table WHERE condition;
Пример
CREATE TABLE books4 AS
SELECT * FROM books WHERE />
Как объяснялось выше, мы видим, что была создана таблица только с одной строкой данных на основе входных данных в предложении WHERE.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Как сделать копию таблицы в 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 , INSERT , UPDATE или DELETE , результаты которой будут скопированы. Заметьте, что запрос должен заключаться в скобки.
Для запросов INSERT , UPDATE и DELETE должно задаваться предложение RETURNING и в целевом отношении не должно быть условного правила, правила ALSO или правила INSTEAD , разворачивающегося в несколько операторов. имя_файла
Путь входного или выходного файла. Путь входного файла может быть абсолютным или относительным, но путь выходного должен быть только абсолютным. Пользователям 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 ACCESS METHOD |