Что такое PostgreSQL?

Бесплатная версия

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

Что дальше?

Что дальше?

После краткого знакомства с PostgreSQL перед читателем открывается несколько путей. Следующий список дает примерное представление о содержимом книги. Этот «путеводитель» поможет вам самостоятельно решить, о чем читать дальше.

• Если вы хотите заняться установкой PostgreSQL 7.1.x, переходите к главе 2.
• Если у вас нет опыта работы с языком SQL и запросами к базам данных, продолжайте чтение с главы 3. Хотя материал главы 3 в основном носит общий характер, в ней также описаны некоторые приемы и концепции, характерные только для PostgreSQL. Обратите на них особое внимание, если хотите досконально разобраться в программе.
• Если вы уже знакомы с языком SQL и структурой команд, вероятно, вас больше заинтересуют главы 4, 5 и 7.
• Если вы владеете практическими навыками работы с PostgreSQL в качестве конечного пользователя и хотите перейти к настройке сервера, определению пользователей и групп, а также заданию параметров аутентификации, переходите к части III. Эта часть призвана помочь системным администраторам в инициализации, настройке и управлении вновь созданными или существующими базами данных PostgreSQL.
• Если вы знакомы с СУБД PostgreSQL и интересуетесь техническими сторонами и методикой программирования, переходите к части IV. В этой части книги описан PL/pgSQL, «родной» процедурный язык PostgreSQL, а также интерфейс JDBC и сервер приложений LXP на базе Web.

Что такое PostgreSQL?

Что такое PostgreSQL?

PostgreSQL - - объектно-реляционная система управления базами данных (ОРСУБД), разработка которой в различных формах ведется с 1977 года. Работа началась с проекта Ingres в Калифорнийском университете (Беркли). Затем проект Ingres был переведен на коммерческую разработку в корпорации Relational Technologies/Ingres.
В 1986 году другая группа, которую возглавлял Майкл-Стоунбрейкер (Michael Stonebraker) из Беркли, продолжила работу над Ingres и создала объектно-реляционную СУБД Postgres. В 1996 году из-за усовершенствования пакета и перехода на распространение с открытыми исходными текстами было принято новое название — PostgreSQL (в течение непродолжительного времени использовалось название Postgres95). В настоящее время над проектом PostgreSQL активно работает группа разработчиков со всего мира.
PostgreSQL считается самой совершенной СУБД, распространяемой на условиях открытых исходных текстов. В PostgreSQL реализованы многие возможности, традиционно встречавшиеся только в масштабных коммерческих продуктах (за дополнительной информацией обращайтесь к разделу «Возможности PostgreSQL»).

Коммерческие продукты семейства PostgreSQL

Коммерческие продукты семейства PostgreSQL

Версия PostgreSQL для операционной системы Red Hat называется «Red Hat Database». Этот продукт относительно недавно появился на рынке баз данных. В основу Red Hat Database заложена версия с открытыми текстами PostgreSQL 7.1. В настоящее время Red Hat Database работает только в системе Linux, желательно в Red Hat Linux 7.1.

Поддержка сообщества PostgreSQL

Поддержка сообщества PostgreSQL

Сообщество PostgreSQL обеспечивает активную поддержку продукта через списки рассылки. Существует несколько списков рассылки, на которые вы можете подписаться, разделенные по темам (например, pfsql-general, pgsql-hackers и т. д.). Списки
рассылки для пользователей SQL содержат достаточно подробную информацию от общих вопросов до разработки программных интерфейсов PostgreSQL. Полный перечень списков рассылки с подробными описаниями приведен на сайте http:/ /www. postg resql.org.

Продукты бесплатные и коммерческие

Продукты бесплатные и коммерческие

Бесплатная версия PostgreSQL состоит только из системы управления базами данных и программных интерфейсов. Версия PostgreSQL для Red Hat также включает графическую программу установки и ограниченную техническую поддержку.
Выбор между коммерческой и бесплатной версиями PostgreSQL определяется прежде всего деловыми соображениями. При наличии компетентного технического персонала вполне подойдет и бесплатная версия, но если вам потребуется поддержка в процессе установки, настройки и администрирования, возможно, лучше приобрести коммерческую версию PostgreSQL.

Возможности PostgreSQL

Возможности PostgreSQL

Как упоминалось выше, PostgreSQL считается самой совершенной СУБД, распространяемой на условиях открытых исходных текстов. В PostgreSQL реализованы многие возможности, обычно присутствующие только в коммерческих СУБД, таких как DB2 и Oracle. Ниже перечислены основные возможности PostgreSQL версии 7.1.x.

• Объектно-реляционная модель. Работа с данными в PostgreSQL основана на объектно-реляционной модели, что позволяет задействовать сложные процедуры и системы правил. Примерами нетривиальных возможностей этой категории являются декларативные запросы SQL, контроль параллельного доступа, поддержка многопользовательского доступа, транзакции, оптимизация запросов, поддержка наследования и массивов.
• Простота расширения. В PostgreSQL поддерживаются пользовательские операторы, функции, методы доступа и типы данных.
• Полноцепная поддержка SQL. PostgreSQL соответствует базовой спецификации SQL99 и поддерживает такие нетривиальные средства, как объединения стандарта SQL92.
• Проверка целостности ссылок. PostgreSQL поддерживает проверку целостности ссылок, обеспечивающую правильность данных в базе.
• Гибкость API. Гибкость API PostgreSQL позволяет легко создавать интерфейсы к РСУБД PostgreSQL. В настоящее время существуют программные интерфейсы для Object Pascal, Python, Perl, PHP, ODBC, Java/JDBC, Ruby, TCL, C/ C+ и Pike.
• Процедурные языки. В PostgreSQL предусмотрена поддержка внутренних процедурных языков, в том числе специализированного языка PL/pgSQL, являющегося аналогом PL/SQL, процедурного языка Oracle. Одним из преимуществ PostgreSQL является возможность использования Perl, Python и TCL в качестве внутренних процедурных языков.
• МУСС. Технология MVCC (Multi-Version Concurrency Control) используется в PostgreSQL для предотвращения лишних блокировок (locking). Каждый, кто хоть раз работал с другими СУБД на базе SQL (например, MySQL или Access), наверняка замечал, что обращение к базе данных для чтения иногда сопровождается задержками, связанными с попытками записи в базу данных. Проще говоря, операции чтения блокируются операциями, производящими обновление записей. Применение технологии MVCC в PostgreSQL полностью решает эту проблему. MVCC лучше низкоуровневой блокировки, поскольку операции чтения никогда не блокируются операциями записи. Вместо этого PostgreSQL отслеживает все транзакции, выполняемые пользователями базы данных, что позволяет работать с записями без ожидания их освобождения.

Клиент-сервер. В PostgreSQL используется архитектура «клиент-сервер» с распределением процессов между пользователями. В целом она напоминает методику работы с процессами в Apache 1.3.x. Главный (master) процесс создает дополнительные подключения для каждого клиента, пытающегося установить соединение с PostgreSQL.
Опережающая регистрация изменений. Опережающая регистрация изменений (Write Ahead Logging, WAL) повышает надежность данных. Все изменения данных протоколируются до их непосредственной актуализации в базе. Наличие протокола изменений гарантирует, что в случае сбоя базы данных (что весьма маловероятно) данные можно будет восстановить по запротоколированным транзакциям. После восстановления системы пользователь продолжает работу с состояния, непосредственно предшествовавшего сбою.

Из сказанного может возникнуть впечатление,

Выводы

Из сказанного может возникнуть впечатление, что коммерческая версия PostgreSQL в чем-то объективно лучше бесплатной версии. В действительности это не так. При выборе версии необходимо руководствоваться только требованиями конкретной ситуации. Потребуется ли техническая поддержка на стадии установки? Так ли необходима графическая оболочка в программе установки? Если на оба вопроса вы ответили положительно, подумайте о приобретении коммерческой версии PostgreSQL. Но если вы или технические специалисты вашей организации справятся с компиляцией исходных текстов и изменением конфигурации, возможно, бесплатная версия окажется более подходящей.
Также следует ответить на два дополнительных вопроса. Нужна ли вам печатная документация? Готовы ли вы заплатить за PostgreSQL? Печатная документация входит в комплект поставки коммерческой версии, но авторы надеются, что после чтения этой книги дополнительная документация вам уже не понадобится. Также приходится учитывать и цену коммерческой версии: Red Hat Database стоит 2295 долларов.
Главное, о чем следует помнить при выборе версии PostgreSQL (с позиций общей полезности и широты возможностей), — между бесплатной и коммерческой версиями не существует функциональных различий.

Краткий курс SQL

Анатомия команд SQL

Анатомия команд SQL

Команды SQL всегда начинаются с действия (verb) — слова или группы слов, описывающих выполняемую операцию. Кроме того, команда SQL обычно содержит одну или несколько секций, уточняющих ее смысл. В табл. 3.2 перечислены основные действия SQL.

Базы данных

Базы данных

Хотя РСУБД PostgreSQL часто называют просто «базой данных», этот термин часто приводит к недоразумениям. База данных PostgreSQL представляет собой объектно-реляционную реализацию того, что в стандарте SQL99 формально называется схемой (schema).
Проще говоря, базой данных называется совокупность логически связанных данных, хранящихся вместе. Обычно СУБД обеспечивает доступ к этим данным в многопользовательской среде. В PostgreSQL дело обстоит именно так, хотя доступ ограничивается системой четко определенных прав и ограничений.
PostgreSQL может одновременно предоставлять доступ к нескольким базам данных, у каждой из которых имеется свой владелец, свои таблицы, представления, индексы и функции.
Чтобы создать таблицу, функцию или любой другой объект базы данных, вы подключаетесь к конкретной базе данных при помощи клиента PostgreSQL. После подключения создается объект, принадлежащий этой базе данных и недоступный для всех остальных баз данных (хотя клиент может одновременно открыть несколько подключений к разным базам).
Жесткая изоляция основных объектов данных в базах данных снижает опасность возникновения конфликтов имен при выборе имени, ранее зарезервированного для другой цели (например, если два пользователя захотят создать таблицу с именем products для двух разных целей). Это связано с тем, что ни одна база данных не располагает информацией о компонентах других баз и не пытается устанавливать с ними какие-либо логические связи. Более того, это правило распространяется и на объекты данных объектно-реляционных баз, поэтому созданные пользователем функции и языковые определения недоступны для других пользователей, подключающихся к другим базам данных через PostgreSQL.
По умолчанию PostgreSQL создает только одну рабочую базу данных с именем template 1. Любая база данных, созданная после template 1, фактически является ее клоном и наследует от прототипа все характеристики и объекты, включая структуру таблиц, функции, языки и т. д. Для новых пользователей PostgreSQL нередко создается стандартная база данных с именем, соответствующим имени пользователя PostgreSQL, поскольку если при подключении пользователя имя базы данных не указано, PostgreSQL по умолчанию в качестве имени базы данных подставляет имя пользователя.

Битовые последовательности

Битовые последовательности

Битовые последовательности предназначены для представления двоичных величин в виде произвольной последовательности нулей и единиц. Как и строковые константы, битовые последовательности заключаются в апострофы, но начинаются с обязательного префикса В (в верхнем или нижнем регистре). По наличию этого символа PostgreSQL определяет, что строка является битовой последовательностью, а не обычной символьной строкой.
В соответствии с синтаксисом PostgreSQL открывающий апостроф должен следовать сразу же после префикса В, а битовая последовательность не может содержать других символов, кроме 0 и 1. Внутренние пробелы в битовых последовательностях запрещены, но допускается их разбиение по аналогии с обычными строковыми константами (см. выше пункт «Строковые константы).
Битовые строки обычно требуются только при работе с двоичными функциями и таблицами, содержащими двоичные данные. В листинге 3.6 продемонстрирована вставка битовой последовательности в таблицу. Сначала двоичное представление байта сохраняется в таблице my_bytes, а затем результат операции проверяется простым запросом.

Целочисленные константы

Целочисленные константы

Целочисленные константы используются гораздо чаще, чем битовые последовательности. В PostgreSQL целочисленной константой считается любая лексема, состоящая из цифр (без десятичной точки) и не заключенная в апострофы. Интервал допустимых значений целочисленных констант зависит в основном от контекста, но в PostgreSQL по умолчанию целочисленный тип представляется четырьмя байтами и принимает значения из интервала от -2 147 483 648 до 2 147 483 647.
Целочисленные константы часто встречаются в математических операциях, а также в командах SQL, содержащих ссылки на поля целочисленного типа. В листинге 3.7 целочисленные константы используются для обновления кода автора командой UPDATE.
Вспомните таблицу authors, которая устанавливает соответствие между числовым кодом автора и двумя строками, представляющими его имя и фамилию. Допустим, из каких-то административных соображений было решено, что все коды авторов, меньшие 100, нужно увеличить до 100 и выше.
Внесение исправлений начинается с поиска записей, у которых поле id удовлетворяет данному условию. Для этого в команду SELECT включается секция WHERE с целочисленной константой, используемой при проверке условия «меньше».

Числовые типы

Числовые типы

Числовые типы PostgreSQL используются для представления целых и вещественных значений. С общей точки зрения к числовым типам PostgreSQL относятся следующие типы:

• 2-, 4- и 8-байтовые целые числа;
• 4- и 8-байтовые вещественные числа;
• дробные числа с фиксированной точностью.

Помимо перечисленных в PostgreSQL существуют некоторые специальные типы, также относимые к категории числовых, в том числе устаревший тип money и специальная конструкция serial (табл. 3.13).

Дата и время

Дата и время

Следующая категория типов является удобным средством для хранения даты и времени в универсальной структуре SQL. При этом программист избавляется от хлопот, связанных с форматом хранения (как, например, при хранении этих данных в символьных строках). В PostgreSQL все вычисления с датой и временем производятся но юлианскому календарю. Продолжительность года считается равной 365,24 дня, что обеспечивает правильное представление любой даты с 4713 года до нашей эры и далеко в будущее.
В PostgreSQL поддерживаются все типы даты и времени, определенные в стандарте SQL92 (табл. 3.14), а также некоторые вспомогательные типы PostgreSQL, помогающие решить проблемы с представлением часовых поясов в SQL92.

Формат представления даты

Формат представления даты

В PostgreSQL предусмотрено несколько стандартных форматов даты, в том числе формат ISO-8601, традиционный формат SQL, исходный формат PostgreSQL и многие другие. Некоторые форматы даты указаны в табл. 3.15.
Перечисленные форматы относятся к типам данных date и timestamp.

Форматирование команд SQL

Форматирование команд SQL

Как упоминалось в предыдущем подразделе, команды SQL состоят из последовательности элементов — лексем. Менее очевиден тот факт, что лексемы могут находиться в одной или нескольких строках, поскольку модуль лексического разбора PostgreSQL игнорирует лишние пропуски (в том числе разрывы строк).
В листинге 3.1 приведена команда SQL, которая в первом случае задана в одной строке, а во втором повторяется с разбивкой на несколько строк. Оба варианта команды SELECT выводят все содержимое таблицы my_list.

Форматы представления времени

Форматы представления времени

Время, как и дата, может задаваться разными способами. В табл. 3.20 перечислены самые распространенные форматы значений типов time и time with time zone.

Геометрические типы

Геометрические типы

Геометрические типы PostgreSQL предназначены для представления объектов на плоскости. Они не относятся к стандартным типам данных SQL и потому в книге подробно не рассматриваются. В табл. 3.24 приведена краткая сводка поддерживаемых геометрических типов.

Идентификаторы объектов

Идентификаторы объектов

Как было сказано в подразделе «Таблицы» раздела «Знакомство с реляционными базами данных», база данных содержит таблицы, а каждая таблица содержит хотя бы одно именованное поле. Таблица может содержать записи данных, но их наличие не является обязательным. Каждое поле записи, хранящейся в таблице, содержит некоторые данные или NULL.
Один из вопросов, которые приходится решать при операциях с базами данных, — как различить две записи с одинаковыми значениями полей? Для этого в PostgreSQL предусмотрены идентификаторы объектов (object identifiers, OID), никальные в пределах таблицы. Иначе говоря, таблица никогда не содержит записи одинаковыми идентификаторами OID. Таким образом, даже если содержимое пользовательских полей двух записей полностью совпадает, на программном уровне записи можно различить по значению OID. Пример приведен в листинге 3.31.

Интервальный тип

Интервальный тип

В стандарте SQL92 определен тип данных interval, представляющий фиксированный промежуток времени. Сам по себе тип interval представляет лишь количественную величину, не связанную с определенным начальным или конечным моментом. Интервалы часто используются в сочетании с типами даты и времени для вычисления новой величины посредством сложения или вычитания. Кроме
того, они могут пригодиться для быстрого вычисления точного промежутка между двумя датами или моментами времени, для чего одна величина вычитается из другой.
В PostgreSQL поддерживаются два варианта синтаксиса определения интервалов:
длина единице [ago]
длина! единица [. длина2 единица2 ... ]
[ago]
Здесь:

• длина — продолжительность интервала, заданная в виде целого или вещественного (для микросекунд) числа. Интерпретация числа определяется следующим параметром.
• единица — единица, в которой измеряется заданный интервал. Разрешены следующие ключевые слова: second, minute, hour, day, week, month,year, decade, century, millennium. Также допускаются сокращения (любой длины при условии однозначной интерпретации) и формы множественного числа.
• ago — необязательное ключевое слово ago указывает, что описываемый период времени предшествует некоторому моменту, а не следует после него. Ключевое слово ago можно рассматривать как аналог знака минус (-) в типах даты и времени.

В листинге 3.27 приведены примеры объединения типов date и interval. Как видно из листинга, вычитание инвертированного интервала (с ключевым словом ago) эквивалентно прибавлению обычного интервала по аналогии с вычитанием отрицательных чисел.

Язык SQL и его предшественники

Язык SQL и его предшественники

Язык SQL в значительной степени основан на реляционной алгебре и кортежно-реляционной модели. Реляционная алгебра, представленная Э. Ф. Коддомв 1972 году, определяет базовые принципы обработки синтаксиса SQL; это процедурный способ построения запросов, управляемых данными, который определяет способ достижения поставленной цели. С другой стороны, кортежно-реляционная модель использует декларативные выражения и определяет саму цель при обработке структурированных запросов.
Некоторые перечисленные ниже дополнительные возможности отделяют язык SQL от его предшественников, ограничивающихся простой реализацией отдельных аспектов реляционной алгебры или модели.

• Поддержка вставки, модификации и удаления данных. Пользователям разрешается вставлять, удалять и обновлять записи, хранящиеся в базе данных.
• Математические операторы. В SQL используются математические операторы сложения, вычитания, умножения, деления, а также выражения вида (valuel * 5) + valueZ. Также поддерживаются операторы сравнения (например, values >= value4).
• Отображение данных. Пользователь может вывести связи, сгенерированные при обработке запроса.
• Присваивание. Пользователь может переименовать связь, сгенерированную при обработке запроса. Он не ограничен стандартным именем связи, производным от имени поля или функции (в зависимости от запроса).
• Агрегатные функции. Пользователь может группировать логически связанные записи и вычислять сводные показатели (среднее арифметическое, сумму, количество, максимальное или минимальное значение).

Ключевые слова и идентификаторы

Ключевые слова и идентификаторы

Ключевыми словами называются зарезервированные термины SQL, имеющие особый синтаксический смысл для сервера (INSERT, UPDATE, SELECT, DELETE и т. д.).
Каждая команда SQL начинается с ключевого слова, хотя многие ключевые слова сами по себе не являются законченными командами. Например, конструкция INSERT INTO является действительной командой SQL, а слово INTO является зарезервированным ключевым словом. Но как нетрудно предположить, слово INTO не имеет смысла вне контекста.
Идентификаторы, как упоминалось выше, представляют собой имена переменных для ссылки на объекты базы данных. Имена идентификаторов произвольно назначаются при создании базы данных. В PostgreSQL идентификаторы могут связываться с базами данных, таблицами, полями, индексами, представлениями, последовательностями, правилами, триггерами и функциями.
В листинге 3.2 в простую таблицу states включается запись, состоящая из трех полей.

Команды SQL

Команды SQL

Любая концептуальная информация о реляционных базах данных и таблицах приносит пользу лишь в том случае, если вы знаете, как организовать взаимодействие с данными. Язык SQL состоит из структурированных команд, предназначенных для добавления, модификации и удаления данных из базы. Эти команды образуют основу для взаимодействия с сервером PostgreSQL
Этот раздел посвящен «анатомии» команд SQL. В нем рассматриваются структурные компоненты команд, описываются функции каждого компонента и их взаимные связи. Стандартный клиент командной строки PostgreSQL, psql, выводит результаты выполнения команд в приведенных примерах.
Большинство примеров команд SQL относится к базе данных booktown. Весь вывод psql снабжается префиксом вида booktown=#
В некоторых примерах используется тестовая база данных testdb. По умолчанию в приглашении psql выводится только имя базы данных и символы =#, означающие, что система готова к вводу новой команды (хотя вы увидите, что символ = динамически изменяется по мере отслеживания состояния входных данных SQL). В книге это приглашение приводится вместе с входными и выходными данными SQL, чтобы вам было проще освоиться с выводом клиента psql.
Клиент psql подробно описан в главе 4. Здесь он упоминается лишь для пояснения стиля примеров команд SQL.
Примечание 1
Примечание 1


Схема базы данных booktown (вместе с примерами записей) находится в файле booktown.sql на компакт-диске. Чтобы установить эту базу данных, введите в приглашении командной строки команду psql -U postgres template! -f /mnt/cdrom/booktown.sql, где /mnt/cdrom — путь к смонтированному компакт-диску, a postgres — имя суперпользователя PostgreSQL.

Комментарием называется

Комментарии

Комментарием называется фрагмент обычного текста, оформленный специальным образом и внедренный в код SQL. Комментарии не влияют на выполнение программы, поскольку PostgreSQL удаляет их из входного потока и интерпретирует как обычные пропуски. Существует две разновидности комментариев: однострочные и многострочные.
Однострочные комментарии начинаются с двух дефисов (- -) и либо находятся в отдельной строке, либо следуют за лексемами SQL (модуль лексического разбора PostgreSQL не считает комментарии лексемами, а все символы, следующие за последовательностью --, интерпретирует как пропуски). Пример однострочного комментария приведен в листинге 3.11.

Константы

Константы

При работе с базами данных многие объекты хранятся на диске, а для обращения к ним используются идентификаторы (имена таблиц, полей и функций). Однако неизбежно настанет момент, когда в систему потребуется передать новые данные — например, при вставке новых записей, при формировании секций с критериями удаления или модификации или при вычислениях на базе существующих записей. Такие данные передаются в виде констант, также иногда называемых «литералами». Константы предназначены для «буквального» представления данных в командах SQL (вместо ссылки на них по идентификатору).
Константы с косвенной типизацией автоматически распознаются модулем лексического разбора PostgreSQL по их синтаксису. В PostgreSQL поддерживаются пять вариатнов констант с косвенной типизацией:

• строковые константы;
• битовые последовательности;
• целочисленные константы;
• вещественные константы;
• логические константы.

Краткая история SQL

Краткая история SQL

В 1970 году доктор Э. Ф. Кодд (Е. F. Codd) из компании IBM в статье «A Relational Model of Data for Large Shared Data Banks» дал формальное определение реляционной модели, образующей концептуальную основу SQL. Данная статья вызвала большой интерес как в плане общей приемлемости, так и в плане практических коммерческих приложений подобных систем.
В 1974 году компания IBM начала работу над проектом System/R. Группа под руководством Дональда Чемберлина (Donald Chamberlin) разработала язык SEQUEL (Structured English Query Language). В 1974-75 годах проект System/R был реализован в прототипе SEQUEL-XRM. Затем в 1976-77 годах проект был полностью переделан, в него была включена поддержка многотабличных и многопользовательских средств. Переработанная система сначала называлась «SEQUEL 2», а затем по соображениям авторских прав была переименована в «SQL».
В 1978 году началось доскональное тестирование системы. Оно наглядно продемонстрировало ее удобство и практическую полезность, в результате чего компания IBM начала разработку коммерческих SQL-продуктов, основанных на прототипе System R, включая SQL/DS (1981 год) и DB2 (1983 год).
Другие разработчики программного обеспечения также обратили внимание на растущую популярность реляционной модели и анонсировали свои продукты на базе SQL. К их числу относились СУБД Oracle, Sybase и Ingres (на базе проекта Ingres Калифорнийского университета в Беркли).
Примечание 1
Примечание 1


Как нетрудно догадаться по названию, СУБД PostgreSQL связана с Ingres. Обе СУБД происходят от проекта Ingres Калифорнийского университета.

Краткий курс SQL

Краткий курс SQL

В этой главе описывается история SQL и основные концепции языка. На базе материала строится следующая глава, посвященная использованию SQL в Post-greSQL. В частности, в ней изложены основные принципы работы реляционных баз данных, описаны объектно-реляционные расширения и структура команд SQL, а также приводится краткий обзор поддерживаемых в PostgreSQL типов данных, операторов и функций.

Пробелы и переводы строк

Листинг 3.1. Пробелы и переводы строк

testdb-* SELECT * FROM mylist;
todos
---------------------------------------
Pick up laundry. Send out bills.
Wrap up Grand Unifying Theory for publication.
(3 rows)
testdb-# SELECT * testdb-* FROM
testdb-# mylist;
todos
--------------------------------------
Pick up laundry. Send out bills.
Wrap up Grand Unifying Theory for publication.
(3 rows)
В листинге 3.1 лексемы второй команды разделены дополнительными пробелами и символами новой строки. Как видно из полученных результатов, PostgreSQL игнорирует лишние пробелы и разрывы строк, вследствие чего команды являются синтаксически эквивалентными. Вы можете воспользоваться этим фактом и разбить длинную команду SQL на несколько строк, чтобы упростить ее чтение. Для таких простых команд, как в листинге 3.1, это не нужно, но разбиение пригодится при работе со сложными командами SQL с многочисленными секциями, выражениями и условиями. В книге периодически встречаются примеры, в которых команда разбивается на несколько строк с комментариями к каждой части.

Операторы в командах SQL

Листинг 3.10. Операторы в командах SQL

booktown=# SELECT * FROM books;
id | title author_id | subject_id
7808 | The Shining |4156 | 9
156 | The Tell-Tale Heart| 15| 9
4513 | Dune |1866 |15
4267 | 2001: A Space Odyssey| 2001| 15
1608 | The Cat in the Hat |1809| 2
1590 | Bartholomew and the Oobleck |1809| 2
(6 rows)
booktown=# UPDATE books SET author_id = author _id + 1500;
UPDATE 6
booktown=# SELECT * FROM books;
id title | author_id | subject_id
7808 | The Shining | 5656 | 9
156 | The Tell-Tale Heart 1515| 9
4513| Dune 3366 | 15
4267 | 2001; A Space Odyssey 3501 | 15
1608 | The Cat in the Hat | 3309 |2
1590 | Bartholomew and the Oobleck | 3309 | 2
Как видно из листинга 3.10, выполнение операции сложения с предыдущим значением поля author_id приводит к модификации этого поля во всех записях таблицы.
Вероятно, вы уже знакомы с основными математическими операторами: + (суммирование двух числовых величин), - (вычитание) и т. д. Существуют и другие, более экзотические операторы — например, поразрядные операторы & и |, которые модифицируют значения на уровне двоичного представления.
Некоторые ключевые слова SQL также часто относятся к категории операторов. В первую очередь это логические операторы AND, OR и NOT. Формально являясь ключевыми словами, они причисляются к операторам, поскольку предназначаются для выполнения операций с константами и идентификаторами.
Основные операторы PostgreSQL перечислены в табл. 3.6.

Однострочные комментарии

Листинг 3.11. Однострочные комментарии

testdb=# SELECT 'Test1 -- This can follow valid SQL tokens.
testdb-# -- or be on a line of its own. testdb-#
AS example; example
Test
(1 row)
Многострочные комментарии начинаются с последовательности /* и завершаются последовательностью */. Такой способ оформления комментариев хорошо знаком программистам С, но между интерпретатором PostgreSQL и компилятором С существует одно принципиальное отличие: комментарии PostgreSQL могут быть вложенными. Иначе говоря, если внутри многострочного комментария имеется другой многострочный комментарий, то закрывающая последовательность */ внутреннего комментария не закрывает внешний комментарий. Пример многострочного комментария приведен в листинге 3.12.

Многострочные комментарии

Листинг 3.12. Многострочные комментарии

testdb=# SELECT 'Multi /* This comment extends across
testdb-# * numerous lines, and can be
testdb-# * /* nested safely */ */
testdb-# | '-test' AS example; example
Multi-test (1 row)
Допустим, имеется файл с кодом SQL, в котором необходимо закомментировать большой фрагмент и передать остаток PostgreSQL для интерпретации и выполнения. Если в этом фрагменте встречаются многострочные комментарии, PostgreSQL правильно заключает, что закрывающая последовательность */ относится к последнему открытому комментарию, а не ко всему закомментированному блоку.
Примечание 1
Примечание 1


Звездочка (без смежного символа косой черты) не имеет особой интерпретации в комментариях. Дополнительные звездочки были включены в листинг 3.12 исключительно по эстетическим сообщениям.

Пример запроса SQL

Листинг 3.13. Пример запроса SQL

booktown=# SELECT Id, name FROM states;
id | name
42 | Washington 51 I Oregon
(2 rows)
booktown=#
В табл. 3.8 и 3.9 анализируется другая, более сложная команда SQL. В ней используется действие UPDATE с секциями SET и WHERE, которые определяют соответственно правило изменения записей и критерий их отбора.

Обновление таблицы командой UPDATE

Листинг 3.14. Обновление таблицы командой UPDATE

booktown=# UPDATE states
booktown-# SET id = 51
booktown-# WHERE
name = 'Oregon';
UPDATE 1
booktown=# SELECT * FROM states
booktown-$ WHERE
name='Oregon':
Id | name | abbreviation
51 | Oregon | OR
(1 row)
booktown=#

Пустые строки и NULL

Листинг 3.15. Пустые строки и NULL

booktown=# SELECT id. title FROM books:
id [ title
7808 | The Shining 156 | The Tell-Tale
Heart 4513 | Dune
100 |
101 | (5 rows)
booktown=# SELECT id. title FROM
books WHERE title = ": id | title
100 (1 row)
Dooktown=# SELECT id, title FROM
books WHERE title IS NULL;
id title
101 |
(1 row)
В листинге 3.16 продемонстрировано более практичное (и реальное) применение ключевого слова NULL в таблице editions, связывающей код ISBN с датой публикации книги.

Пример использования NULL

Листинг 3.16. Пример использования NULL

booktown=# SELECT isbn, publication FROM editions:
isbn | publication
039480001X | 1957-03-01
0394800753 | 1949-03-01
0385121679 | (3 rows)
booktown=# SELECT isbn. publication FROM
editions WHERE publication IS NULL:
isbn | publication
0385121679 |
(1 row)
В этом примере NULL может использоваться для представления изданий, которые еще не были опубликованы или дата публикации которых на момент занесения данных в базу неизвестна. Было бы неправильно подбирать для таких книг какую-нибудь бессмысленную дату, и в обоих случаях NULL выглядит вполне оправданно.

Простая таблица с логическими значениями

Листинг 3.17. Простая таблица с логическими значениями

booktown=# CREATE TABLE daily_inventory
(isbn text. in_stock boolean);

CREATE
booktown=# INSERT INTO
dailyjnventory VALUES ('0385121679', true);

INSERT 3390926 1
booktown=# INSERT INTO dailyjnventory
VALUES ('039480001X'. 't');

INSERT 3390927 1
booktown=# INSERT INTO dailyjnventory
VALUES ('044100590X'. 'true');

INSERT 3390928 1
booktown=# INSERT INTO dailyjnventory
VALUES С0451198492', false);

INSERT 3390929 1
booktown=# INSERT INTO dailyjnventory
VALUES С0394900014', '0');

INSERT 3390930 1
booktown=# INSERT INTO dailyjnventory
VALUES ('0441172717'. '!');

INSERT 3390931 1
booktown=# INSERT INTO dailyjnventory
VALUES ('0451160916');

INSERT 3390932 1
После того как таблица заполнена, можно легко получить информацию об имеющихся в наличии книгах. Для этого используется запрос SELECT, приведенный в листинге 3.18.

Выборка данных по логическому признаку

Листинг 3.18. Выборка данных по логическому признаку

booktown=# SELECT * FROM dailyjnventory WHERE in_stock='yes':
isbn in_stock
0385121679 | t
039480001X | t
044100590X t
0441172717 I t
(4 rows)
В PostgreSQL предусмотрена возможность неявной выборки по значению true в логическом поле; для этого имя поля указывается без операторов или ключевых слов-модификаторов. В хорошо спроектированных таблицах такой подход помогает создавать более наглядные запросы. Пример приведен в листинге 3.19.

Неявная выборка по логическому значению

Листинг 3.19. Неявная выборка по логическому значению

true booktown=# SELECT * FROM dailyjnventory WHERE in_stock;
isbn | in_stock
0385121679 | t
039480001X | t
044100590X | t
0441172717 | t
(4 rows)
Хотя в этом запросе не указана конкретная логическая величина, при отсутствии оператора сравнения подразумевается значение true.
Для выборки по значению false можно либо сравнить значение поля с любой из логических констант, перечисленных в табл. 3.11, либо поставить перед именем поля ключевое слово SQL NOT. Оба способа продемонстрированы в листинге 3.20.

Ключевые слова и команды

Листинг 3.2. Ключевые слова и команды

booktown=# INSERT INTO states VALUES (33, 'Oregon', 'OR');

INSERT 3389701 1
В листинге 3.2 команда SQL INSERT INTO содержит ключевые слова SQL INSERT, INTO и VALUES.
Команда INSERT INTO модифицирует таблицу, заданную идентификатором states. В данном случае модификация сводится к вставке новой записи.

Выборка по логическому значению false

Листинг 3.20. Выборка по логическому значению false

booktown=# SELECT * FROM dailyjnventory WHERE in_stock = 'no';
isbn | in_stock
0451198492 f 0394900014 f (2 rows)
booktown=# SELECT * FROM dailyjnventory
WHERE NOT in_stock:
isbn | in_stock
0451198492 | f
0394900014 | f
(2 rows)
Этот пример убедительно доказывает, что при проектировании языка SQL учитывался фактор наглядности и удобочитаемости программ. При грамотном выборе имен таблиц и полей запрос почти не отличается от обычной фразы на английском языке.
Сравнение значений логических полей с константами из табл. 3.11 может осуществляться при помощи оператора неравенства != (например, WHERE in_stock != 't'). Таким образом, следующие три синтаксические формы эквивалентны:
SELECT * FROM daily_iinventory
WHERE NOT injtock: SELECT * FROM
dailyjnventory WHERE in_stock = 'no1:
SELECT * FROM dailyjnventory WHERE in_stock != 't':
Возможно, вы обратили внимание на то, что в листинге 3.17 в таблицу вставляется семь записей, а суммарное количество записей при двух выборках (для поля i n_stock, равного true и fal se) только шесть. Дело в том, что в последней операции вставки в листинге 3.17 значение поля i n_stock не указано, поэтому в записи книги с кодом ISBN равным 0451160916 поле in_stock равно NULL.
Как упоминалось выше, величина NULL не интерпретируется как true или fal se, поэтому для выборки по значению NULL необходимо использовать условие IS NULL. Также можно воспользоваться оператором ! =, но тогда возникают проблемы с адаптацией программы для других СУБД. Пример запроса SQL с условием IS NULL:
booktown=# SELECT * FROM
dailyjnventory WHERE in_stock IS NULL:
isbn | 1n_stock
0451160916 |
(1 row)
Поскольку IS NULL является обычным условием SQL, для обновления всех случайных значений NULL в поле in_stock можно воспользоваться командой UPDATE, приведенной в листинге 3.21.

Исправление случайных значений NULL

Листинг 3.21. Исправление случайных значений NULL

booktown=# UPDATE dailyjnventory
SET in_stock = Т WHERE in_stock IS NULL;
UPDATE 1

Использование типа numeric вместо money

Листинг 3.23. Использование типа numeric вместо money

booktown=# CREATE TABLE money_example (money_cash money.
booktown(# numeric_cash numeric(10,2)):
CREATE
booktown=# INSERT INTO money_example VALUES C$12.241, 12.24);

INSERT 3391095 1
booktown=# SELECT * FROM money_example;
money_cash | numeric_cash
$12.24 | 12.24 (1 row)
booktown=# SELECT money_cash,
booktown-# '$' | ltrim(to_char(numenc_cash. '9999.99'))
booktown-# AS numeric_cashif1ed
booktown-# FROM money_example;
money_cash | numeric_cashified
$12.24 j 12.24
(1 row)

Использование типа serial

Листинг 3.24. Использование типа serial

booktown=# CREATE TABLE autojdentified (id_serial);

NOTICE: CREATE TABLE will create implicit sequence '
auto Jdentif led Jd_seq'
for SERIAL column 'auto_identified.id'
NOTICE: CREATE TABLE/UNIQUE will create
Implicit index 'auto_identified_1d_key' for table
'autojdentified'
CREATE

Решение задачи «вручную»

Листинг 3.25. Решение задачи «вручную»

booktown=# CREATE SEQUENCE autojdentified id_seq;
CREATE
booktown=# CREATE TABLE autojdentified
booktown-# (id integer UNIQUE DEFAULT
nextval('autojdentifiedjd_seq'));

NOTICE: CREATE TABLE/UNIQUE will create implicit index
'autojdentifiedjdjcey' for table
'autojdentified'
CREATE
ВНИМАНИЕ
После удаления таблицы последовательность, созданная для типов senal, автоматически не удаляется. При удалении таблиц, содержащих поля типа serial, эти последовательности должны удаляться отдельно.

Выбор формата даты

Листинг 3.26. Выбор формата даты

booktown=# SET DATESTYLE TO ISO,US;
SET VARIABLE
booktown=# SHOW DATESTYLE;
NOTICE: DateStyle is ISO with
US (NonEuropean) conventions
SHOW VARIABLE
booktown=f SET DATESTYLE TO
NONEUROPEAN. GERMAN;
SET VARIABLE
booktown=# SHOW DATESTYLE;
NOTICE: DateStyle is German
with European conventions
SHOW VARIABLE
Если дополнительный формат не указан, по умолчанию выбирается подходящее значение (например, для регионального формата German выбирается значение European).
Хотя переменная DATESTYLE является удобным средством настройки формата даты, необходимо помнить, что она относится к категории переменных времени исполнения, а это означает, что настройка действует лишь на протяжении текущего сеанса. Существует два способа присвоить переменной DATESTYLE значение по умолчанию, чтобы его не приходилось указывать заново при каждом сеансе.

• Изменение переменной среды PGDATESTYLE на сервере, на котором работает процесс postmaster. Например, при использовании командного интерпретатора bash можно добавить строку PGDATESTYLE="SQL US" в файл .bash_profile пользователя postgres. При запуске процесса postmaster пользователем postgres переменная PGDATESTYLE будет автоматически применяться ко всем операциям форматирования даты и времени, выполняемым PostgreSQL.
• Изменение переменной среды PGDATESTYLE, используемой клиентским приложением (при условии, что оно было написано с применением библиотеки libpq) в начале сеанса. Этот вариант выбирается в том случае, если формат вывода должен настраиваться клиентом, а не сервером. Например, присваивая значение переменной PGDATESTYLE командой export в приглашении bash перед запуском клиента psql, вы задаете формат, который будет использоваться в работе psql.

Операции с интервалами

Листинг 3.27. Операции с интервалами booktown=# SELECT date('1980-06-25');


date
1980-06-25
(1 row)
booktown=# SELECT interval С 21 years 8 days');

interval
21 years 8 days (1 row)
booktown=# SELECT date('1980-06-25') + interval
('21 years 8 days') booktown-# AS spanned_date:
spanned_date
2001-07-03 00:00:00-07
(1 row)
booktown=# SELECT date ('1980-06-25') -
interval ('21 years 8 days ago1)
booktown-# AS twice_inverted_interval_date;
twice_ nverted_interval_date
2001-07-03 00:00:00-07
(1 row)

Использование констант current и now

Листинг 3.28. Использование констант current и now

booktown=# CREATE TABLE tasklog
booktown=# (taskname char(15),
booktown=# timebegun timestamp,
booktown=# timeflnished timestamp);

CREATE
booktown=# INSERT INTO tasklog VALUES
booktown=# ('delivery', 'now', 'current'):
INSERT 169936 1
booktown=# INSERT INTO tasklog
VALUES booktown=# ('remodeling', 'now', 'current');

INSERT 169937 1
booktown=# SELECT taskname, tlmefnished - timebegun
booktown-# AS timespent FROM tasklog;
taskname | timespent
delivery | 00:15-.32
remodeling [ 00:04:42
(2 rows)
Итак, константа now обычно используется при сохранении в таблице фиксированного момента времени, который не изменяется при последующих ссылках. Как видно из листинга 3.29, плохое понимание различий между константами now и current приводит к потенциальным ошибкам программирования SQL. В листинге приведены две команды INSERT; в первой команде используется now, а во второй — current. Сравнение результатов показывает, что в первой строке при каждом запросе время обновляется, а во второй строке оно всегда остается одинаковым.

Сравнение констант now и current

Листинг 3.29. Сравнение констант now и current

booktown=# INSERT INTO shipments (customer_id. isbn, ship_date)
Ccustomer_id, isbn, ship_date)
booktown-# VALUES (1. '039480001X', 'current');

INSERT 3391221 1
booktown=# INSERT INTO shipments (customer_id. isbn, ship_date)
booktown-# VALUES (2. '0394800753'. 'now');

INSERT 3391222 1
booktown=# SELECT isbn. ship_date FROM shipments: isbn | ship_date
039480001X [ current 0394800753 | 2001-08-10 18:17:49-07
(2 rows)
booktown=# SELECT isbn.
booktown-# to_char(ship_date. 'YYYY-MM-DD HH24:MI:SS')
booktown-# AS value
booktown-# FROM shipments; isbn value
039480001X | 2001-08-10 18:21:22 0394800753 | 2001-08-10 18:17:49
(2 rows)
booktown=# SELECT isbn.
booktown-# to_char(ship_date. 'YYYY-MM-DD HH24:MI:SS')
booktown-# AS value
booktown-# FROM shipments;
isbn | value
039480001X | 2001-08-10 18:22:35 0394800753 | 2001-08-10 18:17:49
(2 rows)

Преодоление ограничений

Листинг 3.3. Преодоление ограничений

booktown=# CREATE TABLE lst_bent_rule (rule_name text);

ERROR: parser: parse error at or near "1"
booktown=# CREATE TABLE "lst_bent_rule" (rule_name text);

CREATE
Более того, имена таблиц могут содержать некоторые символы, которые обычно считаются недопустимыми (например, пробелы или амперсанды, хотя присутствие кавычек, разумеется, запрещено). Хотя стандарт ANSI/ISO SQL не позволяет создавать идентификаторы с именами, совпадающими с ключевыми словами SQL, PostgreSQL (как и ряд других реализаций SQL) достаточно либерально относится к этому ограничению.— такие имена допустимы, но они должны заключаться в кавычки.
Защита идентификаторов при помощи кавычек выручает во многих нестандартных ситуациях, но если вы хотите, чтобы ваши команды SQL были стандартными и хорошо адаптировались для других платформ, старайтесь по возможности придерживаться стандартов ANSI/ISO.

Использование функции преобразования типа

Листинг 3.30. Использование функции преобразования типа

booktown=# SELECT text(1000)
booktown-# AS explicitjtext;
explicit_text

Идентификация записей по OID

Листинг 3.31. Идентификация записей по OID

3Stdb=# SELECT * FROM my_list;
todos
----------------------------------
Correct redundancies In my_list.
Correct redundancies in my_list.
(1 rows)
testdb=# SELECT *. old FROM my_list:
todos | old
----------------------------------------
Correct redundancies in my list. | 3391263
Correct redundancies In my list. | 3391264
( 2 rows)
testdb=# DELETE FROM my_list
testdb-# WHERE old = 3391264;
DELETE 1
testdb=# SELECT *.oid FROM my_list;
todos old
----------------------------------------------
Correct redundancies in my list. | 3391263
(1 row)

Использование строковых констант

Листинг 3.4. Использование строковых констант

booktown=# UPDATE authors
booktown-# SET firstjiame = 'Louisa May'
booktown-l WHERE firstjiame = 'Luoisa May'
UPDATE 1
booktown-# SELECT * FROM authors;
id | lastjiame | firstjiame
1809 | Geisel | Theodor Seuss
1111 | Denham | Ariel 15990 | Bourgeois | Paulette
25041 | Bianco j Margery Williams
115 I Poe I Edgar Allen
16 j Alcott I Louisa May
(6 rows)
Команда UPDATE в листинге 3.4 использует строковые константы Louisa May и Luoisa May в сочетании с ключевыми словами SET и WHERE. Как видно из результатов запроса, команда обновляет содержимое таблицы, заданной идентификатором authors, и исправляет опечатку.
Тот факт, что строковые константы заключаются между апострофами, порождает очевидную семантическую проблему: если в самой последовательности символов встречается апостроф, граница строковой константы будет определена неверно. Чтобы экранировать апостроф в строке (то есть обеспечить его интерпретацию как литерала), следует поставить два апострофа подряд. Модуль лексического анализа воспринимает удвоенный апостроф в строковой константе как один апостроф-литерал. PostgreSQL также позволяет экранировать апострофы обратной косой чертой, в стиле языка С:
testdb=# SELECT 'PostgreSQL"s great!' AS example;
example
PostgreSQL's great! (1 row)
booktown=# SELECT 'PostgreSQLN's
С-style slashes are great!' AS example;
example
PostgreSQL's C-style slashes are great!
(1 row)
В PostgreSQL также поддерживаются служебные последовательности языка С, перечисленные в табл. 3.3.

Разбиение строковых констант

Листинг 3.5. Разбиение строковых констант

booktown=# SELECT 'book'
booktown-#
booktown-# 'end' AS example;
example
bookend (1 row)
booktown=# SELECT 'bookend' AS example;
example
bookend
(1 row)
Обе команды имеют эквивалентную семантику. Тем не менее фрагменты должны разделяться хотя бы одним разрывом строки, а при попытке разделить их только пробелами PostgreSQL выдает сообщение об ошибке:
booktown=# SELECT 'book' 'end' AS example;
ERROR: parser: parse error at or near .....
Дело в том, что без разрыва строки PostgreSQL считает, что вы ссылаетесь на две отдельные константы. Объединение двух строковых констант в одной строке выполняется оператором конкатенации 11, описанным в главе 5:
booktown=# SELECT 'book.' || 'end1 AS example; example
bookend (1 row)

Использование битовых последовательностей

Листинг 3.6. Использование битовых последовательностей

testdiH* INSERT INTO my_bytes VALUES (B'OOOOOOOO'):
testdb=# SELECT my_byte FROM my_bytes:
my_byte
10000000
10000001
10000101
11111111
00000000
(5 rows)

Использование целочисленных констант

Листинг 3.7. Использование целочисленных констант

booktown=# SELECT * FROM
authors WHERE id < 100;
id | lastjiame | firstjiame
16 | Alcott | Louisa May (1 row)
booktown=# SELECT * FROM authors WHERE id = 100:
id | lastjiame | firstjiame
(0 rows)
booktown=# UPDATE authors
booktown-* SET id = 116
booktown-tf WHERE id = 16:
UPDATE 1
booktown=# SELECT * FROM authors WHERE id = 116:
id | last_name firstjiame
116 | Alcott | Louisa May (1 row)
В листинге 3.7 секция WHERE команды SELECT сравнивает идентификатор поля id с целочисленной константой 100. Результат состоит из одной записи. После обнаружения записи с недопустимым значением id вводится вторая команда SELECT, которая проверяет, существуют ли в таблице записи с кодом i d=116. Мы убеждаемся в том, что код 116 не задействован в таблице authors, поскольку значения в поле id должны быть уникальными. Операция завершается командой UPDATE, также содержащей целочисленные константы в секциях SET и WHERE.

Допустимые вещественные значения

Листинг 3.8. Допустимые вещественные значения

booktown=# SELECT .04 AS small_float.
booktown-# -16.63 AS negative_float,
booktown-# 4e3 AS exponent!al_float,
booktown-# 6.1e2 AS negative_exponent:
small_float | negative_float |
exponential__float | negative_exponent
0.04 -16.63 4000 | 0.061
(1 row)

Различия между true и 'true'

Листинг 3.9. Различия между true и 'true'

testdb=# SELECT true AS boolean_t. testdb-# 'true' AS string_t,
testdb-# false AS boolean_f,
testdb-# 'false' AS string_f;
boo1_t | string_t | bool_f | string_f
t | true | f | false
(1 row)
Как показано в листинге 3.9, PostgreSQL выводит значения логического типа в виде t или f, однако это вовсе не означает, что символы t и f могут использоваться в качестве логических констант. PostgreSQL не сможет правильно интерпретировать их, что приведет к ошибке.

Логические константы

Логические константы

Логические (булевы) константы гораздо проще всех остальных типов констант PostgreSQL, поскольку они принимают всего два допустимых значения: true и false. Встретив любое из этих значений, не заключенное в апострофы, PostgreSQL интерпретирует его как логическую константу. Пример показан в листинге 3.9.

Логические значения

Логические значения

Логическим значением называется простая структура данных, представляющая одну из двух величин: true или f al se. В PostgreSQL поддерживается тип данных boo! ean, определенный в стандарте SQL99, с нестандартным синонимом bool.
Логическим переменным, как и другим типам данных, может присваиваться значение NULL, Логическая переменная, равная NULL, никогда не интерпретируется как true или false; она интерпретируется только как NULL. Если вы хотите проверить, равна ли логическая переменная псевдозначению NULL, не пытайтесь сравнивать ее с false, это бессмысленно. Для этой цели следует использовать конструкцию IS NULL. Способность логической переменной принимать значения true, false и NULL (и правила, в соответствии с которыми NULL считается отличным от этих двух величин) называется тройственной логикой.
В табл. 3.11 приведены допустимые обозначения логических констант, правильно опознаваемые PostgreSQL. Выбор обозначений является делом вкуса. Все разновидности значения true, как и все разновидности значения fal se, интерпретируются сервером одинаково.

Массивы константы

Массивы - константы

При вставке в поле таблицы нового значения, которое представляет собой массив, в команде SQL необходимо перечислить входящие в него элементы. В соответствии с синтаксисом массивов-констант перечисляемые элементы ограничиваются символами-разделителями (запятыми для встроенных типов) и заключаются в фигурные скобки, которые, в свою очередь, заключаются в апострофы: '{ элемент!, элемент2 [ . ...] }'
В этом синтаксисе элементы могут относиться к любому из допустимых типов PostgreSQL. Поскольку весь массив заключается в апострофы, все апострофы внутри элементов массива должны экранироваться как в обычных строковых константах. С другой стороны, выбор запятой в качестве символа-разделителя создает интересную проблему при работе с символьными строками, содержащими запятые, поскольку не заключенные в апострофы запятые интерпретируются как разделители. Но как упоминалось выше, в массивах-константах в апострофы должен заключаться лишь весь массив, а не его отдельные элементы.
В PostgreSQL эта проблема решается заключением строковых констант в кавычки в тех случаях, когда вне контекста массива обычно используются апострофы: '{"valuel","value 2. which contains a comma" }'
Помните, что для правильной интерпретации PostgreSQL массивы обязательно должны заключаться в апострофы. Массив-константу можно рассматривать как особую разновидность строковых констант, которая интерпретируется как массив в зависимости от контекста (например, при добавлении новых данных в поле, имеющее тип массива). В другой ситуации PostgreSQL интерпретирует эту константу как обычный строковый литерал (последовательность символов, заключенная в апострофы), который просто содержит фигурные скобки.

Массивы в таблицах

Массивы в таблицах

В PostgreSQL предусмотрен синтаксис для определения массивов как фиксированного, так и переменного размера; тем не менее в PostgreSQL 7.1.2 ограничение размера не соблюдается. Это означает, что с точки зрения программы массив может иметь фиксированный размер, но при этом изменение размеров все равно производится динамически. Например, поле таблицы, определенное как массив из трех элементов, может содержать три элемента в одной записи, четыре элемента в другой и вообще не содержать элементов в третьей.
Также допускается определение многомерных массивов, в которых каждый элемент может быть не атомарным значением, а другим массивом. При выборке из многомерных массивов элементы выводятся во вложенных фигурных скобках:
booktown=# SELECT editions FROM myjiotes
WHERE title='The Cat In the Hat';
editions
{{"039480001X"."lst Ed. hard Cover"}.
{"039400014"."1st Ed"}} Cl row)

Массивы

Массивы

В исходном варианте реляционной модели значения полей таблиц должны представлять собой атомарные объекты данных. В объектно-реляционных базах данных, к которым относится PostgreSQL, в структурах данных могут использоваться составные значения, называемые массивами.
Массив представляет собой совокупность элементов с общим идентификатором. Элементы массива могут относиться как к встроенному, так и к пользовательскому типу данных, но они обязательно должны быть однотипными. При обращении к элементам массива используется индексная запись с квадратными скобками (например, ту_аггау[0]). Элементы массивов-констант перечисляются в фигурных скобках, заключенных в апострофы (например, '{value_one.value_two,value_three}').

Обязательная защита идентификаторов

Обязательная защита идентификаторов

Идентификаторы обязательно должны заключаться в кавычки только в двух случаях: если идентификатор объекта базы данных совпадает с ключевым словом или в его имени присутствует хотя бы одна прописная буква. В любом из этих случаев идентификатор должен защищаться как при создании объекта, так и при последующих ссылках на него в командах SELECT, DELETE или UPDATE и т. д.
Если не заключить в кавычки идентификатор, совпадающий с ключевым словом, PostgreSQL выдаст сообщение об ошибке, поскольку идентификатор интерпретируется как ключевое слово. Допустим, имеется таблица с именем select. При попытке обратиться к ней со следующим запросом вы получите сообщение об ошибке:
testdb=# SELECT * FROM select
ERROR: parser: parse error at or near "select"
Как показывает этот пример, запрос к незащищенному идентификатору таблицы select приводит к ошибке. Чтобы идентификатор select воспринимался как имя таблицы, а не как ключевое слово, его необходимо заключить в кавычки. Таким образом, правильный запрос к таблице с именем sel ect должен выглядеть так:
testdb=# SELECT * FROM "select";
selected
1
52 105 (4 rows)
Аналогично следует поступать и с идентификаторами, содержащими хотя бы один символ верхнего регистра. Например, если вы по какой-либо причине создали таблицу с именем ProDucts (обратите внимание на прописные буквы Р и D) и теперь хотите ее уничтожить (еще бы, с таким именем!), идентификатор также следует заключить в кавычки:
D0oktown=# DROP TABLE ProDucts;
iRROR: table "products" does not exist
booktown=# DROP TABLE "ProDucts";
DROP
Защита идентификаторов иногда бывает чрезвычайно полезной, даже если вы зсегда создаете объекты базы данных только с «правильными» именами. Наприпер, при импортировании данных через внешнее соединение ODBC (например, is Microsoft Access) имена созданных таблиц могут быть записаны символами верх-iero регистра. Без защиты идентификаторов вам не удастся использовать такие габлицы в программе.

Операторы

Операторы

Другую категорию специальных символов составляют операторы, предназначенные для выполнения различных операций с идентификаторами или константами. Операторы могут использоваться как для математических вычислений (сложение, вычитание и т. д.), так и для сравнения и логических операций.
Вернемся к таблице books и ее числовому полю author_id. Вспомните, что в этом поле хранится целочисленный код, определяющий автора книги. Теперь представьте, что вследствие модификации системы все коды авторов должны быть увеличены на 1500. Задача решается командой UPDATE и выполнением операции с полем author_id. При этом используется оператор сложения (+). Пример приведен в листинге 3.10.

Предварительное планирование

Предварительное планирование

Прежде чем переходить к непосредственному созданию таблиц, желательно выде-ить немного времени на предварительное планирование объектов базы данных, также на выбор имени, типа и смысла каждого поля в таблице. В результате пла-ирования схема выбора имен становится более стройной и последовательной, это, в свою очередь, приводит к появлению более наглядных и «вразумительных» команд и запросов.
Кроме перечисленных семантических факторов (имена, типы и смысл полей), еобходимо проследить за четким установлением связей между таблицами. Проек-ирование связей является важной частью процесса проектирования таблиц, по-кольку любые ошибки в этой области — как дублирование больших объемов дан-ых, так и случайное исключение важных данных из таблиц — являются крайне ежелательными.
Вернемся к таблице books базы данных booktown, структура которой приведена табл. 3.1. В полях каждой записи хранится внутренний код книги, название, код втора и код темы. Обратите внимание: вместо полного имени автора и текстового писания темы в таблице хранятся простые целочисленные коды, используемые ля связи с двумя другими таблицами: authors и subjects. Содержимое этих таблиц астично иллюстрируют табл. 3.26 и 3.27.

Преобразование типов

Преобразование типов

В PostgreSQL поддерживаются три отдельных варианта синтаксиса преобразования (приведения) типов, то есть механизма приведения данных от одного типа к другому. В команде SQL преобразование типов позволяет явно задать тип создаваемой константы (вместо его косвенного определения по правилам языка).
В общем случае приведение строковой константы к другому типу может выполняться любым из трех способов:
тип 'значение'
'значение': -.тип
CAST ('значение' AS тип)
Числовые константы преобразуются в символьную строку следующими способами:
значение: : тип
CAST (значение AS тип)
Здесь значение представляет константу, тип которой требуется изменить, а тип — новый тип этой константы.
Примечание 1
Примечание 1


Помните, что тип money считается устаревшим, что несколко затрудняет его использование в преобразованиях.
Преобразование к другому типу данных не ограничивается одними константами. Поля набора данных, возвращаемого запросом SQL, также могут преобразовываться к другому типу, при этом используются следующие формы синтаксиса:
идентификатор:: тип
CAST (идентификатор AS тип)
Следует учесть, что не каждый тип данных может быть приведен к любому другому типу. Например, не существует осмысленного преобразования символьной строки abed в двоичную последовательность типа bit. Недопустимые попытки преобразования приводят к ошибкам PostgreSQL. Чаще всего встречаются преобразования символьных строк, типов даты/времени или числовых типов к типу text, а также символьных строк в числа.
Кроме синтаксических форм преобразования типа существуют некоторые функции, позволяющие добиться практически того же результата. Имена этих функций часто совпадают с именами итоговых типов (например, text()), хотя существуют и узкоспециализированные варианты (например, bitfromint4()). В листинге 3.30 приведен пример использования функции text О для преобразования целого числа 1000 в строку типа text, содержащую символы «1000».

Псевдозначение NULL

Псевдозначение NULL

Выше говорилось о том, что с каждым полем ассоциируется определенный тип данных и поле принимает значения только этого типа. Тем не менее существует значение, которое может храниться в любых полях независимо от типа; в SQL оно представлено ключевым словом NULL. Ключевое слово NULL не соответствует конкретному объекту данных и потому вообще не считается типом; это системное ключевое слово, которое указывает базе данных на то, что поле не содержит никакого значения. Единственное исключение из правила об универсальности NULL составляют поля, для которых установлено ограничение NOT NULL.
Ключевое слово NULL часто используется для представления необязательных значений. Оно позволяет обойтись без странных и противоестественных схем, например представления отсутствующих данных в целочисленном поле отрицательными числами. Ситуация со временем может измениться, но NULL — всегда NULL.
NULL можно рассматривать как псевдозначение, то есть признак отсутствия значения, который никогда не может быть эквивалентен значению, отличному от NULL. Одна из распространенных ошибок при работе с псевдозначениями NULL связана с тем, что их часто путают с пустыми символьными строками. Отчасти это связано с тем, что при выборке пустой строки данные в клиенте не выводятся. При выборке NULL происходит то же самое, однако NULL принципиально отличается от пустых строк, и это необходимо хорошо понимать, чтобы ваши запросы правильно работали. Поле строкового типа, содержащее пустую строку, содержит последовательность символов, хотя бы и пустую; таким образом, поле имеет определенное значение. Ключевое слово NULL обозначает полное отсутствие значения в поле.
Это весьма принципиальное различие, поскольку правила выполнения операций SQL с пустыми строками очень отличаются от правил операций с псевдозначениями NULL. Особенно заметно эти различия проявляются при объединениях, рассматриваемых в главе 4.
Примеры выборки NULL и пустых строк приведены в листинге 3.15. Первый запрос SELECT показывает, что записи двух книг были вставлены в таблицу без названий (поле ti tie). Тем не менее из последующих запросов становится видно, что в одной записи (id=100) это поле содержит пустую строку, а в другой записи — NULL

Символьные типы

Символьные типы

Символьные типы используются в любых операциях с символьными данными (например, с фрагментами текста в кодировке ASCII). Обычно они применяются для хранения имен, адресов и т. д.
В стандартном языке SQL поддерживаются два символьных типа character и character varyi ng. В PostgrcSQL к ним добавился обобщенный тип text, не требующий явного задания максимального размера поля. Размер полей типа text автоматически изменяется в соответствии с объемом хранящихся данных и практически не ограничивается (размер поля не может превышать один гигабайт, но на практике поля такого размера почти не встречаются). В табл. 3.12 перечислены символьные типы данных PostgreSQL.

Синтаксические символы

Синтаксические символы

Некоторые специальные символы выполняют в командах SQL примерно те же функции, что и знаки препинания в английском языке. В табл. 3.5 перечислены основные синтаксические символы PostgreSQL.

Системные поля

Системные поля

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

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

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

Для сохранения совместимости с предыдущими версиями PostgreSQL разработчики продолжают поддерживать типы данных datetime и timespan. Тип datetime эквивалентен timestamp, а тип timespan — типу interval.
К числу типов даты/времени также относятся типы abstime и reltime, обладающие пониженной точностью представления. Тем не менее это внутренние типы PostgreSQL, которые могут исчезнуть в следующих версиях. Старайтесь использовать только SQL-совместимые типы данных и как можно скорее устранить устаревшие типы из существующих приложений.

Специальные символы

Специальные символы

Специальные символы имеют особое синтаксическое значение в PostgreSQL. По этой причине они обычно не могут использоваться в идентификаторах, хотя, как упоминалось выше в пункте «Защищенные идентификаторы» подраздела «Ключевые слова и идентификаторы», данное ограничение обычно удается обойти при помощи кавычек.

Стандарты SQL

Стандарты SQL

В 1986 году язык SQL был стандартизирован Американским национальным институтом стандартов (ANSI), а в 1987 году появился стандарт Международной организации по стандартам (ISO). Стандарт ANSI/ISO был принят в качестве Федерального стандарта по обработке информации (FIPS) правительства США. В 1989 году был опубликован пересмотренный стандарт, который обычно обозначается сокращениями «SQL89» и «SQL1».
По некоторым причинам (отчасти из-за столкновения интересов фирм-разработчиков) стандарт SQL89 был намеренно оставлен незавершенным, а многие возможности были отнесены к разряду «определяемых при реализации». С целью укрепления стандарта комитет ANSI пересмотрел свою предыдущую работу, и в 1992 году был принят стандарт SQL92 (также называемый SQL2). В новом стандарте были исправлены некоторые недостатки SQL89 и намечены концептуальные особенности SQL, которые на тот момент превосходили возможности любых существующих реализаций РСУБД. Кстати, стандарт SQL92 был в шесть раз длиннее своего предшественника. Вследствие расхождений стандарта с текущей ситуацией авторы определили три уровня соответствия SQL92: начальное соответствие (минимальные улучшения в SQL89), промежуточное соответствие (реально достижимый набор принципиальных улучшений) и полное соответствие (стопроцентное выполнение всех положений SQL92).
Позднее, в 1999 году, в ANSI/ISO был опубликован стандарт SQL99, также называемый SQL3. В этом стандарте рассматривались некоторые нетривиальные аспекты современных SQL-систем, в том числе концепции объектно-реляционных баз данных, интерфейсы уровня вызова и обеспечение логической целостности. На смену уровням соответствия SQL92 пришли новые уровни: базовый и расширенный.
В настоящее время PostgreSQL соответствует большинству положений начального уровня стандарта SQL92, а также многим положениям промежуточного и полного уровней. У многих новых положений, появившихся в SQL99, существуют аналоги в области объектно-реляционных концепций PostgreSQL (массивы, функции и наследование).

Строковые константы

Строковые константы

Строковая константа представляет собой произвольную последовательность символов, заключенную в апострофы. Строковые константы часто используются при вставке новых данных в таблицу и при передаче символьной информации другим
объектам базы данных. Ниже приведен пример использования строковых констант при обновлении имен и фамилий таблицы authors базы данных booktown:
booktown=# SELECT * FROM authors;
id | lastjiame | firstjiame
1809 Geisel | Theodor Seuss
1111 | Denham | Ariel
15990 | Bourgeois | Paulette
25041 | Bianco | Margery Williams
16 | Alcott I Luoisa May
115 | Рое | Edgar Allen
(6 rows)
Из результатов запроса видно, что поле firstjiame с кодом id=16, Louisa May, было ошибочно записано в виде Luoi sa May. Ошибка исправляется командой UPDATE со строковой константой, приведенной в листинге 3.4.

Структура имен идентификаторов

Структура имен идентификаторов

Максимальная длина ключевых слов и идентификаторов PostgreSQL равна 31 симюлу. В процессе лексического разбора все ключевые слова и идентификаторы большей длины автоматически усекаются. Идентификаторы начинаются с любой буквы английского алфавита (a-z) или с символа подчеркивания, далее следует фоизвольное сочетание букв, цифр (0-9) и символов подчеркивания. Ключевые лова не могут начинаться или завершаться символом подчеркивания, но для имен щентификаторов это разрешено. Ни ключевые слова, ни идентификаторы не могут начинаться с цифры.
Выше в пункте «Обязательная защита идентификаторов» было показано, что включение идентификатора в кавычки позволяет «преодолеть» правило игнорирования регистра символов. То же относится и к правилу, согласно которому иденификатор не может начинаться с цифры. Хотя без кавычек PostgreSQL не позволит создать таблицу с именем lst_bent_rul e, в кавычках это имя становится приемлемым.
В листинге 3.3 первая команда пытается создать таблицу с недопустимым именем, после чего вторая команда преодолевает это ограничение при помощи кавычек.

Пример таблицы SQL

Таблица 3.1. Пример таблицы SQL

id	title	authorjd	subjected
7808	The Shining	4156	9
156	The Tell-Tale Heart	15	9
4513	Dune	1866	15
4267	2001: A Space Odyssey	2001	15
1608	The Cat in the Hat	1809	2
1590	Bartholomew and the Oobleck	1809	2

Таблица состоит из четырех столбцов, следующих слева направо в фиксированном порядке. В настоящий момент она содержит шесть записей, также иногда называемых кортежами (tuples). Обратите внимание на очень важное обстоятельство: несмотря на фиксированный порядок столбцов в реляционной базе данных, записи хранятся в произвольном порядке. Как будет показано при описании структуры запросов SQL в главе 4, в SQL существуют средства для упорядочивания записей при выборке, но автоматическое упорядочение записей в самой базе не производится. Если в запросе SQL записи должны следовать в определенном порядке, вы должны явно включить в запрос соответствующую секцию.
Каждая таблица содержит минимум один столбец, однако таблица может не содержать ни одной записи. Каждый вертикальный столбец соответствует фиксированному атрибуту данных, представленных в таблице (как, например, столбец title в таблице books из приведенного выше примера). Без столбцов содержимое соответствующего элемента данных становится неопределенным, тогда как без записей в таблице просто отсутствуют данные. В PostgreSQL 7.1 таблица может содержать до 1600 столбцов и неограниченное количество записей (точнее, ограниченное только аппаратными факторами — например, объемом свободного дискового пространства).
В табл. 3.1 имена столбцов наглядно характеризуют смысл хранящихся в них данных. Впрочем, они выбираются более или менее произвольно, поэтому в процессе планирования имен в таблицах следует помнить о предотвращении возможных конфликтов имен.
Хотя на первый взгляд это и не очевидно, каждый столбец таблицы характеризуется определенным типом данных. Тип данных не только помогает лучше описать информацию, хранящуюся в столбце, но и ограничивает его содержимое. Например, столбец author_id имеет тип Integer; это означает, что любая попытка вставки записи, у которой в этом столбце не находится целое число (например, ПОа), завершится неудачей. Типы данных столбцов подробно описаны в разделе «Типы данных».
В этом разделе были представлены общие принципы логической организации данных в реляционных базах и таблицах. В следующем разделе объясняется, почему все операции с базой осуществляются при помощи команд SQL.

Типы данных PostgreSQL

Таблица 3.10. Типы данных PostgreSQL

Тип данных	Описание	Стандарт
Логические и двоичные типы данных
boolean, bool	Отдельная логическая величина (true или false)	SQL99
bit(n)	Битовая последовательность фиксированной длины (ровно nбит)	SQL92
bit varying(/7),varbit(rt)	Битовая последовательность переменной длины (до n бит)	SQL92
Символьные типы
character(n), char(n)	Символьная строка фиксированной длины (ровно n символов)	SQL89
character varying(n), varchar(n)	Символьная строка переменной длины (до n символов)	SQL92
text	Символьная строка переменной или неограниченной длины	PostgreSQL
Числовые типы
small int, int2	2-байтовое целое со знаком	SQL89
integer, int, int4	4-байтовое целое со знаком	SQL92
bigint, int8	8-байтовое целое со знаком, до 18 цифр	PostgreSQL
real, float4	4-байтовое вещественное число	SQL89
double precision, floats, float	8-байтовое вещественное число	SQL89
numeric(p.s), decimal (p.s)	Число из р цифр, содержащее 5 цифр в дробной части	SQL99
money	Фиксированная точность, представление денежных величин	PostgreSQL, считается устаревшим
serial	4-байтовое целое с автоматическим приращением	PostgreSQL
Время и дата
date	Календарная дата (день, месяц и год)	SQL92
time	Время суток	SQL92
time with time zone	Время суток с информацией о часовом поясе	SQL92
timestamp	Дата и время	SQL92
interval	Произвольный интервал времени	SQL92
Геометрические типы
box	Прямоугольник на плоскости	PostgreSQL
line	Бесконечная линия на плоскости	PostgreSQL
Iseg	Отрезок на плоскости	PostgreSQL
circle	Круг с заданным центром и радиусом	PostgreSQL
path	Замкнутая или разомкнутая геометрическая фигура на плоскости	PostgreSQL
point	Точка на плоскости	PostgreSQL
polygon	Замкнутый многоугольник на плоскости	PostgreSQL
Сетевые типы
cidr	Спецификация сети IP	PostgreSQL
inet	Сетевой IP-адрес с необязательными битами подсети	PostgreSQL
macaddr	МАС-адрес (например, аппаратный адрес адаптера Ethernet)	PostgreSQL
Системные типы
old	Идентификатор объекта (записи)	PostgreSQL
xid	Идентификатор транзакции	PostgreSQL

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

Логические константы

Таблица 3.11. Логические константы

True	False
true	false
't'	Т
'true'	'false'
'У'	'n'
'yes'	'no'
'1' '0'

ВНИМАНИЕ
Помните, что все константы, перечисленные в табл. 3.11 (за исключением true и false), должны заключаться в апострофы. В противном случае сервер выдает сообщение об ошибке.
В листинге 3.17 приведен пример создания таблицы da I ly_i inventory с информацией о наличии книг в магазине. В этой таблице код ISBN ассоциируется с логическим признаком. После создания таблица заполняется серией команд INSERT, в которых передается строковая константа (код ISBN) и логические константы в разных форматах.

Символьные типы

Таблица 3.12. Символьные типы

Тип	Размер	Описание
character(rt), chart/7)	(4+ л) байт	Символьная строка фиксированной длины, дополненная пробелами до п символов
character varying(/?), varchar(/?)	До (4+л) байт	Символьная строка переменной длины, максимальный размер равен п
text	Переменный	Строка переменной длины, максимальный размер не ограничен

Параметр п в табл. 3.12 представляет произвольное количество символов и задается для поля при его создании.
Примечание 1
Примечание 1


Тип данных text не предусмотрен в стандарте ANSI/ISO SQL, однако он поддерживается многими реляционными СУБД, в том числе Sybase и MS SQL Server.

Числовые типы PostgreSQL

Таблица 3.13. Числовые типы PostgreSQL

Тип	Размер	Допустимые значения
bigint, int8	8 байт	Целые числа в интервале от -9 223 372 036 854 775 807 до 9 223 372 036 854 775 807
double precision, floats, float	8 байт	Вещественные числа, 15 значащих цифр, неограниченный размер (с ограниченной точностью)
integer, int, int4 numeric(p.s), decimal (p. s)	4 байта Переменный	Целые числа в интервале от -2 147 483 648 до 2 147 483 647 Целые и вещественные числа из рцифр (всего) и 5 цифр в дробной части
real, float4	4 байта	Вещественные числа, шесть значащих цифр, неограниченный размер (с ограниченной точностью)
small int, int2	2 байта	Целые числа в интервале от -32 768 до 32 767
money	4 байта	Вещественные числа с двумя цифрами в дробной части в интервале от -21 474 836.48 до 21 474 836.47
serial	4 байта	Целые числа в интервале от 0 до 2 147 483 647

Как видно из табл. 3.13, у некоторых типов данных PostgreSQL имеются синонимы, полностью эквивалентные исходным типам. Синонимы были созданы для удобства, хотя иногда это приводит к недоразумениям, поскольку некоторые синонимы встречаются в других языках программирования. Если не знать, с каким типом ассоциируется тот или иной синоним, возможны случайные ссылки на другие типы данных. Например, в PostgreSQL типы real и doubl e представляют значения, которые во многих языках относятся к типу float; при этом у обоих типов имеются синонимы, имена которых содержат слово «float» (float и flot8 относятся к double precision, float4 относится к real). Если вы попытаетесь использовать синоним f I oat, полагая, что он связан с типом real, возникнут проблемы, поскольку в действительности этот синоним связан с типом double precision.
Тип numeric Тип numeric (также называемый типом decimal) предназначен для представления сколь угодно больших или малых значений с фиксированной точностью, задаваемой пользователем. При создании таблицы с полем типа numeric в круглых скобках указываются два значения: точность и масштаб. Точность определяет максимальное количество цифр (включая цифры в дробной части), а масштаб определяет количество цифр только в дробной части. Если параметры не заданы, по умолчанию точность равна 30, а масштаб — 6. Максимальная точность (а следовательно, и максимальный размер), задаваемая таким образом, равна 1000. На практике 1000 цифр обычно вполне достаточно.
Примечание 1
Примечание 1

Предотвращение ошибок переполнения
booktcwn=# INSERT INTO numbers
VALUES (9.99999999);
INSERT 3390697 1
booktown=# SELECT * FROM numbers:
number
10.000000 (1 row)
booktown=# INSERT INTO
numbers VALUES (9999.99999999);
ERROR: overflow on numeric
AMS(value) >= 10*5 for field with precision
11 scale 6 booktown=# INSERT INTO
numbers VALUES (trunc(99999.99999999.6));
INSERT 3390698 1
booktown=# SELECT * FROM
numbers; number
10.000000 99999.999999 (2 rows)
booktown=# INSERT INTO
numbers VALUES (trunc0.99999999. 6));
INSERT 3390699 1
booktown=# SELECT * FROM numbers;
number
10.000000 99999.999999 9.999999
(3 rows)

Типы даты и времени

Таблица 3.14. Типы даты и времени

Тип данных	Размер	Описание	Интервал
date	4 байта	Календарная дата (год, месяц и день)	От 4713 г. до н. э. до 32 767 г. н. э.
time	4 байта	Время суток без часового пояса	От 00:00:00 до 23:59	:59.99
time with time zone	4 байта	Время суток с часовым поясом	От 00:00:00+12 до 23:59:59.99-12

timestamp with time zone, 8 байт Календарная дата и время От 1903 г. н. э.
timestamp с часовым поясом до 2037 г. н. э.
interval 12 байт Общий промежуток От-1780 000 000 лет
времени до 1 780 000 000 лет

Форматы представления даты

Таблица 3.15. Форматы представления даты

Пример	Описание
July 1. 2001	Название месяца, день и год
Sunday July 1. 2001	Название дня недели, название месяца, день и год
July 15. 01 BC	Название месяца, день и год до нашей эры
2001-07-01	Стандартный формат ISO-8601: год, месяц и день в числовом виде
20010715	ISO-8601: полный год, месяц, день
010715	ISO-8601: год из двух цифр, месяц, день
7/01/2001	Американский формат: месяц, день и год
1/7/2001	Европейский формат: день, месяц и год
2001.182	Числовой формат с полным годом и номером дня в году

При указании имени месяца в датах следует либо ввести полное название месяца, либо выбрать одно из стандартных сокращений, перечисленных в табл. 3.16.

Сокращенные обозначения месяцев

Таблица 3.16. Сокращенные обозначения месяцев

Месяц	Сокращение
Январь	Jan
Февраль	Feb
Март	Mar
Апрель	Apr
Май	May
Июнь	Jun
Июль	Jul
Август	Aug
Сентябрь	Sep, Sept
Октябрь	Oct
Ноябрь	Nov
Декабрь	Dec

В табл. 3.17 приведены аналогичные сокращения для дней недели.

Сокращенные обозначения дней недели

Таблица 3.17. Сокращенные обозначения дней недели

День	Сокращение
Воскресенье	Sun
Понедельник	Моп
Вторник	Tue, Tues
Среда	Wed, Weds
Четверг	Thu, Thur, Thurs
Пятница	Fri
Суббота	Sat

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


Хотя даты можно форматировать при помощи строковых функций (таких, как to_char()), гораздо эффективнее установить по умолчанию как можно более близкое представление и лишь затем применять ручное преобразование типов и форматирование текста.
Общий формат вывода даты/времени устанавливается применением команды SET к переменной DATESTYLE. Переменной может быть присвоено одно из четырех стандартных значений, перечисленных в табл. 3.18.

Константы форматов даты

Таблица 3.18. Константы форматов даты

Общий формат	Описание	Пример
ISO	Стандарт ISO-8601	2001-06-25 12:24:00-07
SQL	Традиционный формат SQL	06/25/2001 12:24:00.00 РОТ
Postgres	Исходный формат PostgreSQL	Моп 25 Jun 12:24:00 2001 PDT
German	Региональный формат для Германии	25.06.2001 12:24:00.00 РОТ

Например, формат SQL устанавливается следующей командой SQL:
booktown=# SET DATESTYLE TO SQL;
SET VARIABLE
Если выполнить запрос SELECT current_timestamp после присваивания, PostgreSQL вернет текущее время в формате SQL:
booktown=# SELECT current_timestamp;
timestamp
08/10/2001 13:25:55.00 PDT (1 row)
Вывод текущего значения переменной DATESTYLE во время работы PostgreSQL производится следующей командой:
booktown=# SHOW DATESTYLE;
NOTICE: DateStyle is SQL
with US (NonEuropean)
conventions SHOW VARIABLE
Помимо общих форматов, представление даты в PostgreSQL зависит от другого фактора: порядка перечисления компонентов (табл. 3.19). Этот порядок перечисления определяет, должен ли в выводимой дате день следовать за месяцем или наоборот. Порядок перечисления компонентов применяется к четырем общим форматам знакомой командой SET DATESTYLE и не изменяет в формате ничего, кроме относительного расположения дня и месяца.

Дополнительные форматы вывода даты

Таблица 3.19. Дополнительные форматы вывода даты

Формат	Описание	Пример
European	День/месяц/год	12/07/2001 17:34:50.00 МЕТ
US. NonEuropean	Месяц/день/год	07/12/2001 17:34:50.0 PST

Более того, общий формат и относительный порядок дня/месяца можно задать в одной команде SET с разделением констант запятыми. Порядок перечисления констант в команде SET не важен, если они не являются взаимоисключающими (например, SQL и ISO). Пример приведен в листинге 3.26.

Основные действия PostgreSQL

Таблица 3.2. Основные действия PostgreSQL

Действие	Описание
CREATE DATABASE	Создание новой базы данных
CREATE INDEX	Создание нового индекса для столбца таблицы
CREATE SEQUENCE	Создание новой последовательности в существующей базе данных
CREATE TABLE	Создание новой таблицы в существующей базе данных
CREATE TRIGGER	Создание нового определения триггера
CREATE VIEW	Создание нового представления для существующей таблицы
SELECT	Выборка записей из таблицы
INSERT	Вставка одной или нескольких новых записей в таблицу
UPDATE	Модификация данных в существующих записях
DELETE	Удаление существующих записей из таблицы
DROP DATABASE	Уничтожение существующей базы данных
DROP INDEX	Удаление индекса столбца из существующей таблицы
DROP SEQUENCE	Уничтожение существующего генератора последовательности
DROP TABLE	Уничтожение существующей таблицы
DROP TRIGGER	Уничтожение существующего определения триггера
DROP VIEW	Уничтожение существующего представления
CREATE USER	Создание в системе новой учетной записи пользователя PostgreSQL
ALTER USER	Модификация существующей учетной записи пользователя PostgreSQL
DROP USER	Удаление существующей учетной записи пользователя PostgreSQL
GRANT	Предоставление прав доступа к объекту базы данных
REVOKE	Лишение прав доступа к объекту базы данных
CREATE FUNCTION	Создание новой функции SQL в базе данных
CREATE LANGUAGE	Создание нового определения языка в базе данных
CREATE OPERATOR	Создание нового оператора SQL в базе данных
CREATE TYPE	Создание нового типа данных SQL в базе данных

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


В учебниках SQL термины «команда» и «запрос» часто считаются эквивалентными. В этой книге термин «запрос» используется только по отношению к командам, возвращающим данные (например, SELECT), а не к общим командам SQL, которые также могут создавать и модифицировать данные.
Во внутреннем представлении PostgreSQL структурированные команды SQL интерпретируются в виде последовательности лексем, обычно разделяемых пропусками (пробелами или символами новой строки вне парных ограничителей), хотя некоторые лексемы могут следовать без пропусков, если это не вызывает неоднозначной интеопоетапии (например, опепатопы могут стоять вплотную к идеитификаторам). В данном контексте лексемой считается слово или символ, осмысленно идентифицируемый сервером в процессе разбора (интерпретации) команды SQL.
С технической точки зрения каждая лексема может быть ключевым словом, идентификатором, защищенным идентификатором, константой (также встречается термин «литерал») или одним из специальных символов. К категории ключевых слов PostgreSQL относит слова, имеющие заранее определенный смысл в контексте SQL или PostgreSQL — действия, секции, имена функций и некоторые необязательные составляющие команд SQL (как, например, слово WORK в команде COMMIT). Идентификаторы представляют имена переменных для таблиц, столбцов и других объектов баз данных.
Ключевые слова и идентификаторы относятся к внутренним функциям, значениям и записям, смысл которых определяется PostgreSQL С другой стороны, константы описывают данные, интерпретируемые буквально (например, числа или символьные строки).
Наконец, команды SQL могут содержать специальные символы. К этой категории относятся зарезервированные символы (круглые и квадратные скобки, точка с запятой), влияющие на смысл и расположение ключевых слов, идентификаторов и литералов. Специальные символы можно рассматривать как своего рода «знаки препинания» в командах SQL.
Операторы также относятся к категории специальных символов и используются для применения логических или математических операций между данными (литеральными или представленными в виде идентификаторов). Обычно операторы содержат от одного до четырех символов.
В нескольких ближайших подразделах эти базовые компоненты SQL описываются более подробно.

Taблица 3 20 Форматы представления времени

Taблица 3.20. Форматы представления времени

Пример	Описание
01:24	ISO-8601 с точностью до минут
01:24 AM	Эквивалент 01:24 (суффикс AM используется только для наглядности и не влияет на значение)
01:24 РМ	Эквивалент 13:24 (для использования суффикса РМ час должен быть меньше либо равен 12)
13:24	24-часовой формат, эквивалент 01:24 РМ
01:24:11	ISO-8601, с точностью до секунд
01:24:11.112	ISO-8601, с точностью до микросекунд
012411	ISO-8601, с точностью до секунд, числовое форматирование

В дополнение к этим форматам в PostgreSQL предусмотрена возможность уточнения времени в типах time и time with time zone. Дополнительные форматы перечислены в табл. 3.21.

Допустимые форматы часового пояса

Таблица 3.21. Допустимые форматы часового пояса

Пример	Описание
01:24:11-7	ISO-8601, GMT + 7 часов
01:24:11-07:00	ISO-8601, GMT + 7 часов 0 минут
01:24:11-0700	ISO-8601, GMT + 7 часов 0 минут
01:24:11 PST	ISO-8601, тихоокеанское стандартное время (GMT + 7 часов)

Примечание 3
Примечание 3


В PostgreSQL поддерживаются все сокращенные обозначения часовых поясов, предусмотренные в стандарте ISO.
Tnntime with time zone поддерживается в PostgreSQL в основном для сохранения совместимости с существующими стандартами SQL и другими СУБД. Если вам потребуется работать с часовыми поясами, рекомендуется использовать тип timestamp, описанный в следующем пункте. Это объясняется прежде всего тем, что из-за действия летнего времени осмысленная интерпретация часовых поясов иногда возможна лишь при наличии даты.
Во внутреннем представлении PostgreSQL вся информация о часовых поясах хранится в виде числового смещения от времени по Гринвичу (GMT), также называемого UTC (Universal Coordinated Time). По умолчанию PostgreSQL выводит время в часовом поясе, заданном в файле конфигурации операционной системы. Если вы хотите, чтобы время выводилось в другом часовом поясе, это можно сделать четырьмя способами.

• Настройка переменной среды TZ на сервере. Переменная используется для определения часового пояса по умолчанию при запуске postmaster. Например, она может задаваться в файле .bash_profile пользователя postgres командой export TZ='zone'.
• Настройка переменной среды PGTZ на стороне клиента. Переменная среды PGTZ может быть прочитана любым клиентом, написанным с использованием библиотеки libpq. Значение интерпретируется как стандартный часовой пояс клиента.
• Команда SQL SET TIMEZONE ТО. Команда устанавливает для текущего сеанса заданный часовой пояс (например, SET TIMEZONE TO UTC).
• Секция SQL AT TIME ZONE. Согласно стандарту SQL92 значение секции задается в виде обозначения часового пояса (например, PST) или интервала (например, Interval ( -07:00')). Секция AT TIME ZONE включается в команду SQL после значения времени (например, SELECT my_t1mestamp AT TIME ZONT 'PST').

Примечание 4
Примечание 4


Если переменная часового пояса содержит недопустимое значение, в большинстве систем по умолчанию используется время по Гринвичу (GMT). Кроме того, если при компиляции PostgreSQL был задан ключ USE_AUSTRALIAN_RULES, обозначение EST относится к австралийскому восточному стандартному времени (смещение +10.00 часов по отношению к GMT), а не к восточному стандартному времени США.

Примеры данных типа timestamp

Таблица 3.22. Примеры данных типа timestamp

Пример	Описание
1980-06-25 11:11-7	Формат даты ISO-8601 с точностью до минут, часовой пояс PST
25/06/1980 12:24:11.112	Европейский формат даты с точностью до микросекунд
06/25/1980 23:11	Американский формат даты с точностью до минут в 24-часовом представлении
25.06.1980 23:11:12 РМ	Немецкий региональный формат даты с точностью до микросекунд и суффиксом РМ

ВНИМАНИЕ
Хотя в PostgreSQL поддерживается синтаксис создания полей или значений типа timestamp without time zone, в PostgreSQL 7.1.2 полученный тип данных все равно содержит информацию о часовом поясе.

Константы даты и времени

Таблица 3.23. Константы даты и времени

Константа	Описание
current	Текущее (отложенное) время обработки транзакции. В отличие от now не привязывается к конкретному моменту времени и возвращает текущее системное время
epoch infinity	1970-01-01 00:00:00+00 («день рождения» Unix) Абстрактная константа, более «поздняя» по сравнению со всеми допустимыми значениями даты и времени
-infinity	Абстрактная константа, более «ранняя» по сравнению со всеми допустимыми значениями даты и времени
now	Фиксированное время обработки транзакции
today	Полночь текущего дня
tomorrow	Полночь следующего дня
yesterday	Полночь предыдущего дня

В PostgreSQL также поддерживаются три встроенные функции для получения текущего времени, даты и их комбинации. Для них были выбраны подходящие имена current_date, current_time и current_timestamp.
Если судить только по именам, константы now и current на первый взгляд кажутся идентичными. В действительности они принципиально различаются по способу хранения в таблице. Константа now транслируется в системное время на момент выполнения команды (например, время вставки, если константа используется в команде INSERT). С другой стороны, константа current обычно применяется в особых случаях (например, при отслеживании сведений о процессах) для вычисления разности между начальным временем, зафиксированным константой now, и текущим временем; результат определяет время выполнения процесса. В листинге 3.28 константы now и current используются для построения журнала задач. Первая команда создает таблицу с полями для хранения имени задачи, начальной и конечной даты/времени. В таблице создаются записи двух задач, при этом начальное время задается константой now, а конечное время — константой current. Из листинга видно, что обе задачи не завершены.
ВМИМАНИЕ
Константы даты/времени, как показано в листинге 3.28, обязательно заключаются в апострофы.

Геометрические типы

Таблица 3.24. Геометрические типы

Тип	Размер	Описание	Синтаксис
point	16 байт	Точечный объект, характеризуемый только координатами на плоскости. Координаты А- и /представляются вещественными числами	(А-./)
Iseg	32 байта	Отрезок прямой. Задается координатами начальной и конечной точек	((xl.yl). (х2.у2))
box	32 байта	Прямоугольник. Задается координатами двух углов, расположенных по диагонали	((xl.yl) лх2.у2))
path	4+32хлбайт	Замкнутая фигура (аналог многоугольника): множество из п точек, соединенных отрезками	((xl.yl)....)
path	4+32х/?байт	Разомкнутая фигура (аналог многоугольника): множество из п точек, соединенных отрезками	[(xl.yl),...]
polygon	4+32х/7 байт	Многоугольник (аналог замкнутой фигуры): п конечных точек отрезков, образующих контур многоугольника	((xl.yl)....)
circle	24 байта	Круг с центром в точке (х.у) и радиусом г	<(х,у) .r >

Системные поля

Таблица 3.25. Системные поля

Поле	Описание
old	4-байтовый уникальный идентификатор объекта записи. В пределах одной таблицы значения end никогда не повторяются
tableoid	Идентификатор объекта таблицы, содержащей запись. Имя таблицы связывается с идентификатором в системной таблице рд class
xmin	Идентификатор транзакции вставки для кортежа
cmin	Идентификатор команды, ассоциированной с транзакцией вставки для кортежа
xmax	Идентификатор транзакции удаления для кортежа. Для видимых (не удаленных) кортежей равен нулю
cmax	Идентификатор команды, ассоциированной с транзакцией удаления для кортежа. По аналогии с xmax равен нулю для видимых кортежей
ctid	Идентификатор, описывающий физическое местонахождение кортежа в базе данных. Поле ctid содержит пару чисел: номер блока и индекс кортежа в блоке

Таблица authors

Таблица 3.26. Таблица authors

id	last_name	first_name
1809	Geisel	Theodor Seuss
illl	Denham	Ariel
15990	Bourgeois	Paulette
2031	Brown	Margaret Wise
25041	Margery Williams	Bianco
16	Alcoa	Louisa May
115	Poe	Edgar Allen

Таблица subjects

Таблица 3.27. Таблица subjects

id	subject	location
1809	Arts	Creativity St
1111	Children's Books	Kids Ct
15990	Classics	Academic Rd
2031	Computers	Productivity Ave
25041	Drama	Main St
16	Horror	Black Raven Dr
115	Science Fiction	Main St

Вынесение данных об авторе и теме из таблицы books повышает эффективность хранения данных. Если в таблице имеются данные о нескольких книгах, относящихся к одной теме, то вместо нескольких экземпляров полных данных, связанных с темой, в таблице будут храниться только значения subjected. Кроме того, это упрощает модификацию данных, связанных с темой книги (например, информации о размещении этих книг на полках магазина). Такие данные достаточно один раз изменить в одной небольшой таблице вместо того, чтобы обновлять множество записей в основной базе. Аналогичные рассуждения применимы и к таблице authors, связанной с books по полю authorjd.
Тщательное планирование также помогает избежать ошибок при выборе типов данных. Например, таблица editions связывает коды ISBN с кодами книг, хранящимися в таблице booktown. На первый взгляд кажется, что для представления кодов ISBN можно воспользоваться полем типа integer, однако такое решение было бы ошибочным, поскольку коды ISBN иногда содержат символьные данные. Кроме того, в поле типа integer будут теряться начальные нули (код 0451160916 превратится в 451160916).
Из всего сказанного можно сделать вывод, что проектирование таблиц является важной составляющей процесса проектирования базы данных.

Служебные последовательности

Таблица 3.3. Служебные последовательности PostareSQL в стиле С

Последовательность	Описание
\\	Обратная косая черта (литерал)
V	Апостроф (литерал)
\Ь	Забой
\f	Подача листа
\п	Новая строка
\г	Возврат курсора
\t	Табуляция
\ххх	ASCII-символ с восьмеричным кодом ххх

ВНИМАНИЕ
Вследствие того что обратная косая черта имеет особый смысл (см. табл. 3.3), при включении в строку этот символ обязательно экранируется другой косой чертой (например, в строке 'A single backslash is: \\' двойной символ обратной косой черты преобразуется в один).
Если две строковые константы PostgreSQL разделены промежутком, в который входит хотя бы один символ новой строки, они объединяются в одну строковую константу. Пример приведен в листинге 3.5.

Представление величин с плавающей точкой

Таблица 3.4. Представление величин с плавающей точкой

Представление	Пример
##.##	6.4
##e[+-]##	8е-8
[##].##[e[+-]##]	.04e8
##.[##][e[+-]##]	4e.5

В первом формате до десятичной точки и после нее должна стоять хотя бы одна цифра. Это необходимо для того, чтобы модуль лексического анализа PostgreSQL опознал значение как вещественную, а не целочисленную константу. В других форматах хотя бы одна цифра должна стоять до или после экспоненты, обозначенной буквой е. Наличие десятичной точки и/или экспоненты отличает вещественные константы от целочисленных.
В листинге 3.8 использование всех вещественных форматов продемонстрировано на примере простой команды SQL SELECT.

Синтаксические символы

Таблица 3.5. Синтаксические символы

Символ	Определение
* (звездочка)	Выборка всех полей таблицы в команде SELECT, а также подсчет всех записей в агрегатной функции count ()
( ) (круглые скобки)	Группировка выражений, изменение приоритета операторов и вызов функций. Смысл круглых скобок в значительной степени зависит от контекста
[ ] (квадратные скобки)	Выборка конкретного элемента массива или объявление типа массива (например, в команде CREATE TABLE)
: (точка с запятой)	Признак завершения команды SQL. Внутри команд может использоваться только в строковых константах и защищенных идентификаторах
. (запятая)	Разделитель элементов в списке
. (точка)	Десятичный разделитель в вещественных константах (например, 3.1415), а также квалификатор имен полей (например, table name. column name)
: (двоеточие)	Определение срезов (slices) в массивах
$ (знак доллара)	Обозначение позиционного параметра в определении функции

Основные операторы PostgreSQL

Таблица 3.6. Основные операторы PostgreSQL

Оператор	Определение
Математические операторы
+	Сложение двух чисел
-	Вычисление разности двух чисел
/	Вычисление частного от деления двух чисел
*	Умножение двух чисел
!	Факториал целого числа
§	Модуль (абсолютное значение) числа
Операторы сравнения
=	Проверка эквивалентности двух величин
<	Проверка условия «первое число меньше второго»
>	Проверка условия «первое число больше второго»
~	Поиск совпадения регулярного выражения в тексте
Логические операторы
NOT	Логическое отрицание
AND	Логическая конъюнкция (true, если оба логических операнда равны true)
OR	Логическая дизъюнкция (true, если хотя бы один из логических операндов равен true)

Смысл многих операторов может изменяться в зависимости от контекста, но оператор = играет особенно важную роль в секции SET команды UPDATE.
Хотя в большинстве выражений оператор = требуется для проверки эквивалентности двух величин, в сочетании с идентификатором в секции SET команды UPDATE он становится оператором присваивания и используется для присваивания нового значения существующему идентификатору.
За дополнительной информацией об операторах обращайтесь к разделу «Операторы» в главе 5.

Простой запрос SQL

Таблица 3.7. Простой запрос SQL

	SELECT	id, name	FROM	states
Тип лексемы	Ключевое слово	Идентификаторы	Ключевое слово	Идентификатор
Описание	Команда	Имена полей	Имя секции	Имя таблицы

Как видно из таблицы, команда SELECT содержит ключевые слова SELECT и FROM. Ключевое слово FROM с лексемой states образует секцию, уточняющую смысл команды SELECT.
Лексемы id, name и states в приведенном примере являются идентификаторами. Идентификаторы Id и name определяют выбираемые поля, а идентификатор states определяет имя таблицы, из которой производится выборка. Таким образом, приведенный выше запрос приказывает PostgreSQL выбрать поля Id и name каждой записи таблицы states. В листинге 3.13 показаны результаты выполнения этого запроса.

Команда UPDATE с секцией SET

Таблица 3.8. Команда UPDATE с секцией SET

UPDATE	states	SET	id	=	51
Ключевое слово	Идентификатор	Ключевое слово	Идентификатор	Оператор	Целочисленная константа
Команда	Имя таблицы	Имя секции	Имя поля	Присваивание	Новое значение поля id

Секция WHERE

Таблица 3.9. Секция WHERE

WHERE	name	=	'Oregon'
Ключевое слово	Идентификатор	Оператор	Строковая константа
Имя секции	Имя поля	Проверка эквивалентности	Искомое значение

Приведенная команда просматривает содержимое поля name каждой записи и проверяет, совпадает ли оно с условием секции WHERE (совпадение со строковой константой ' Oregon'). Затем во всех записях, соответствующих этому условию, полю id присваивается значение 51.
Итак, рассмотренная команда UPDATE содержит три ключевых слова, три идентификатора и две константы. Ключевыми словами являются лексемы UPDATE (выполняемое действие), SET (правило обновления записей) и WHERE (критерий отбора обновляемых записей).
Оба оператора представлены знаком =. В секции SET этот знак используется для присваивания (то есть обновления поля существующей записи) — применение, специфическое для секции SET. С другой стороны, в секции WHERE оператор = используется для сравнения двух значений. В данном примере поле name записи сравнивается со строковой константой Oregon.
Наконец, в примере присутствует целочисленная константа 51 (новое значение поля id) и строковая константа Oregon (сравниваемая с полем name в секции WHERE).
Таким образом, команда UPDATE, приведенная в листинге 3.14, обновляет таблицу states, присваивая значение 51 полю Id всех записей, у которых поле name содержит значение Oregon. Результат проверяется следующей командой SELECT.

Таблицы в PostgreSQL

Таблицы в PostgreSQL

Многие программисты (особенно обладающие опытом работы с другими реляционными СУБД на базе SQL) хорошо знакомы с общими концепциями реляционных баз данных, рассмотренными в этой главе. Тем не менее в разных РСУ БД используются разные механизмы работы с таблицами на системном уровне. В этом разделе более подробно описана реализация таблиц в PostgreSQL.

Таблицы

Таблицы

Данный раздел посвящен таблицам — одному из важнейших элементов SQL. Таблицы необходимо знать во всех подробностях, поскольку именно в таблицах хранятся все данные. Хорошее знание логической структуры таблиц является обязательным условием правильного планирования и проектирования структур данных SQL и всех программных функций, обеспечивающих доступ к этим данным.
Таблица состоит из строк (записей) и столбцов (полей), пересечения которых называются элементами данных. В электронных таблицах (например, в Excel) элементам данных соответствуют ячейки таблицы. Столбец определяет имя и тип данных, хранящихся в соответствующем элементе данных записи. Каждая запись (строка таблицы) состоит из элементов данных, описываемых именем и типом соответствующего столбца. Таким образом, каждый элемент данных в записи косвенно связан со всеми остальными элементами этой записи. В определенном смысле поле можно рассматривать как описание отдельного элемента записи, а каждую запись — как совокупность данных, удовлетворяющих этим описаниям.
В табл. 3.1 приведено описание структуры простой таблицы books. Ссылки на эту таблицу будут неоднократно встречаться в дальнейших примерах. В каждой записи таблицы хранится информация об отдельной книге: числовой код книги, название, код автора и код темы. Эти характеристики описываются полями 1 d, ti tl e, authoMd и subjected (слева направо).

Тип money

Тип money

Тип money предназначен для хранения денежных величин и обычных чисел. На момент написания книги тип money считается устаревшим и использовать его не рекомендуется. В книге он представлен лишь как один из действующих типов, который все еще может встречаться в существующих системах PostgreSQL.
Вместо типа money следует использовать тип numeric с масштабом 2 и точностью, достаточной для представления максимальной необходимой величины (включая две цифры для дробной части). Форматирование, аналогичное типу money, выполняется при помощи функции to_char(), используемой в листинге 3.23. В этом примере продемонстрирован оператор конкатенации и функция форматирования текста ltrim(), о которых рассказано в главе 4.

Тип serial

Тип serial

Хотя тип serial не относится к числу стандартных типов, он часто используется при создании в таблице полей-идентификаторов, содержащих уникальное значение для каждой записи. В типе serial объединены функциональные возможности 4-байтового типа integer, индекса и последовательности. В листинге 3.24 тип serial генерирует уникальный идентификатор для каждой записи в таблице auto_identified.
В листинге 3.25 та же задача решается при помощи поля типа integer, функции nextval() и последовательности (последовательности описаны в главе7). На момент написания книги эти два способа были функционально тождественными.

Тип timestamp

Тип timestamp

Тип timestamp PostgreSQL сочетает функциональные возможности типов date и time. Формат timestamp состоит из даты, за которой следует минимум один пробел, после чего идет время и необязательный часовой пояс.
В этом формате поддерживаются любые сочетания форматов даты и времени, перечисленные в табл. 3.15 и 3.20. Примеры допустимого ввода в формате timestamp приведены в табл. 3.22.

Типы данных

Типы данных

SQL относится к категории языков с сильной типизацией. Это означает, что с любым объектом данных, представленным в PostgreSQL, связывается определенный тип, даже если на первый взгляд это и не очевидно. Тип данных одновременно определяет и ограничивает разновидности операций, которые могут выполняться с этими данными.
Типы не только ассоциируются со всеми данными, но и играют важную роль при создании таблиц. Как упоминалось в разделе «Знакомство с реляционными базами данных», таблицы состоят из одного или нескольких полей. При создании таблицы каждому полю, помимо имени, назначается определенный тип данных.
Примечание 1
Примечание 1


Хотя в PostgreSQL предусмотрен достаточно широкий спектр встроенных типов данных, вы также можете определять собственные типы данных командой CREATE TYPE. За дополнительной информацией обращайтесь к описанию команды CREATE TYPE.
В табл. 3.10 перечислены базовые типы данных PostgreSQL, а также их синонимы (альтернативные имена). Также существует множество внутренних (то есть не предназначенных для нормального использования) и устаревших типов данных, которые не приводятся в таблице.
Хотя большинство типов данных PostgreSQL взято непосредственно из стандартов SQL, существуют и другие, нестандартные типы данных (например, гео-
метрические и сетевые типы). По этой причине у типов данных PostgreSQL не всегда находятся прямые аналоги в других СУБД на базе SQL.

Вещественные константы

Вещественные константы

Вещественные константы обладают определенным сходством с целочисленными константами, но используются для представления не только целых, но и дробных величин.
Существует несколько форматов представления вещественных констант, представленных в табл. 3.4. Запись ## означает одну или несколько цифр.

Встроенные константы даты и времени

Встроенные константы даты и времени

В PostgreSQL предусмотрено несколько специальных констант, представляющих стандартные значения даты и времени. Эти константы перечислены в табл. 3.23.

команда SQL состоит из отдельных

Выводы

Итак, команда SQL состоит из отдельных лексем, каждая из которых может быть ключевым словом, идентификатором, защищенным идентификатором, константой или специальным символом. В табл. 3.7 структура команд SQL поясняется на примере простой команды SELECT.

Защищенные идентификаторы

Защищенные идентификаторы

Хотя обычно это и не требуется, идентификаторы могут заключаться в кавычки, указывающие на их буквальную интерпретацию. Например, просмотр всех полей таблицы с именем states обычно производится следующей простой командой:
booktown=# SELECT * FROM states;
id | name | abbreviation
---+-------+---------------
33|Oregon|OR
42| Washington | WA
(2 rows)
Команда содержит ключевые слова SELECT и FROM, а также идентификаторы * (обозначение всех полей) и states (имя таблицы). Команда производит выборку всех полей таблицы states и выводит все ее содержимое.
Аналогичного эффекта можно добиться, заключив идентификатор в кавычки:
booktown=# SELECT * FROM "states";
id | name I abbreviation
33 | Oregon | OR 42 | Washington | WA (2 rows)
Как показывает этот пример, применение кавычек к идентификаторам, записанным символами нижнего регистра, ни на что не влияет. Однако попытка защитить идентификатор stAtes в следующей команде приводит к неудаче:
booktown=# SELECT * FROM "stAtes";
ERROR: Relation 'stAtes' does not exist
Дело в том, что команда приказывает PostgreSQL найти таблицу с именем stAtes (вместо states). Другими словами, заключая идентификатор в кавычки, мы требуем, чтобы интерпретатор PostgreSQL интерпретировал его буквально.
Все незащищенные идентификаторы преобразуются к нижнему регистру. Любая смешанная комбинация символов разных регистров (stAtEs, STATES) при отсутствии кавычек перед выполнением команды автоматически приводится к виду states.
Примечание 1
Примечание 1


Преобразование незащищенных идентификаторов к нижнему регистру является отличительной особенностью PostgreSQL. В соответствии со стандартом SQL92 незащищенные идентификаторы должны преобразовываться к верхнему регистру. По историческим причинам, а также для удобства чтения PostgreSQL не выполняет требования данной части стандарта SQL92. Это обстоятельство особенно важно для администраторов баз данных, знакомых с другими продуктами SQL, в которых идентификаторы автоматически преобразуются к верхнему регистру (например, Oracle). Чтобы ваши приложения легко адаптировались для других платформ, помните о проблеме регистра во избежание конфликтов.
Модуль лексического разбора нормально воспринимает команды, записанные в смешанном регистре (при условии правильности их синтаксиса). Тем не менее к выбору регистра символов при записи программ следует относиться внимательно, поскольку смена регистра символов при оформлении программы может как упростить, так и затруднить чтение большого объема кода SQL.
В этой книге идентификаторы записываются символами нижнего регистра, а ключевые слова — верхнего. Визуальное отделение фиксированных системных синтаксических элементов от пользовательских объектов данных значительно упрощает чтение и понимание сложных команд SQL.

Знакомство с реляционными базами данных

Знакомство с реляционными базами данных

PostgreSQL относится к категории объектно-реляционных систем управления базами данных (ОРСУБД). Модель ОРСУБД представляет собой усовершенствование более традиционной модели реляционной системы управления базами данных (РСУБД). В РСУБД логически связанные данные хранятся в двумерных структурах, называемых таблицами. Данные могут состоять из элементов, относящихся к различным стандартным типам — целые и вещественные числа, символы, строки, дата/время. В таблице элементы данных образуют «решетку» из столбцов (полей) и строк (записей). Одной из главных особенностей реляционной модели является ее концептуальная простота, причем это может считаться как ее главным достоинством, так и главным недостатком.
Объектно-реляционная специфика PostgreSQL дополняет традиционную реляционную модель данных многочисленными усовершенствованиями. К их числу относится поддержка массивов (хранения нескольких элементов в одном поле), наследования (связей типа «предок—потомок» между таблицами) и функций (программных методов, вызываемых командами SQL). Для опытных программистов в PostgreSQL даже предусмотрены возможности расширения типов данных и использования процедурных языков.
Вследствие объектно-реляционной ориентации таблицы иногда называются классами, а записи и поля могут соответственно именоваться экземплярами (instances) и атрибутами (attributes). В книге эти термины считаются синонимами. Другие структуры данных SQL (такие, как индексы и представления) иногда называются объектами базы данных.
Примечание 1
Примечание 1


Учтите, что термин «объектно-реляционный» не является синонимом термина «объектно-ориентированный», характерного для многих современных языков программирования. Несмотря на поддержку ряда объектных усовершенствований, PostgreSQL все равно формально является реляционной системой управления базами данных (РСУБД).

Знакомство с SQL

Знакомство с SQL

SQL (Structured Query Language) — мощный, универсальный и проверенный временем язык запросов к реляционным базам данных. История SQL восходит к научным разработкам компании IBM в 70-х годах. В нескольких ближайших разделах вы познакомитесь с историей языка SQL, его предшественниками и различными стандартами SQL, появившимися за эти годы.

SQL в PostgreSQL

Анализ структуры таблицы

Анализ структуры таблицы

Команда \d (с указанием имени таблицы) предназначена для вывода структуры таблицы и ее ограничений, если они имеются. В листинге 4.7 приведены выходные данные команды \d для таблицы books, созданной в предыдущем разделе.
Обратите внимание: каждая строка результата описывает одно из полей (то есть столбцов) таблицы; таким образом, таблица фактически разворачивается па 90°. Такое представление выбрано для наглядности, поскольку многие таблицы содержат большое количество полей, не помещающихся на экране (или на печатной странице) по горизонтали. В книге этот формат будет использоваться при описании структуры таблиц.

COPY TO

COPY TO

Синтаксис команды COPY FROM практически аналогичен синтаксису команды, предназначенной для экспортирования данных в файл, просто ключевое слово FROM заменяется ключевым словом ТО. Кроме того, ключевое слово stdin заменяется ключевым словом stdout, если выходные данные вместо файла направляются в стандартный вывод (например, на экран в psql). В листинге 4.22 приведен пример экспортирования таблицы books в ASCII-файл.

Добавление данных командами INSERT и COPY

Добавление данных командами INSERT и COPY

После создания таблицы с заданной структурой наступает следующий этап — заполнение таблицы данными. В PostgreSQL имеются три общих способа заполнения таблиц данными:

• вставка новых группированных данных командой INSERT INTO;
• вставка существующих данных из другой таблицы командой INSERT INTO в сочетании с командой SELECT;
• вставка данных из внешнего файла командой COPY (или \copy).

Добавление ограничений

Добавление ограничений

После создания таблицы сохраняются некоторые возможности добавления ограничении. В PostgreSQL 7.1.x команда ALTER TABLE с секцией ADD CONSTRAINT позволяет определять для полей существующих таблиц только ограничения внешнего ключа и проверки. Команда создания новых ограничений имеет следующий синтаксис:
ALTER TABLE таблице
ADD CONSTRAINT имя_ограничения определение
Синтаксис определения зависит от типа ограничения. В листинге 4.12 продемонстрирован синтаксис создания ограничения внешнего ключа для таблицы editions (связанной с полем id таблицы books) и ограничения проверки для поля type.

Другие возможности SQL

Другие возможности SQL

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

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

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

Команда COPY также позволяет выполнять операции ввода и вывода с данными в двоичном формате. Если команда COPY FROM содержит ключевое слово BINARY, входной файл должен быть создан командой COPY TO в двоичном формате PostgreSQL. Двоичные файлы загружаются быстрее ASCII-файлов, но в отличие от последних их нельзя читать и редактировать в простых текстовых редакторах.
В листинге 4.21 приведена команда COPY, предназначенная для вставки записей из двоичного файла в таблицу subjects базы данных booktown.

Группировка записей

Группировка записей

Секция GROUP BY представляет чрезвычайно мощную концепцию SQL — агрегирование. На практике агрегирование запросов SQL приводит к тому, что все записи с одинаковыми значениями выражения, заданного в секции GROUP BY, группируются в одну агрегатную запись. Выражение GROUP BY может быть простым полем таблицы, но оно также может представлять собой произвольную операцию с полем. При перечислении нескольких полей или выражений, разделенных запятыми, группировка записей осуществляется лишь при совпадении записей по всем критериям.
Чтобы эффективно пользоваться агрегированием, необходимо понимать, что все целевые поля, участвующие в агрегирующем запросе, но не указанные в секции GROUP BY, доступны лишь при выборке через агрегатную функцию. Агрегатная функция получает имя поля (или выражение, в котором участвует хотя бы одно имя поля), представляющее несколько значений (например, в нескольких сгруппированных записях), выполняет с ними некоторую операцию и возвращает одно значение.
Самые распространенные агрегатные функции:

• count () — возвращает количество записей в наборе;
• тах () — возвращает максимальное значение в наборе;
• min () — возвращает минимальное значение в наборе.

Агрегатные функции работают только с записями итогового набора, поэтому они выполняются после обработки всех условных объединений и секций WHERE.
Предположим, вы хотите вывести количество книг, хранящихся в базе данных booktown для каждого издательства. Название издательства связывается с названиями опубликованных книг простым объединением таблиц editions и publishers, однако подсчитывать записи вручную весьма утомительно, а при очень больших объемах итоговых наборов — вообще неприемлемо.
В листинге 4.40 выполняется стандартное объединение двух таблиц базы данных booktown, но в нем присутствуют два новых элемента: вызов функции count () и секция GROUP BY.

Использование таблиц

Использование таблиц

Таблицы являются основными блоками хранения данных в базе. Перед любыми операциями создания, выборки или модификации записей необходимо сначала создать таблицу, в которой эти записи будут храниться.
В этом разделе рассматриваются процедуры создания, модификации и удаления таблиц командами CREATE TABLE, ALTER TABLE n DROP TABLE SQL (вопросам создания баз данных посвящена глава 9).

Конструкции CASE

Конструкции CASE

Чтобы программа SQL могла принимать простейшие решения, не прибегая к процедурным языкам, в PostgreSQL поддерживаются конструкции CASE, предусмотренные стандартом SQL Ключевые слова SQL CASE, WHEN, THEN и END позволяют выполнять простые условные преобразования записей.
Вся конструкция CASE включается в целевой список команды SELECT. По умолчанию итоговому полю конструкции CASE присваивается имя case, но ему можно назначить синоним, как любому обычному полю. Общий синтаксис конструкции CASE в списке целей команды SELECT выглядит следующим образом:
CASE WHEN условие! THEN результат! WHEN условие2 THEN результат2
[ ... ]
[ ELSE результат_по_умопчанию END [ AS синоним ]
Конструкция CASE-WHEN-THEN-ELSE отчасти напоминает условные команды f-then-else в традиционных языках программирования (листинг 4.50). Условия секций WHEN должны возвращать логический результат.
Если условие в секции WHEN выполняется, результат соответствующей секции THEN возвращается в поле итогового набора. Если ни одно условие не выполнено, можно задать значение по умолчанию в секции ELSE. Если при отсутствии секции ELSE результат остается неопределенным, возвращается NULL.

Копирование данных из внешних файлов командой COPY

Копирование данных из внешних файлов командой COPY

В PostgreSQL поддерживается и такая полезная возможность, как прямое импортирование данных в таблицу из внешних файлов командой COPY. Файл, содержащий входные данные команды COPY, хранится либо в стандартном текстовом формате ASCII с ограничением полей специальным символом-разделителем, либо в двоичном формате таблиц PostgreSQL. В ASCII-файлах в качестве разделителя обычно используется символ табуляции или запятая. При импортировании данных из ASCII-файла каждая строка файла интерпретируется как отдельная запись данных, а каждый компонент строки — как значение соответствующего поля записи.
Команда COPY FROM работает значительно быстрее обычной команды INSERT, поскольку данные передаются прямо в приемную таблицу за одну транзакцию. С другой стороны, к формату исходного файла предъявляются чрезвычайно жесткие требования, поэтому ошибка всего в одной строке приводит к сбою всей команды COPY.
Синтаксис команды COPY FROM:
COPY [ BINARY ] таблица [ WITH 0IDS ]
FROM { 'имя_файла' \ stdin }
[ [USING] DELIMITERS 'разделитель' ]
[ WITH NULL AS 'строка_nulГ ]
Ниже поясняется смысл параметров команды.

• BINARY. Признак импортирования входных данных из двоичного файла, ранее созданного командой COPY TO.
• таблица. Имя таблицы, в которую импортируются данные.
• WITH OIDS. Из первой строки файла загружаются значения всех идентификаторов OID импортируемой таблицы.
• FROM { 'имя_файла' \ stdin }. Источник, из которого PostgreSQL получает входные данные — файл с заданным именем либо стандартный ввод (stdin).
• [ USING ] DELIMITERS 'разделитель'. Символ, используемый в качестве разделителя при разборе входных данных. Не используется для файлов, выведенных в двоичном формате PostgreSQL.
• WITH NULL AS ' строка_null. Заданная строка должна интерпретироваться как значение NULL. He используется для файлов, выведенных в двоичном формате PostgreSQL.

При подготовке к импортированию файла проследите за тем, чтобы этот файл был доступен для чтения процессом postmaster (то есть пользователем, запустившим PostgreSQL). Кроме того, разрешены только абсолютные имена файлов; при попытке передать относительное имя происходит ошибка.
При работе с входными файлами в формате ASCII в секции DELIMITERS передается символ, используемый в качестве разделителя значений полей в строках файла. Если разделитель не указан, PostgreSQL считает, что значения разделяются символом табуляции. Необязательная секция WITH NULL определяет формат, в котором передаются значения NULL. Если секция отсутствует, PostgreSQL интерпретирует последовательность \N как NULL (например, пустые поля исходного файла по умолчанию интерпретируются как пустые строковые константы, а не как NULL).
Если данные вводятся вручную или передаются терминалу другой программой, в качестве источника в секции FROM можно указать стандартный ввод (stdin). При получении данных из стандартного ввода входной поток должен завершаться последовательностью \. (обратная косая черта плюс точка), за которой немедленно следует символ новой строки.
В листинге 4.19 приведено содержимое файла, выведенного PostgreSQL в формате ASCII. Поля разделяются запятыми, а для представления NULL используется строка \null.B файле сохранены данные из таблицы subjects базы данных booktown.

Назначение системного пути для psql

Листинг 4.1. Назначение системного пути для psql

[user@host user]$ psql
bash: psql: command not found
[user@host user]$ echo $PATH
/b1n:/usr/bin:/usr/local/bin:/usr/bin/Xll:/usr/XHR6/tnn
[user@host user]$ export PATH=$PATH:/usr/local/pgsql/bin
[user@host user]$ psql testdb
Welcome to psql. the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit
testdb=#
После настройки переменной PATH интерактивный терминал PostgreSQL запускается командой psql, за которой следует имя базы данных.
ВНИМАНИЕ
После выхода из psql изменения переменной среды будут потеряны. Чтобы изменения переменной PATH сохранялись после выхода из программы, команда настройки PATH включается в стартовый сценарий командного интерпретатора (например, ~/.bash_profile).

относящийся к интерпретатору

Листинг 4.1, относящийся к интерпретатору bash, иллюстрирует процедуру назначения системного пути для клиента psql.

Переименование таблицы

Листинг 4.10. Переименование таблицы

oktown=# ALTER TABLE books RENAME TO literature;
TER
oktown=# ALTER TABLE literature RENAME TO books;
TER

Переименование поля

Листинг 4.11. Переименование поля

booktown=# \d daily_inventory
Table "daily_inventory"
Attribute | Type | Modifier
isbn | text
in_stock | boolean |
booktown=# ALTER TABLE daily_inventory
booktown-# RENAME COLUMN in_stock TO is_in_stock;
ALTER
booktown=# ALTER TABLE daily_inventory
booktown-l RENAME COLUMN is_in_stock TO is_stocked;
ALTER

Создание новых ограничений

Листинг 4.12. Создание новых ограничений в существующей таблице

booktown=# ALTER TABLE editions
booktown-# ADD CONSTRAINT foreign_book
booktown-# FOREIGN KEY (book_id) REFERENCES books (id);

NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)
for FOREIGN KEY check(s)
CREATE
booktown=# ALTER TABLE editions
booktown-# ADD CONSTRAINT hard_or_paper_back
booktown-# CHECK (type = 'p' OR type = 'h'):
ALTER
Установка ограничения внешнего ключа приводит к тому, что любое значение book_i d в таблице edi ti ons также должно существовать в таблице books. Кроме того, вследствие установленного ограничения проверки поле type в таблице editions может содержать только значения р или Ь.
Примечание 2
Примечание 2


Ограничение уникальности также неявно устанавливается при создании уникального индекса командой CREATE INDEX (см. раздел «Индексы» в главе 7).
Дополнительная информация об ограничениях, их назначении и синтаксисе приведена в главе 7.

Смена владельца таблицы

Листинг 4.13. Смена владельца таблицы

booktown=# ALTER TABLE employees booktown-# OWNER TO corwin;
ALTER
Примечание 3
Примечание 3


Смена владельца таблицы может осуществляться либо текущим владельцем, либо суперпользователем PostgreSQL.

Реструктуризация

Листинг 4.14. Реструктуризация таблицы командой CREATE TABLE AS

booktown=# \d books
Table "books" Attribute | Type | Modifier
id | integer | not null
title text I not null
authorjd | integer |
subjectjd integer j
publication j date | Index: books_id_pkey
booktown=# CREATE TABLE new_books
booktown-# (id, title. authorjd, subjectjd)
booktown-# AS SELECT id, title, authorjd, subjectjd
booktown-f FROM books;
SELECT
booktown=# ALTER TABLE books RENAME TO old_books;
ALTER
booktown=# ALTER TABLE new_books RENAME TO books;
ALTER
booktown=# \d books
Table "books" Attribute | Type | Modifier
id | integer |
title | text |
authorjd j integer j
subjectjd | integer |
booktown=# DROP TABLE books;
DROP
ВНИМАНИЕ
В PostgreSQL 7.1.x присутствие в команде SELECT необязательного списка полей, заключенного в круглые скобки, исключает использование специального символа *. Недостаток должен быть исправлен в PostgreSQL 7.2.

Реструктуризация

Листинг 4.15. Реструктуризация таблицы командами CREATE TABLE и INSERT INTO

booktownHf CREATE TABLE new_books (
booktown(# id integer UNIQUE,
booktown(# title text NOT NULL.
booktown(# authorjd integer.
booktown(# subjectjd integer.
booktown(# CONSTRAINT books_id_pkey PRIMARY КЕУ (id)
booktown(# ):
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index ' books Jd_pkey'
for table 'new_books'
CREATE
booktown=# INSERT INTO new_books
booktown-# SELECT id, title, author_id, subjectjd
booktown-f FROM books;
INSERT 0 12
booktown=# ALTER TABLE books RENAME TO old_books;
ALTER
booktown=# ALTER TABLE new_books RENAME TO books:
ALTER
booktown=# \d books
Table "books" Attribute | Type | Modifier
id | integer | not null
title ( text I not null
authorjd integer j
subjectjd integer | Index: booksjd_pkey
За дополнительной информацией об использовании команды INSERT INTO с командой SELECT обращайтесь к подразделу «Вставка данных из других таблиц командой SELECT» раздела «Добавление данных командами INSERT и COPY». Команда SELECT рассматривается в разделе «Выборка данных командой SELECT».

Вставка новой записи в таблицу books

Листинг 4.16. Вставка новой записи в таблицу books

booktown=# INSERT INTO books (Id, title, author_id, subject_id)
booktown-# VALUES (41472, 'Practical PostgreSQL', 1212, 4);

INSERT 3574037 1
Команда SQL, приведенная в листинге 4.16, вставляет новую запись с кодом (id) 41472, названием «Practical PostgreSQL», кодом автора 1212 и кодом темы 4. Обратите внимание на завершающее сообщение, начинающееся со слова INSERT, — оно указывает на то, что операция вставки была выполнена успешно. Первое число после INSERT является идентификатором объекта (OID) созданной записи, а второе число обозначает количество созданных записей (в нашем примере,— 1).
В приведенном примере необязательный список полей совпадает с порядком следования полей в структуре таблицы (слева направо). В данном случае этот список можно опустить, поскольку команда INSERT предполагает, что значения присваиваются в естественном порядке следования полей таблицы. Поля в списке можно переставить, но в этом случае порядок значений в секции VALUES тоже должен измениться, как показано в листинге 4.17.

Изменение порядка перечисления полей

Листинг 4.17. Изменение порядка перечисления полей

booktown=# INSERT INTO books (subjected, author_id, id, title)
booktown-# VALUES (4, 7805, 41473, 'Programming Python');

INSERT 3574041 1

Вставка данных из другой таблицы

Листинг 4.18. Вставка данных из другой таблицы

booktown-# INSERT INTO books (id, title, author_id, subject_id)
booktown-# SELECT nextval('book_ids', title, author_id, subject_id
booktown-# FROM book_queue WHERE approved;
INSERT 0 2
В приведенном примере запрос SELECT, включенный в команду INSERT INTO, переносит две записи из таблицы book_queue в таблицу books. В этом контексте допускается использование любой синтаксически правильной команды SELECT. В нашем примере в выборку включается результат вызова функции nextval () для последовательности bookj ds, за которым следуют значения полей title, author_id и subject_id из таблицы book_queue.
На этот раз команда создает сразу несколько новых записей, поэтому в сообщении об успешном выполнении операции вместо значения OID, которое выводилось бы при вставке одной записи, выводится 0. Второе число, как и в случае с обычной командой INSERT INTO, равно количеству созданных записей (в данном случае — 2).

Пример копируемого ASCIIфайла

Листинг 4.19. Пример копируемого ASCII-файла

1.Business.Productivity Ave
2.Children's Books,Kids Ct
3.Classics.Academic Rd
4,Computers,Productivity Ave
5,Cooking.Creativity St
12.Religion.\null
8.Hi story.Academic Rd
9.Horror.Black Raven Dr
10.Mystery.Black Raven Dr
11.Poetry.Sunset Dr
13.Romance.Main St
14.Science.Productivity Ave
15.Science Fiction.Main St
0.Arts.Creativity St
6.Drama.Main St
7.Entertainment.Main St
Следующая команда (листинг 4.20) импортирует содержимое файла /tmp/ subjects.sql в таблицу subjects базы данных booktown.

Получение списка команд psql booktown=# \?

Листинг 4.2. Получение списка команд psql booktown=# \?

\а toggle between unaligned and aligned mode
\c[onnect] [dbname|- [user]]
connect to new database (currently 'booktown')
\C