Операторы и функции

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

В PostgreSQL поддерживаются стандартные операторы и функции SQL, определенные в стандартах ANSI/ISO, — математические операторы, основные функции форматирования текста, выделение компонентов даты/времени и т. д. Кроме того, в PostgreSQL реализованы многочисленные нестандартные расширения, в том числе операторы поиска по регулярным выражениям и универсальная функция форматирования данных to_char().

Ниже описаны встроенные операторы и функции. Одной из замечательных особенностей языка PostgreSQL является возможность его расширения в этой области. Когда вы освоите стандартные операторы и функции, вероятно, вам захочется определить свои собственные. Эта тема рассматривается в главе 7.

Операторы

В главе 3 операторы SQL определялись как лексемы, предназначенные для выполнения операций с константами и идентификаторами и возвращающие результаты этих операций. Некоторые ключевые слова SQL тоже считаются операторами из-за воздействия на данные в командах SQL. В этом разделе термином «операторы» будут обозначаться как специальные символы, так и ключевые слова. Действия, выполняемые оператором, зависят от контекста его применения. Область применения операторов чрезвычайно широка, от выполнения математических операций и конкатенации строк до разнообразных сравнений. В следующем подразделе описываются общие правила применения операторов SQL, а далее подробно рассматриваются некоторые важнейшие категории операторов:

строковые операторы; числовые операторы; логические операторы.

ПРИМЕЧАНИЕ

Самый «свежий» и наиболее полный список операторов PostgreSQL выводится в psql командой \do. Помните, что многие из перечисленных операторов существуют только в PostgreSQL и могут не поддерживаться в других СУБД на базе SQL.

После описания различных категорий операторов приводится информация о роли псевдозначений NULL в выражениях и о порядке выполнения операторов.

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

Оператор работает с одним операндом или с двумя операндами. Большинство операторов работает с двумя операндами, между которыми ставится сам оператор (например, а-b). Такие операторы называются бинарными. Операторы, работающие с одним операндом, называются унарными; в этом случае оператор либо предшествует операнду, либо следует за ним (например, знак @ перед значением является унарным оператором, возвращающим модуль числа).

Некоторые операторы имеют несколько интерпретаций в зависимости от типа данных, к которым они применяются. С другой стороны, часть операторов не имеет смысла для некоторых типов данных (дополнительная информация о типах данных PostgreSQL приведена в главе 3).

Например, оператор сложения (+) может использоваться для суммирования двух целочисленных величии, но он не может прибавить целое число к текстовой строке. Считается, что такой вариант использования оператора дает неопределенный результат и потому является недопустимым. При этом сам оператор (в данном случае +) опознается нормально, но PostgreSQL выводит сообщение об ошибке (вроде того, что приведено в листинге 5.2).

Для примера рассмотрим таблицу authors, связывающую имена авторов с числовыми кодами.

Таблица "authors"

Атрибут | Тип | Модификатор

id | integer not null

lastjiame j text firstjname 1 text

Индекс: authors_pkey

Код автора id относится к типу integer (4-байтовое целое), а фамилия автора — к типу text. Поскольку поле id относится к числовому типу, оно может использоваться в качестве операнда математического оператора с другим числовым значением.

В листинге 5.1 приведен пример правильного использования оператора сложения (+).

Листинг 5.1. Правильное использование математического оператора

booktown=# SELECT id 1 AS id_p1us_one. 1ast_name

booktown-# FROM authors

booktown-# ORDER BY id DESC LIMIT 5;

id_plus_one j last_name

25042 Bianco 15991 | Bourgeois

7807 | Christiansen

7806 | Lutz

4157 I King

(5 rows)

Попытка сложения несовместимых типов продемонстрирована в листинге 5.2.

Листинг 5.2. Неверное использование операторов

booktown=# SELECT id + lastjiame AS mistake

booktown-# FROM authors:

ERROR: Unable to identify an operator '+' for types 'int4' and 'text' You will have to retype this query using an explicit cast

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

Строковые операторы

В PostgreSQL существует обширный набор строковых операторов для выполнения различных действий, от простой конкатенации текста и сравнения строк до поиска по регулярным выражениям. Строковые операторы работают с типами char, varchar, а также с собственным типом PostgreSQL text.

Ниже описаны основные операторы сравнения и конкатенации, а также реализация операторов регулярных выражений с учетом и без учета регистра символов.

Базовые операторы сравнения

Базовые операторы сравнения строк и конкатенации, поддерживаемые в PostgreSQL, перечислены в табл. 5.1.

ПРИМЕЧАНИЕ

Ключевые слова LIKE и ILIKE, приводящие к вызову функции 11ke(), иногда называются операторами сравнения строк. Эти ключевые слова рассматриваются ниже в разделе «Функции».

Таблица 5.1. Базовые строковые операторы

Оператор	Синтаксис	Описание
=	'строка!' = ' строка2'	Возвращает true, если первая строка точно совпадает со второй
! =	'строка!' != ' строка2'	Возвращает true, если первая строка не совпадает со второй
о	'строка!' <> ' строка2'	Идентичен оператору !=
<	'строка!' < ' строка2'	Возвращает true, если при лексикографической сортировке первая строка предшествует второй
<=	'строка!' <= ' строка2'	Возвращает true, если при лексикографической сортировке первая строка предшествует второй или их значения совпадают
>	'строка!' > ' строка2'	Возвращает true, если при лексикографической сортировке вторая строка предшествует первой
>=	'строка!' >= ' строка2'	Возвращает true, если при лексикографической сортировке вторая строка предшествует первой или их значения совпадают

Все операторы сравнения строк возвращают логическое значение (true или false). Лексикографическая сортировка, упоминаемая в табл. 5.1, последовательно сравнивает символы строк и определяет, какой из символов «больше» другого. Если начальные символы двух строк совпадают, проверяются следующие символы (слева направо). Перебор продолжается до тех пор, пока не будут найдены два различающихся символа. В этом алгоритме сортировки «больший» символ выбирается сравнением ASCII-кодов, как показывает следующий пример:

booktown=# SELECT letter, ascii(letter)

booktown-# FROM text_sorting

booktown-# ORDER BY letter ASC;

letter ascii

0 48

1 49

2 50

3 51

A 65

В 66

С 67

D 68

a 97

b 98

с 99

d I 100

(12 rows)

Если вы не уверены в порядке сортировки того или иного символа, воспользуйтесь функцией ascii О для получения его ASCII-кода (функция asci i () описана ниже в разделе «Функции»). В листинге 5.3 из таблицы books выбираются названия всех книг, первая буква которых при сортировке предшествует символу «D».

Листинг 5.3. Сравнение строк

booktown=# SELECT title FROM books

booktown=# WHERE substrCtitle, 1, 1) < 'D1;

title

2001: A Space Odyssey

Bartholomew and the Oobleck

(2 rows)

Конкатенация

Оператор конкатенации (11) играет очень важную роль при форматировании выходных данных. Как и все операторы, он может использоваться в командах SQL всюду, где могут использоваться константы. Допускается последовательная конкатенация строковых значений, для этого перечисляемые строковые константы или идентификаторы разделяются операторами 11.

Например, оператор конкатенации может использоваться в секции WHERE для фильтрации записей на основании сравнения с символьной строкой. Пример приведен в листинге 5.4.

Листинг 5.4. Конкатенация строк

booktown=# SELECT 'The Title: ' | title || ', by ' |

booktown-# first_name || ' ' II lastjiame AS bookjinfo

booktown-# FROM books NATURAL JOIN authors AS a (authorjd) LIMIT 3;

book_info

The Title: The Shining, by Stephen King

The Title: Dune, by Frank Herbert

The Title: 2001: A Space Odyssey, by Arthur C. Clarke

(3 rows)

Операторы регулярных выражений

Если обычного сравнения строк оказывается недостаточно, в PostgreSQL предусмотрены специальные операторы для поиска совпадений по регулярным выражениям. Регулярное выражение похоже на строковую константу, с которой сравнивается исходная строка, но некоторые символы (квадратные скобки, вертикальная черта, обратная косая черта и т. д.) при сравнении интерпретируются особым образом. Если вам доводилось работать с такими программами Unix, как sed, grep или perl, возможно, вы уже знакомы с синтаксисом регулярных выражений.

ПРИМЕЧАНИЕ

Общие сведения о регулярных выражениях приведены в книге Джеффри Фридла (Jeffrey Friedl) «Mastering Regular Expressions».

Регулярное выражение состоит из литералов и метасимволов (специальных символов). Обычно регулярные выражения могут совпадать с несколькими вариантами строковых значений. Пример приведен в листинге 5.5, где в таблице authors ищутся все имена (fi rstjiame), начинающиеся с символа А или Т.

Листинг 5.5. Поиск по регулярному выражению

booktown=# SELECT first_name, last_name

booktown-# FROM authors

booktown-# WHERE firstjiame - "AT1;

fi rstjiame lastjiame

Ariel Denham Tom Christiansen

Arthur C. | Clarke Andrew Brookins

Theodor Seuss j Geisel (5 rows)

Символ ~ в секции WHERE является оператором регулярного выражения, а само регулярное выражение задается строкой ЛА| ЛТ. В него входят специальные символы (метасимволы) л и |, а также литералы А и Т. Метасимволы регулярных выражений описаны ниже в этом пункте.

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

Операторы регулярных выражений перечислены в табл. 5.2. Их операндами всегда являются строка с регулярным выражением и текст, в котором ищется совпадение (идентификатор или константа). Все операторы регулярных выражении

возвращают результат логического типа, интерпретация которого зависит от специфики самого оператора.

Таблица 5.2. Операторы регулярных выражений

Оператор	Синтаксис	Описание
-	' строка' ~ ' выражение'	Возвращает true, если в строке существует совпадение для регулярного выражения
i ~	'строка' \- 'выражение'	Возвращает true, если в строке нет совпадения для регулярного выражения
~*	' строка' ~* ' выражение'	Возвращает true, если в строке существует совпадение для регулярного выражения (без учета регистра символов)
|_*	'строка' !-* 'выражение'	Возвращает true, если в строке нет совпадения для регулярного выражения (без учета регистра символов)

В табл. 5.3 перечислены метасимволы регулярных выражений. Как было указано выше, в регулярных выражениях эти символы интерпретируются особым образом.

Таблица 5.3. Метасимволы регулярных выражений

Символ	Синтаксис	Описание
-	^выражение	Соответствует началу строки
$	выражение$	Соответствует концу строки
.	,	Соответствует одному символу
[ ]	[abc]	Соответствует любому из символов, перечисленных в квадратных скобках
Г]	ГаЬс]	Соответствует любому символу кроме символов, перечисленных в квадратных скобках
[-]	[a-z]	Соответствует любому символу из интервала, заданного в квадратных скобках
Г-]	Га-z]	Соответствует любому символу кроме символов из интервала, заданного в квадратных скобках
7	а?	Соответствует нулю экземпляров или одному экземпляру предшествующего символа или подвыражения
*	а*	Соответствует нулю экземпляров или произвольному количеству экземпляров предшествующего символа или подвыражения
+	а+	Соответствует одному и более экземпляров предшествующего символа или подвыражения
1	выражение! \ выражение2	Соответствует левому или правому подвыражению
( )	(выражение!) выражение2	Группировка подвыражений с явным определением приоритета выполняемых операций

ПРИМЕЧАНИЕ

Литералы, совпадающие с метасимволами из табл. 5.3, должны экранироваться в строке двумя символами \ (например, знак $ представляется последовательностью \\$).

Регулярные выражения часто применяются для поиска подстрок в строках-литералах большего размера. При поиске соответствия с учетом регистра символов используется оператор -; с оператором -* регистр игнорируется. Примеры приведены в листинге 5.6.

Листинг 5.6. Простой поиск соответствия

booktown=# SELECT title FROM books

booktown-# WHERE title - 'The';

title

The Shining The Cat in the Hat The Velveteen Rabbit

The Tell-Tale Heart

(4 rows)

booktown=# SELECT title FROM books

booktown-f WHERE title -* 'The'; title

The Shining The Cat in the Hat Bartholomew and

the Oobleck Franklin in the Dark The Velveteen Rabbit

The Tell-Tale Heart

(6 rows)

Как видно из результатов, оператор ~* возвращает две дополнительные записи, поскольку выражение соответствует не только подстроке «the», но и любым модификациям в регистре символов этой подстроки (the, tHe, ThE и т. д.).

Если добавить в начало этого регулярного выражения символ л, оно будет соответствовать только подстроке «The», находящейся в начале исходного текста (листинг 5.7).

Конструкция . * обозначает любое количество произвольных символов до следующего подвыражения. В данном примере этим подвыражением является пара строк в круглых скобках (rabbit и heart), разделенных символом |, что соответствует любой из перечисленных строк.

Листинг 5.7. Нетривиальный поиск соответствия

booktown=f SELECT title FROM books

booktown-# WHERE title -* IAThe.*(rabbit|heart)';

title

The Velveteen Rabbit The Tell-Tale Heart

(2 rows)

Выражение AThe.*(rabbit|heart) означает следующее: соответствие находится лишь в том случае, если строка начинается с символов «The», далее идет любое количество произвольных символов, а после них следует либо подстрока «rabbit», либо подстрока «heart». Оператор ~* (вместо оператора ~) производит сравнение без учета регистра символов.

В листинге 5.8 приведен пример еще более сложного регулярного выражения.

Листинг 5.8. Сложный поиск соответствия

booktown=# SELECT title FROM books

booktown-# WHERE title ~* 'rt.*[ri]t) | (ingJjune$)' :

title

The Shining Dune

The Velveteen Rabbit The Tell-Tale Heart

(4 rows)

booktown=#

На первый взгляд регулярное выражение в листинге 5.8 выглядит устрашающе. Но если разбить его на элементы, становится видно, что оно состоит из двух выражений в круглых скобках, разделенных символом [.Таким образом, соответствие будет найдено, если хотя бы одно из этих выражений указывает на значение в поле title.

Продолжая анализ выражения, мы видим, что подвыражение слева от символа | состоит из следующих элементов (слева направо): метасимвол л, за которым следует символ t, затем последовательность .* и пара квадратных скобок с символами г и i, за которыми следует символ t. В переводе на русский язык это означает, что соответствие должно начинаться с буквы t, за которой следует ноль или более символов, пока не будет встречена буква г или 1, после которой немедленно следует буква t. Если хотя бы одно из этих условий не соблюдается, соответствия нет.

Выражение в правой части устроено проще. Оно состоит из двух литералов ing и une, разделенных очередным символом , причем за каждым литералом следует символ $. В переводе это означает, что совпадение должно заканчиваться подстрокой Ing или une. При выполнении любого (благодаря символу |) из этих условий соответствие считается найденным.

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

Числовые операторы PostgreSQL делятся на три категории.

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

Ниже приведены более подробные описания по всем трем категориям.

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

Математические операторы используются в целевых списках, в секции WHERE команды SELECT и вообще везде, где встречаются числовые данные.

В табл. 5.4 перечислены математические операторы PostgreSQL и приведены примеры их использования.

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

Оператор	Синтаксис	Описание
+	а + b	Суммирование числовых величин а и b
-	а - Ь	Вычитание числовой величины b из а
*	а * b	Умножение числовых величин а и b
/	а / b	Деление числовой величины а на b
%	а % b	Остаток от деления а на b
А	а - b	Возведение а в степень b
|/	!/ а	Квадратный корень из а
II/	||/ а	Кубический корень из b
1	а!	Факториал а
! 1	!! а	Факториал а (отличается от постфиксного оператора только расположением)
@	@ а	Модуль (абсолютное значение) а

Пример использования математических операторов в целевом списке приведен в листинге 5.9. Оператор / используется для вычисления удельной прибыли по каждой книги. Частное от деления преобразуется к типу numeri с с усечением до двух цифр в дробной части. Наконец, из результата вычитается целочисленная константа 1, чтобы результат выражался в процентах свыше 100.

Листинг 5.9. Использование математических операторов

booktown=# SELECT isbn,

booktown-# (retail / cost)::numeric(3, 2) - 1 AS margin

booktown-# FROM stock

booktowri-# ORDER BY margin DESC

booktown-# LIMIT 4;

isbn | margin

0451457994 | 0.35

0760720002 | 0.33

0451198492 0.30

0441172717 | 0.29

(4 rows)

Обратите внимание на определение временного псевдонима margin при помощи ключевого слова AS. Псевдоним представляет собой временное имя, которое существует только во время обработки запроса.

Операторы сравнения

Операторы сравнения работают со значениями таких типов, как integer или text, но всегда возвращают результат типа boo"! ean. Они часто встречаются в секции WHERE, но могут использоваться в любом контексте, в котором действителен тип boolean. Операторы сравнения PostgreSQL перечислены в табл. 5.5.

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

Оператор	Описание
<	Возвращает true, если левое значение меньше правого
>	Возвращает true, если левое значение больше правого
<=	Возвращает true, если левое значение меньше правого или равно ему
>=	Возвращает true, если левое значение больше правого или равно ему
=	Возвращает true, если левое значение равно правому
<> или ! =	Возвращает true, если левое значение не равно правому

ПРИМЕЧАНИЕ

Оператор <> существует как синоним оператора != для обеспечения совместимости с другими реализациями СУБД на базе SQL. Работают эти операторы одинаково.

Пример команды, в которой используются операторы сравнения, приведен в листинге 5.10.

Листинг 5.10. Использование операторов сравнения

booktown=# SELECT isbn, stock booktown-# FROM stock

booktown-# WHERE retail <= 25

booktown-# AND stock != 0:

isbn | stock

0441172717 | 77

0590445065 | 10

0679803335 | 18

0760720002 | 28

09296C5942 | 25

1885418035 | 77

(6 rows)

Сравнение с использованием ключевых слов

Ключевое слово BETWEEN (также иногда называемое оператором) позволяет проверить, входит ли значение в некоторый интервал. Например, команда SELECT, приведенная в листинге 5.11, находит книги, цена которых находится в интервале от 10 до 17 долларов.

Листинг 5.11. Ключевое слово BETWEEN

booktown=# SELECT isbn FROM stock

booktown-# WHERE cost BETWEEN 10 AND 17;

isbn

0394800753 0441172717 0451457994

(3 rows)

Аналогичного результата можно добиться и при помощи оператора <= в сочетании с оператором >= (листинг 5.12).

Листинг 5.12. Имитация ключевого слова BETWEEN при помощи операторов

booktown=# SELECT isbn FROM stock

booktown-# WHERE cost >= 10 AND cost <= 17; isbn

0394800753 0441172717 0451457994

(3 rows)

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

Двоичные операторы

Двоичные операторы выполняют поразрядные операции с битовыми последовательностями или целыми числами, что обычно приводит к изменению их значений. Двоичные операторы PostgreSQL перечислены в табл. 5.6.

Таблица 5.6. Двоичные операторы

Оператор	Синтаксис	Описание
&	а & b	Поразрядная конъюнкция двоичных представлений а и b (которые могут быть заданы в виде целых чисел)
1	а | b	Поразрядная дизъюнкция двоичных представлений а и b (которые могут быть заданы в виде целых чисел)
f	а # b	Поразрядная операция исключающей дизъюнкции двоичных представлений а и b (которые могут быть заданы в виде целых чисел)
-	- b	Поразрядное отрицание, возвращает инвертированную битовую последовательность b
«	b « n	Сдвиг b влево на n разрядов
»	b » n	Сдвиг b вправо на n разрядов

В листинге 5.13 приведен пример сдвига числа и его двоичного представления на два разряда вправо оператором ». Кроме того, в нем используется функция преобразования битовой последовательности в целочисленный тип bitten nt4(), описанная в разделе «Функции».

Листинг 5.13. Сдвиг битовых последовательностей

booktown=# SELECT b'1000' » 2 AS "8 shifted right".

booktown-# Mttoint4(b'1000' » 2) AS integer.

booktown-# 8 » 2 AS likewise;

8 shifted right | integer | likewise

0010 I 2 I 2

(1 row)

ПРИМЕЧАНИЕ

При сдвиге битовых последовательностей исходная длина строки не изменяется, а разряды, выходящие за левый или правый край последовательности, отсекаются. При использовании операторов &, | или # битовые операнды должны иметь одинаковую длину.

Логические операторы

Ключевые слова AND, OR и NOT являются логическими (булевыми) операторами PostgreSQL. Обычно они используются для операций с логическими условиями в командах SQL, особенно в секциях WHERE и HAVING.

В табл. 5.7 приведена сводка результатов, возвращаемых логическими операторами AND, OR и NOT для всех возможных сочетаний операндов (true, false и NULL).

Таблица 5.7. Операторы AND, OR и NOT

а	b	aANDb	aORb	NOT a	NOTb
true	true	true	true	false	false
true	false	false	true	false	true
true	NULL	NULL	true	false	NULL
false	false	false	false	true	true
false	NULL	false	NULL	true	NULL
NULL	NULL	NULL	NULL	NULL	NULL

В листинге 5.14 приведена пара запросов с ключевыми словами OR и AND, объединяющими условия выборки записей. Первый запрос возвращает информацию о книгах, которые стоят больше 30 долларов или отсутствуют на складе. Как видно из итогового набора, для возвращения записи должно выполняться одно или оба условия.

Во втором запросе те же условия объединяются ключевым словом AND. В результате записи проходят более жесткий отбор, поскольку должны выполняться сразу оба критерия. Итоговый набор состоит всего из одной записи для книги, которая стоит дороже 30 долларов и отсутствует на складе.

Листинг 5.14. Объединение условий с использованием логических операторов

booktown=# SELECT isbn, cost, stock

booktown-# FROM stock

booktown-# WHERE cost > 30

booktown-# OR stock = 0:

Isbn cost | stock

0394900014 23.00 | 0

044100590X | 36.00 | 89

0451198492 | 36.00 | 0

0451457994 17.00 j 0 (4 rows)

booktown=# SELECT Isbn, cost, stock

booktown-# FROM stock

booktown-# WHERE cost > 30

booktown-# AND stock = 0;

Isbn | cost stock

0451198492 36.00 0

(1 row)

Операторы и NULL

Если таблица содержит значения NULL, можно воспользоваться специальными операторами сравнения, чтобы учесть поля NULL при выборке или игнорировать их. Конструкция IS NULL проверяет, содержит ли поле значение NULL. Обратное условие проверяется конструкцией NOT IS NULL.

В листинге 5.15 конструкция IS NULL используется для поиска авторов, у которых отсутствуют данные в поле f 1 rst_name.

Листинг 5.15. Проверка с использованием конструкции IS NULL

booktown=# SELECT lastjiame, firstjiame

booktown-l FROM authors

booktown-* WHERE firstjiame IS NULL;

lastjiame fi rstjname

Geisel

(1 row)

При сравнении команд в листингах 5.15 и 5.16 может показаться, что эти два варианта синтаксиса идентичны. Тем не менее между ними существует принципиальное различие.

Листинг 5.16. Сравнение со значением NULL

booktown=# SELECT lastjiame, firstjiame

booktown-l FROM authors

booktown-f WHERE firstjiame = NULL:

lastjiame | fi rstjname

Geisel |

(1 row)

В PostgreSQL условие = NULL преобразуется в IS NULL, а условие != NULL преобразуется в IS NOT NULL. Это сделано только для совместимости с существующими клиентскими приложениями (например, Microsoft Access).

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

Все остальные операторы сравнения для операнда NULL возвращают NULL, поскольку NULL никогда не бывает больше или меньше другой величины, отличной от NULL (листинг 5.17). NULL можно рассматривать как своего рода «черную дыру» в мире SQL — для этого псевдозначения ни один оператор сравнения (кроме IS NULL и специального оператора преобразования =) не возвращает true; NULL не может участвовать в операциях сложения, конкатенации и т. д.

Листинг 5.17. Операторы и псевдозначение NULL

booktown=# \pset null *nu11* Null display is

booktown=# SELECT 5 > NULL;

?column?

*null*

(1 row)

booktown=# SELECT NULL IS NULL;

? column?

t (1 row)

booktown=# SELECT NULL || 'Test'; ?column?

*null*

(1 row)

Приоритет операторов

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

Листинг 5.18. Приоритет операторов

booktown=# SELECT 60 + 12 * 5

AS "sixty plus twelve times five",

booktown-# 12 + 60 * 5

AS "twelve plus sixty times five";

sixty plus twelve times five |

twelve plus sixty times five

120 | 312

(1 row)

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

В табл. 5.8 перечислены группы операторов PostgreSQL в порядке убывания их приоритета.

Таблица 5.8. Приоритет операторов SQL

Оператор	Синтаксис	Описание
	значение: : тип	Явное преобразование типа
[ ]	значение^ индекс]	Индексация элемента массива
	таблица, поле	Разделитель имен таблицы и столбца
-	-значение	Унарный минус
А	основание * степень	Возведение в степень
* / %	значение! * значение2	Умножение, деление и остаток
+ -	значение! + значение2	Сложение и вычитание
IS	значение IS признак	Сравнение с true или false
IS NULL	значение IS NULL	Сравнение с NULL
IS NOT NULL	значение IS NOT NULL	Проверка несовпадения с NULL
Прочее	—	Все остальные пользовательские и встроенные операторы, не входящие ни в одну из категорий
IN	значение IN набор	Проверка принадлежности к заданному набору
BETWEEN	значение BETWEEN a AND b	Проверка принадлежности к интервалу [а. Ь]
LIKE, ILIKE	строка LIKE, шаблон	Проверка совпадения шаблона со строкой
<><=>=	значение! < значение2	Сравнения по критериям «меньше», «больше», «меньше либо равно», «больше либо равно»
=	значение! = значение2	Проверка равенства
NOT	NOT значение	Логическое отрицание
AND	значение! AND значение2	Логическая конъюнкция
OR	значение! OR значение2	Логическая дизъюнкция

ПРИМЕЧАНИЕ

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

Функции

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

Использование функций

При вызове функции в команде SQL указывается имя функции, после которого в круглых скобках перечисляются аргументы. Существует два основных стиля передачи аргументов. Стандартные функции SQL92 обычно реализуются так, что их аргументы разделяются специальными ключевыми словами SQL (такими, как FROM, FOR и USING). С другой стороны, функции в стиле PostgreSQL получают аргументы, разделенные запятыми (вероятно, программисту с опытом программирования на С этот способ покажется более привычным).

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

функция_в_стиле_sq192 ( { аргумент \ КЛЮЧЕВОЕ_СЛОВО } [.:.] )

ipyHKuHz_Bj:THnej)gsql92 ( аргумент [, ...] )

ВНИМАНИЕ

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

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

имя_функции (имя_вложенной_функции (аргументы [. ... ] ) [. ...] )

В PostgreSQL существует множество стандартных функций, работающих со встроенными типами данных. Полный список функций выводится командой \df в клиенте psql. Кроме того, в PostgreSQL поддерживается возможность определения пользовательских функций при помощи команды CREATE FUNCTION. За дополнительной информацией обращайтесь к главе 7.

ПРИМЕЧАНИЕ

По умолчанию полям в целевом списке, значение которых определяется функцией, назначается синоним в виде имени функции без круглых скобок и без аргументов (например, to_char).

Математические функции

Математические функции PostgreSQL выполняют стандартные арифметические и тригонометрические операции и работают с разными типами данных. В большинстве случаев тип возвращаемого значения совпадает с типом аргументов функции. В табл. 5.9 приведена сводка основных математических функций PostgreSQL.

Таблица 5.9. Математические функции PostgreSQL

Функция	Описание
abs(x)	Возвращает модуль (абсолютное значение) х
acos(x)	Возвращает арккосинус х
asin(x)	Возвращает арксинус х
atan(x)	Возвращает арктангенс х
atan2(x.y)	Возвращает арктангенс х/у
cbrt(x)	Возвращает кубический корень х
ceil (x)	Возвращает минимальное целое число, не меньшее х (округление в верхнюю сторону)
cos(x)	Возвращает косинус х
cot(x)	Возвращает котангенс х
degrees(r)	Возвращает количество градусов в г радиан
exp(x)	Возвращает константу е (2,71828...) в степени х
floor(x)	Возвращает максимальное целое число, не большее х (округление в нижнюю сторону)
ln(x)	Возвращает натуральный логарифм х (функция, обратная ехр(х))
log(b.x)	Возвращает логарифм х по основанию b
log(x)	Возвращает десятичный логарифм х
modtx.yj	Возвращает остаток от деления х/у
pi О	Возвращает константу к (3,14159...)
pow(x.y)	Возвращает х в степени у
radians(d)	Возвращает количество радиан в d градусов
ranaomO	Возвращает псевдослучайное число в интервале от 0,0 до 1,0
round(x)	Возвращает число х, округленное до ближайшего целого
sin(x)	Возвращает синус х
sqrt(x)	Возвращает квадратный корень х
tan(x)	Возвращает тангенс х
trunc(x)	Возвращает целую часть х
trunc(x.s)	Возвращает значение х, усеченное до s цифр в дробной части

Ниже приведены более подробные описания всех функций, перечисленных в таблице, с указанием аргументов, типов данных и выполняемых действий. Хотя функция обычно принимает аргументы лишь одного типа, PostgreSQL при необходимости пытается привести переданные аргументы к нужному типу. Если попытка автоматического преобразования типа завершается неудачей, PostgreSQL выводит сообщение об ошибке и вы можете попытаться выполнить явное преобразование. Дополнительная информация о явном преобразовании типов приведена в главе 3.

abs(x)

Синтаксис:

abs(x)

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

Аргумент функции abs() может относиться к любому из числовых типов данных (numeric, bigint, small int, real или double precision). Тип возвращаемого значения совпадает с типом аргумента.

Пример

testdb=# SELECT abs(lOO) AS abs_positive.

testdb-# abs(-lOO) AS absjiegative:

abs_positive | absjiegative

100 100

(1 row)

acos()

Синтаксис:

acos(x)

Функция acos () возвращает арккосинус аргумента х, относящегося к типу doubl e precision и лежащему в интервале допустимых значений косинуса (от -1 до 1). Фактически является обратной по отношению к функции cos(). Результат представляет собой угол (в радианах) в интервале от 0 до я, выраженный типом double precision.

Пример

testdb=# SELECT acos(l). acos(O),

acos(-l). testdb-# acos(cosd)) AS inverse_example:

acos | acos | acos | inverse_example

0 | 1.5707963267949 | 3.14159265358979 |

(1 row)

asin()

Синтаксис:

asln(x)

Функция as1n() возвращает арксинус аргумента х, относящегося к типу double jrecisi on и лежащему в интервале допустимых значений синуса (от -1 до 1). Факти-lecKii является обратной по отношению к функции sin(). Результат представляет со-юй угол (в радианах) в интервале от -я/2 до л/2, выраженный типом doubl e preci s I on.

Пример

testdb=# SELECT asin(l), asin(O), asin(-l),

testdb-# asin(sind)) AS inverse_examp1e;

asin | asin [ asin [ lnverse_example

1.5707963267949 0 | -1.5707963267949 | 1

(1 row)

itan()

Синтаксис:

itan(x)

Функция atari О возвращает арктангенс аргумента х, относящегося к типу doubl e )reci slon. Фактически является обратной по отношению к функции tan(). Результат представляет собой угол (в радианах) в интервале от -л/2 де я/2, выраженный типом double precision.

Пример

testdb=# SELECT atan(l). atan(O). atan(-l),

testdb-# atan(tand)) AS inverse_example;

atan atan | atan | inverse_example

0.785398163397448 | 0 | -0.785398163397448 | 1

(1 row)

itan2()

Синтаксис:

3tari2(x.y)

По аналогии с функцией atan(), функция atan2() возвращает арктангенс, то есть угол (в радианах) в интервале от -я/2 до я/2, выраженный типом doubl e preci si on. Однако в отличие от atan() она получает два аргумента типа double precision вместо одного и возвращает арктангенс величины, полученной делением первого аргумента на второй.

В общем случае вызов функции atan2(x,y) эквивалентен вызову atan(x/y), но при передаче аргумента у=0 функции atan2() не происходит ошибки деления на ноль, как при вызове atan() для величины х/у. Если аргумент у равен нулю, функция atan2() возвращает я/2 для положительных значений х, -я/2 для отрицательных х и 0 для х=0.

Пример

testdb=# SELECT atan2(0, 1). atan2(l, 1),

testdb-# atanCO / 1) AS functionally.

testdb-# atand / 1) AS identical;

atan2 atan2 | functionally identical

0 i 0.785398163397448 | 0 | 0.785398163397448 (1 row)

testdb=# SELECT atan2(l, 0) AS positive_x.

testdb-# atan2(-l, 0) AS negative_x,

testdb-# atan2(0, 0) AS zero_x,

testdb-i pi() / 2 AS pi_over_two:

positive_x | negative_x | zero_x \ pi_over_two

1.5707963267949 | -1.5707963267949 | 0 | 1.5707963267949

(1 row)

cbrt()

Синтаксис:

cbrt()

Функция cbrt () получает один числовой аргумент х типа doubl e preci si on и возвращает кубический корень, также представленный типом double precision. Фактически является обратной по отношению к функции pow(), выполняющей возведение в третью степень.

Пример

testdb=# SELECT pow(2.0. 3) AS "two cubed".

testdb-# cbrt(8.0) AS "eight's cube root";

two cubed | eight's cube root

8 | 2

(1 row)

ceil()

Синтаксис:

ceil(x)

Функция cei 1 () получает один числовой аргумент х, относящийся к любому из числовых типов данных (numeric, bigint, smallint, real или double precision), и округляет его до минимального целого, не меньшего переданной величины. Целые числа остаются без изменений.

Пример

testdb=# SELECT ceil(1.0). ceil(1.1), ceil(1.5);

ceil | ceil ceil

1 | 2 | 2

(1 row)

COS()

Синтаксис:

COSU)

Функция cos О получает один аргумент х типа double precision, представляющий угол в радианах, и возвращает косинус этого угла в виде типа doubl e preci si on.

Пример

testdb=# SELECT cos (pi О) AS cosj>i.

testdb-# cos(O) AS cos_zero:

cos_pi | cos_zero

1 | 1

(1 row)

cot()

Синтаксис:

cot(x)

Функция cot() получает один аргумент х типа double precision, представляющий угол в радианах, и возвращает котангенс этого угла в виде типа doubl e preci s i on. Аргумент должен быть отличным от нуля.

Пример

testdb=# SELECT cot(l). cot(-l):

cot cot

0.642092615934331 | -0.642092615934331

(1 row)

degrees()

Синтаксис:

degrees(r)

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

Пример

testdb=# SELECT degrees(acos(-D) AS half_circle.

testdb-# degrees(pi) * 2) AS ful1_circle:

half_circle | full_circle

180 | 360

(1 row)

exp()

Синтаксис:

exp(x)

Функция получает один аргумент х типа numeri с или doubl e preci si on и возвращает его экспоненту, то есть результат возведения константы е в заданную степень.

Пример

testdb=# SELECT exp(O.O) AS one.

testdb-# exp(l.O) AS e.

testdb-* exp(2.0) AS "e squared":

one I e I e squared

1 | 2.71828182845905 | 7.38905609893065

(1 row)

floor()

Синтаксис:

floor(x)

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

Пример

testdb=# SELECT floor(l.O) AS one.

testdb-# floor(1.1) AS "one point one",

testdb-# floord.8) AS "one point eight";

one one point one one point eight

1 | 1 | 1

(1 row)

ln()

Синтаксис:

ln(x)

Функция ln(x) получает один аргумент х типа numeric или double precision и возвращает его натуральный логарифм. Фактически является обратной по отношению к функции ехр(). Эквивалентна вызову функции log() с передачей основания е.

Пример

testdb=# SELECT In(lO.O) AS naturaljog.

testdb-| log(expd.O). 10.0) AS naturaljog,

testdb-# In(expdO.O)) AS inverse_example:

naturaljog | naturaljog | inverse_example

2.30258509299405 | 2.30258509299404 | 10

(1 row)

log()

Синтаксис:

log(x) log(b.x)

Функция log() получает один или два аргумента типа numeric. Если передан один аргумент, возвращается его десятичный логарифм. Если переданы два аргумента, функция log(b.x) возвращает логарифм х по основанию Ь.

Пример

testdb=# SELECT log(12.0) AS logj.2.

testdb-# logdO. 12.0) AS logj.2.

testdb-# log(3, 12.0) AS "log 12. base 3";

logJ2 logJ2 log 12. base 3

1.07918124604762 | 1.0791812460 | 2.2618595071

(1 row)

mod()

Синтаксис:

mod(x.y)

Функция mocK) получает два аргумента х и у, относящихся к числовым типам numeric, Integer, smallint или bigint. Возвращаемое значение представляет собой остаток от деления х на у.

Пример

testdb=# SELECT mod(5. 5) AS no_remainder.

testdo-# mod(6, 5) AS remainder_one,

mod(19. 5) AS remainder_four;

no_remainder | rema1nder_one | remainder_four

0 | 1 | 4

(1 row)

pi()

Синтаксис:

pi()

Функция pi () вызывается без аргументов и возвращает константу п, примерно равную 3,14159265358978.

Пример

testdb=# SELECT pi() AS "the pi constant":

the pi constant

3.14159265358979

(1 row)

pow()

Синтаксис:

pow(x.y)

Функция pow() получает два аргумента х и у, относящихся к числовым типам numeri с или doubl e preci s i on. Возвращаемое значение представляет собой результат возведения х в степень у. Тип данных результата совпадает с типом переданных аргументов. Следует помнить, что аргументы должны содержать десятичную точку.

Пример

testdb=# SELECT pow(2.0, 3.0) AS "two cubed".

testdb-# pow(2.0, 2.0) AS "two squared".

testdb-# pow(2.0, 1.0) AS "just two";

two cubed | two squared just two

8 | 4 2

(1 row)

radlans()

Синтаксис:

radians(d)

Функция radians () получает аргумент d типа double precision, представляющий угол в градусах, и преобразует его в радианы. Возвращаемое значение относится к типу double precision. Фактически функция radiansO является обратной по отношению к функции degrees ().

Пример

testdb=# SELECT radians(180) AS ha1f_circle.

testdb-# radians(360) AS fu11_circle;

half_circle | full_cnrcle

3.14159265358979 | 6.28318530717959

(1 row)

random ()

Синтаксис:

randon ()

Функция random() вызывается без аргументов и возвращает псевдослучайное число типа double precision в интервале от 0,0 до 1,0. При каждом вызове randonK) генерируется новое значение, даже если функция вызывается несколько раз в одном запросе.

Обычно функция random() используется в сочетании с математическими операторами (например, + и *) для получения псевдослучайных чисел в заданном интервале и последующим вызовом одной из функций округления (roundC), trunc() и т. д.).

Пример

testdb=# SELECT randonK) AS natural random,

testdb-# round(randomO * 9) + I AS one_through_ten.

testdb-# truncC randonK) * 99) + 1 AS one_through_one_hundred:

natural_random | one_through_ten | one_through_one_hundred

0.478887704424042 | 2 | 37

(1 row)

round ()

Синтаксис:

round(x) round(x.s)

Функция roundO вызывается с одним или с двумя аргументами. Первый аргумент х относится к типу numeric или double precision и определяет округляемое число. Второй необязательный аргумент s относится к типу integer и определяет количество цифр в дробной части после округления. Тип возвращаемого значения совпадает с типом первого аргумента.

Если значение аргумента s больше количества цифр в дробной части х, недостающие позиции заполняются нулями.

Пример

testdb=# SELECT round(l.O) AS one,

testdb-# round(l.l) AS "one point one",

testdb | round(1.5) AS "one point five",

testdb-# roundd.8) AS "one point eight";

one | one point one |

one point five | one point eight

1 | 1 | 2 | 2

(1 row)

testdb=# SELECT round(1.4949. 1) AS one_digit_scale.

testdb-# roundd.4949, 3) AS three_digit_scale.

testdb-# roundd.4949, 10) AS ten_digit_scale.

testdb-# roundd.4949. 0) AS rounded;

one_digit_scale | three_digit_scale | ten_digit_scale rounded

1.5 1.495 | 1.4949000000 1

(1 row)

Sin()

Синтаксис:

Sin(x)

Функция si n () получает один аргумент х типа doubl e preci si on, представляющий угол в радианах, и возвращает синус этого угла в виде значения типа doubl e preci si on.

Пример

testdb=# SELECT sin(pi() / 4) AS quarter_pi,

testdb-# sin(pi() / 2) AS half_pi:

quarter_pi | half_pi

0.707106781186547 |

(1 row)

sqrt()

Синтаксис:

sqrt()

Функция sqrt() получает один числовой аргумент х типа double precision или lumeric и возвращает квадратный корень, при этом тип возвращаемого значения ювпадает с типом аргумента. Фактически является обратной по отношению к Ьункции pow(), выполняющей возведение во вторую степень.

Пример

estdb=# SELECT sqrt(2.0), sqrt(4.0),

estdb-# sqrt(pow(2.0, 2)) AS inverse_example:

sqrt | sqrt | inverse_exampl

1.4142135623731 2 | 2

(1 row)

tan()

Синтаксис:

tan(x)

Функция tan() получает один аргумент х типа doubl e preci si on, представляющий тол в радианах, и возвращает тангенс этого угла в виде значения типа doubl e preci s i on.

Пример

testdb=# SELECT tan(pi() / 8).

testdb-# tan(O): tan | tan

0.414213562373095 |

(1 row)

trunc()

Синтаксис:

trunc(x) trunc(x.s)

Функция trunc() вызывается с одним или с двумя аргументами. Первый аргумент х относится к типу numeric или double precision и определяет исходное число. Второй необязательный аргумент s относится к типу integer и определяет количество цифр в дробной части после усечения.

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

Пример

testdb=# SELECT trunc(1.598) AS natural-truncation.

testdb-# trunc(1.598. 1) AS one_decima1_point,

testdb-# trunc(1.598, 8) AS extra_places:

natural_truncation one_decimal_point | extra_places

1 | 1.5 | 1.59800000

(1 row)

Строковые функции

В PostgreSQL существует множество разнообразных строковых функций, предназначенных для форматирования, анализа и сравнения строк. К их числу относятся как функции стандарта SQL92, так и нестандартные расширения PostgreSQL (например, ItrimO, rtrimO и substrO). Строковые функции PostgreSQL перечислены в табл. 5.10. В общем случае все, что говорится о типе text, в равной степени относится к значениям типа character и varchar.

Таблица 5.10. Строковые функции

Функция	Описание
ascii(s)	Возвращает ASCII-код символа, переданного в виде строковой переменной s
btrim(s [, t])	Возвращает строку s, в начале и в конце которой удалены все символы, входящие в строку t (если аргумент t не задан, усекаются начальные и конечные пропуски — пробелы, символы табуляции и т. д.)
char length(s)	Возвращает длину строки s в символах
chr(n)	Возвращает символ с ASCII-кодом n
s ilike(f)	Возвращает true, если выражение f совпадает (без учета регистра символов) с s
imtcap(s)	Возвращает строку s, в которой первая буква каждого слова преобразуется к верхнему регистру
length(s)	Возвращает длину строки s в символах
s like(f)	Возвращает true, если выражение f совпадает с s
lower (s)	Возвращает строку s, преобразованную к нижнему регистру
lpad(s. n [. c])	Возвращает строку s, дополненную слева содержимым строки с (или пробелами, если аргумент с не задан) до длины n (или усеченную справа до n символов)
ltrim(s [. f])	Возвращает строку s, в начале которой удалены все символы, входящие в строку f (если аргумент f не задан, усекаются начальные пропуски — пробелы, символы табуляции и т. д.)
octet_length(s)	Возвращает длину строки s в байтах
pos1tion(b IN s)	Возвращает позицию подстроки b в строке s (отсчет начинается с 1)
repeat (s, n)	Возвращает строку s, повторенную n раз
rpad(s. n. [. c])	Возвращает строку s, дополненную справа содержимым строки с (или пробелами, если аргумент с не задан) до длины n (или усеченную слева до n символов)
rtrim(s [. f])	Возвращает строку s, в конце которой удалены все символы, входящие в строку f (если аргумент f не задан, усекаются конечные пропуски — пробелы, символы табуляции и т. д.)
strpos(s. b)	Возвращает позицию подстроки b в строке s (отсчет начинается с 1). Относится к числу функций PostgreSQL и дублирует функцию SQL positionO, но с передачей аргументов в стиле С
substr(s. b [. 1])	Выделяет из строки s подстроку, начинающуюся с позиции n (отсчет начинается с 1). Необязательный аргумент 1 определяет максимальную длину подстроки в символах
substring FROM n FOR 1 )	Выделяет из строки s подстроку, начинающуюся с позиции n (отсчет начинается с 1). Необязательный аргумент 1 определяет максимальную длину подстроки в символах
to_ascii (s. f)	Возвращает строку s, преобразованную из расширенной кодировки f в ASCII
translate(s. f. r)	Возвращает строку s, в которой все символы, входящие в строку f, заменяются соответствующими символами строки г
trim(направление f FROM s)	Возвращает строку s, в начале и/или в конце которой удалены все символы, входящие в строку f. В аргументе направление передается ключевое слово SQL, определяющее направление усечения (LEADING, TRAILING или BOTH)
upper(s)	Возвращает строку s, преобразованную к верхнему регистру

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

ascii()

Синтаксис: ascii(s)

Функция asci i () получает один аргумент — отдельный символ или строку типа text, и возвращает числовой ASCII-код первого интерпретированного символа. Результат возвращается в виде значения типа integer.

Пример

booktown=# SELECT asciitТ); ascii

84

(1 row)

booktown=# SELECT DISTINCT ON (substr)

booktown-# title. substrCtitle, 1, 1),

booktown-# ascii(tltle)

booktown-# FROM books

booktOMl-f ORDER BY substr ASC:

title | substr ascii

2001: A Space Odyssey | 2 | 50

Bartholomew and the Oobleck j В I 66

Dune |D 68

Franklin in the Dark | 70

Goodnignt Moon | G j 71

Little Women L 76

Practical PostgreSQL | P j 80

The Shining Т 84

(8 rows)

btrim()

Синтаксис:

btrim(s) btrim(s. t)

Функция получает один или два аргумента типа text, второй аргумент t не является обязательным. Если аргумент t задан, функция удаляет в начале и в конце строки s все символы, входящие в строку t. Если функция вызвана с одним аргументом, удаляются начальные и конечные пропуски — пробелы, символы табуляции и т. д.

Порядок перечисления символов в строке t для функции btrim() значения не имеет. В начале и конце строки s удаляются подстроки, полностью состоящие из символов, входящих в строку t.

Пример

booktown=# SELECT btrimC whitespace example ') AS trim_blanks,

booktown-# btr1m('123example 332', '123') AS trimjnumbers:

trim_blanks | trim_numbers

whitespace example example

(1 row)

char_length()

Синтаксис:

charjength(s)

Функция SQL92 char_l engthC) получает один аргумент s типа text, varchar или character и возвращает длину полученной строки в символах. Возвращаемое значение относится к типу integer.

Пример

booktown=# SELECT charjength(title). title

booktown-l FROM books Dooktown-# LIMIT 3: charjength | title

11 | The Shining

4 Dune

21 I 2001: A Space Odyssey

(3 rows)

chr()

Синтаксис: chr(n)

Функция chr() получает один числовой аргумент п типа integer и возвращает символ с ASCII-кодом, равным п. Возвращаемое значение относится к типу text. Функция chr() фактически является обратной по отношению к функции asci 1 ().

Примеры

booktowrHf SELECT chr(65), ascii('A');

chr ! ascii

A | 65

(1 row)

initcap()

Синтаксис: initcap(s)

Функция initcapO получает один аргумент s типа text, преобразует первые буквы всех слов к верхнему регистру и возвращает полученную строку. В данном контексте «словом» считается любая последовательность символов, отделенная от других слов пробелами.

Пример

booktown=# SELECT initcapCa prospective book title'):

initcap

A Prospective Book Title

(1 row)

length()

Синтаксис:

length(s)

Аналог функции SQL92 charj ength(). Получает один аргумент s типа text, varchar или character и возвращает длину полученной строки в символах в виде значения типа integer.

Пример

booktown=# SELECT length(title), title

booktown-# FROM books

booktown-# LIMIT 3; length title

11 | The Shining 4 I Dune

21 | 2001: A Space Odyssey

(3 rows)

ПРИМЕЧАНИЕ

В стандарт SQL92 включены две функции определения длины строки: char_length() и octet_length(). Следовательно, эти две функции с большей, чем функция length(), вероятностью будут поддерживаться другими реляционными СУБД.

like() и ilike()

Синтаксис:

s like (f) s LIKE f like(s.f) s ilike(f) s HIKE f

Функция 1 i ke() проверяет, совпадает ли выражение, заданное аргументом f, со строкой s. При вызове функции либо передаются два аргумента типа text, либо используется специальный синтаксис SQL, в котором аргумент s предшествует имени функции. Функция 11 i ke() является нестандартной версией функции 11 ke(), игнорирующей регистр символов при сравнении, и вызывается только в синтаксисе SQL.

ПРИМЕЧАНИЕ

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

Использование функции 1 ike() отличается от обычного сравнения с помощью оператора =, поскольку строка f может содержать символы подчеркивания (_) или процента (%), интерпретируемые особым образом. PostgreSQL интерпретирует символ _ как один произвольный символ, а символ % — как ноль или более произвольных символов. Эти специальные символы могут находиться в любом месте строки f.

В PostgreSQL существуют и более совершенные средства поиска по шаблону, о которых говорилось в пункте «Операторы регулярных выражений» подраздела «Строковые операторы» раздела «Операторы» этой главы.

Примеры

booktown=# SELECT * FROM books

booktown-# WHERE title LIKE ('XRabbit1):

id i title | authorjd subjectjd

1234 | The Velveteen Rabbit | 25041 | 3

(1 row)

booktown=# SELECT * FROM books

booktown-# WHERE title LIKE

id | title | authorjd | subjectjd

4513 | Dune | 1866 | 15

25908 | Franklin in the Dark | 15990 | 2 (2 rows)

booktown=# SELECT * FROM books

booktown-# WHERE title ILIKE '«python*':

id | title | authorjd | subjectjd

41473 | Programming Python | 7805 | 4 41477

Learning Python | 7805 4

(2 rows)

lower()

Синтаксис: lower(s)

Функция SQL92 1 ower () получает один аргумент типа text, преобразует все символы строки к нижнему регистру и возвращает полученную строку в виде значения типа text.

Пример

booktown=# SELECT lower(title)

booktown-# FROM books

booktown-# LIMIT 3;

lower

tne shining

dune

2001: a space odyssey

lpad()

Синтаксис:

lpad(s. n) 1pacl(s. n. c)

Функция lpad() вызывается с двумя или тремя аргументами s, n и с (необязательный аргумент), относящимися к типам text, Integer и text соответственно. Строка s дополняется слева до длины n символов пробелами или содержимым необязательного аргумента с.

Если начальная длина строки s больше п, строка усекается справа до длины п.

Пример

booktown=# SELECT title. IpadCtitle, 12, AS dashed,

booktown-# IpadCtitle. 12. '-+-') AS pius_dashed

booktown-# FROM books LIMIT 4;

title | dashed | plus_dashed _

The Shining | -The Shining | -The Shining

Dune |Dune Dune

2001: A Space Odyssey | 2001: A Spac | 2001: A Spac

The Cat in the Hat | The Cat in t | The Cat in t

(4 rows)

Itrim()

Синтаксис:

Itrim(s) ItrimCs. f)

Функция 11 ri m() получает один или два аргумента типа text (аргумент f не обязателен). Если аргумент f не задан, функция возвращает строку s, из которой удалены все начальные пробелы. В противном случае удаляется начальная подстрока, состоящая только из символов, содержащихся в f. Если такая подстрока не найдена, s остается без изменений.

Примеры

booktown=# SELECT ItrimC whitespace example'):

Itrim

whitespace example (1 row)

booktown=# SELECT title. ItrimCtitle, 'TD2he ')

booktown-# FROM books booktown-# LIMIT 4:

title | Itritn

The Shining | Shining

Dune I une

2001: A Space Odyssey | 001: A Space Odyssey

The Cat in the Hat j Cat In the Hat

(4 rows)

octet_length()

Синтаксис: octetjength(s)

Функция SQL92 octetj ength() получает один аргумент s типа text, varchar или character и возвращает длину полученной строки в байтах. Возвращаемое значение относится к типу integer.

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

Пример

booktown=# SELECT title, octetjength(title)

booktown-# FROM books

booktown-# ORDER BY title ASC

booktown-# LIMIT 3:

title octet Jength

2001: A Space Odyssey | 21

Bartholomew and the Oobleck | 27

Dune | 4

(3 rows)

position()

Синтаксис: position(b IN s)

Функция SQL92 position^) получает два аргумента, относящихся к типу text, и возвращает начальную позицию подстроки b в строке s (отсчет начинается с 1). Возвращаемое значение относится к типу i nteger. Если подстрока не найдена, функция возвращает 0.

Пример

booktown=# SELECT title, positiorK'the' IN title) AS the_pos

booktown-# FROM books

booktown-# WHERE positionCthe1 IN title) != 0:

title | the_pos

The Cat in the Hat | 12

Bartholomew and the Oobleck | 17

Franklin in the Dark 13

(3 rows)

repeat()

Синтаксис: repeat(s. n)

Функция repeat О получает два аргумента s и п, относящихся к типам text и integer соответственно. Функция возвращает символьную строку s, повторенную п раз, в виде значения типа text.

Пример

booktown=# SELECT repeat(lastjname. 2)

booktown-l FROM authors booktown-# LIMIT 3; repeat

DenhamDenham BourgeoisBourgeois BiancoBianco

(3 rows)

rpad()

Синтаксис:

rpad(s. n) rpad(s. n. c)

Функция rpadO похожа на функцию IpadO, но дополняет строку справа, а не слева. Она вызывается с двумя или тремя аргументами s, n и с (необязательный аргумент), относящимися к типам text, integer и text соответственно. Строка s дополняется справа до длины п символов пробелами или содержимым необязательного аргумента с.

Если начальная длина строки s больше п, строка усекается слева до длины п.

Пример

booktown=# SELECT rpad('whitespace example'. 30): rpad

whitespace example (1 row)

booktown=# SELECT title. rpacKtitle. 12, ' -') AS right_dashed.

booktown-# rpad(title, 12, '-+-') AS right_plus_dashed

booktown-# FROM books

booktown-f LIMIT 3:

title [ right_dashed | right_plus_dashed

The Shining The Shining- | The Shining-Dune Dune

2001: A Space Odyssey 2001: A Spac 2001: A Spac

(3 rows)

rtrim()

Синтаксис:

rtrim(s)

trlm(s. f)

Функция rtri m() получает один или два аргумента типа text (аргумент f не обязателен). Если аргумент f не задан, функция возвращает строку s, из которой удалены все завершаю nine пробелы. В противном случае удаляется завершающая подстрока, состоящая только из символов, содержащихся в f. Если такая подстрока не найдена, s остается без изменений.

Примеры

booktown=# SELECT rtrimC'whitespace example ');

rtrim

whitespace example

(1 row)

booktown=# SELECT title, rtrinKtitle, 'yes')

booktown-# FROM books

booktown-# LIMIT 4;

title rtrim

The Shining | The Shining

Dune Dun 2001: A Space Odyssey 2001: A Space Od

The Cat in the Hat j The Cat in the Hat

(4 rows)

strpos()

Синтаксис: strpos(s. b)

Функция strposO эквивалентна функции SQL92 positionO, но аргументы передаются ей в стиле С. Функция получает два аргумента, относящихся к типу text, и возвращает начальную позицию подстроки b в строке s (отсчет начинается с 1). Возвращаемое значение относится к типу 1 nteger. Если подстрока не найдена, функция возвращает 0.

Пример

booktown=# SELECT title, strposdower(title), 'rabbit')

booktown-l FROM books

booktown-# WHERE strposdower(title), 'rabbit') != 0;

title | strpos

The Velveteen Rabbit | 15

(1 row)

substr()

Синтаксис:

substr(s. n) substr(s. n. 1)

Функция substrO эквивалентна функции SQL92 substring!), но аргументы передаются ей в стиле С. Функция вызывается с двумя или тремя аргументами s, n и 1 (необязательный аргумент), относящимися к типам text, т nteger и i nteger соответственно. Возвращаемое значение представляет собой подстроку s, начинающуюся с позиции п. Необязательный аргумент 1 определяет максимальную длину подстроки в символах.

Если заданная длина подстроки превышает количество оставшихся символов в строке s, возвращается только остаток строки. Иначе говоря, возвращаемое значение не дополняется до заданной длины.

Пример

booktown=# SELECT title, substr(title, 15). substr(title. 5. 9)

booktown-# FROM books

booktown-# ORDER BY title DESC

booktown-# LIMIT 3;

title | substr | substr

The Velveteen Rabbit Rabbit | Velveteen

The Tell-Tale Heart Heart Tell-Tale

The Shining | | Shining

(3 rows)

substring()

Синтаксис:

substrts. n) substr(s, n. 1)

Функция SQL92 substring() эквивалентна функции PostgreSQLsubstr(). Функция вызывается с двумя или тремя аргументами s, n и 1 (необязательный аргумент), относящимися к типам text, integer и integer соответственно. Возвращаемое значение представляет собой подстроку s, начинающуюся с позиции п. Необязательный аргумент 1 определяет максимальную длину подстроки в символах.

Примеры

booktown=# SELECT title, substring(title FROM 15)

booktown-# FROM books

booktown-* ORDER BY title DESC

booktown-# LIMIT 3;

title | substring

The Velveteen Rabbit | Rabbit

The Tell-Tale Heart Heart The Shining

(3 rows)

booktown=# SELECT title, substring(title FROM 5 FOR 9)

booktown-# FROM books

booktown-# ORDER BY title DESC booktown-# LIMIT 3;

title | substring

The Velveteen Rabbit Velveteen

The Tell-Tale Heart Tell-Tale

The Shining | Shining

(3 rows)

to_ascii()

Синтаксис:

to_ascii(s, f)

Функция to_asci i () получает строковый аргумент s, относящийся к типу text, и обозначение расширенной кодировки f, а возвращает обычный ASCII-текст в виде значения типа text.

Допустимы следующие обозначения расширенных кодировок: LATIN1 (ISO 8859-1), LATIN2 (ISO 8859-2) и WIN1250 (Windows CP1250 или WinLatin2). Для работы функции необходима поддержка расширенных кодировок (устанавливается при помощи ключа командной строки компилятора при установке PostgreSQL).

Пример

booktown=# SELECT to_ascii('Multibyte Source', 'LATIN1');

to_ascii

Multibyte Source

(1 row)

translate()

Синтаксис: translate(s. f. r)

Функция transl ate() получает три аргумента s, f и г, относящихся к типу text. В строке s все символы, входящие в строку f, заменяются символами с тем же индексом из строки г. Возвращаемое значение относится к типу text.

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

В следующем примере все вопросительные знаки заменяются восклицательными.

Пример

booktown=# SELECT translated am an example?', '?', '!'):

translate

I am an example!

(1 row)

В следующем примере все вхождения символа «i» заменяются символом «w», а все вхождения символа «s» — символом «а». Лишний символ в конце строки «was» игнорируется.

Пример

booktown=# SELECT translate('This is a mistake.', 'is', 'was');

translate

Thwa wa a mwatake.

(1 row)

В последнем примере все гласные буквы заменяются пустой строкой, то есть удаляются из входной строки.

Пример

booktown=# SELECT title.

booktown-# translateCtitle, 'aeiouAEIOU', '') AS vowelless

booktown-# FROM books

booktown-f LIMIT 5;

title | vowelless

The Shining Th Shnng

Dune I Dn

2001: A Space Odyssey | 2001: Spc dyssy

The Cat In the Hat | Th Ct n th Ht

Bartholomew and the Oobleck Brthlmw nd th blck

(5 rows)

trim()

Синтаксис:

trim(направление f FROM s)

Функция SQL92 trim() способна заменить функции PostgreSQL rtrim(), ltrim() и btrim(). При вызове она получает три аргумента: ключевое слово (LEADING, TRAILING или BOTH) и две строки, f и s.

С ключевым словом LEADING функция trim() работает аналогично функции ItrimO, удаляя в начале строки s наибольшую подстроку, все символы которой входят в f.

С ключевым словом TRAILING функция trim() работает аналогично функции rtrim(), удаляя в конце строки s наибольшую подстроку, все символы которой входят в f.

С ключевым словом BOTH функция trim() работает аналогично функции btrim(), удаляя в начале и в конце строки s наибольшие подстроки, все символы которых входят в f.

Примеры

booktowrH1 SELECT isbn, trim(LEADING '0' FROM isbn)

booktown-# FROM editions booktown-# LIMIT 2; isbn | Itrim

039480001X 39480001X 0451160916 451160916

(2 rows)

booktown=# SELECT isbn, trimCTRAILING 'X' FROM isbn)

booktown-# FROM editions booktown-# LIMIT 2;

1Sbn rtrim

039480001X j 039480001 0451160916 j 0451160916

(2 rows)

booktowrHf SELECT isbn, trimCBOTH 'OX' FROM isbn)

booktown-# FROM editions booktown-# LIMIT 2; isbn I btrim

039480001X | 39480001 0451160916 | 451160916

(2 rows)

upper()

Синтаксис:

upper(s)

Функция SQL92 upper() получает один аргумент типа text, преобразует все символы строки к верхнему регистру и возвращает полученную строку в виде значения типа text.

Пример

booktown=# SELECT title, upper(title)

booktown-# FROM books

booktown-# ORDER BY id ASC

booktown-# LIMIT 3;

title | upper

The Tell-Tale Heart | THE TELL-TALE HEART

Little Women j LITTLE WOMEN

The Velveteen Rabbit | THE VELVETEEN RABBIT

(3 rows)

Функции для работы с датой и временем

В PostgreSQL поддерживаются как стандартные функции SQL current_date, current_time и current_timestamp, так и множество нестандартных функций. Функции PostgreSQL, предназначенные для работы с датой и временем, перечислены в табл. 5.11.

Таблица 5.11. Функции для работы с датой и временем

Функция	Описание
current date	Возвращает текущую дату в виде значения типа date
current time	Возвращает текущее время в виде значения типа time
current timestamp	Возвращает текущие дату и время в виде значения типа timestamp
date_part(s. t)	Выделяет из значения типа timestamp компонент даты или времени, определяемый строкой s
date_part(s. i)	Выделяет из значения типа interval компонент даты или времени, определяемый строкой s
date trunc(s, t)	Возвращает значение типа timestamp, усеченное до точности s
extract (k FROM t)	Выделяет из значения типа timestamp компонент даты или времени, определяемый ключевым словом k
extracttk FROM i)	Выделяет из значения типа interval компонент даты или времени, определяемый ключевым словом k
isfinite(t)	Возвращает true, если значение типа timestamp соответствует конечной величине (не invalid и не infinity)
isfinite(i)	Возвращает true, если значение типа interval соответствует конечной величине (не infinity)
now()	Возвращает текущие дату и время в виде значения типа timestamp. Эквивалент константы now
timeofdayO	Возвращает текущие дату и время в виде значения типа text

Ниже приведены более подробные описания всех функций, перечисленных в таблице. Обратите внимание на отсутствие круглых скобок в определениях функций current_date, current_time и current_timestamp — это сделано для сохранения совместимости со стандартом SQL92.

current_date

Синтаксис:

current_date

Функция current_date вызывается без аргументов и возвращает текущую дату в виде значения типа date. Результат эквивалентен преобразованию специальной константы now к типу date.

Пример

booktown=# SELECT current_date,

booktown-# 'now'::date AS date;

date date

2001-08-31 | 2001-08-31

(1 row)

current_time

Синтаксис:

current_time

Функция current_time вызывается без аргументов и возвращает текущее время в виде значения типа time. Результат эквивалентен преобразованию специальной константы now к типу time.

Пример

booktown=# SELECT current_time,

booktown-# 'now'::time AS time;

time | time

11:36:52 | 11:36:52

(1 row)

current_timestamp

Синтаксис:

current_timestamp

Функция current_timestamp вызывается без аргументов и возвращает текущее время в виде значения типа timestamp. Результат эквивалентен преобразованию специальной константы now к типу timestamp или вызову функции now().

Пример

booktown=# SELECT current_timestamp.

booktown-# now() AS timestamp;

timestamp | timestamp

2001-08-31 11:39:42-07 2001-08-31 11:39:42-07

(1 row)

date_part()

Синтаксис:

date_part(s. t) date_part(s. i)

Функция date_part() получает два аргумента. Первый аргумент s относится к типу text, а второй, t или I, — к типу timestamp или interval соответственно. Функция выделяет во втором аргументе компонент, определяемый строкой s, и возвращает его в виде значения типа doubl e preci si on.

Чтобы лучше понять, как работает функция date_part(), представьте, что значение типа timestamp или interval делится на компоненты — часы, минуты и т. д. Эти компоненты (и соответственно допустимые значения аргумента s) перечислены в табл. 5.12. Помните, что некоторые значения относятся только к типу tlmestamp и не поддерживаются для типа Interval.

Таблица 5.12. Компоненты типов timestamp и interval

Компонент	Описание
century	Год, разделенный на 100 (не совпадает с текущим веком!)
day	День месяца (от 1 до 31) для типа timestamp, продолжительность интервала в днях для типа interval
decade	Год, разделенный на 10
dow	День недели (от 0 до 6), начиная с воскресенья. Для типа interval не поддерживается
doy	День года (от 1 до 366). Для типа interval не поддерживается
epoch	Количество секунд от начала эпохи (1 января 1970 г.) для типа timestamp, продолжительность интервала в секундах для типа interval
hour	Час в значении типа timestamp
microseconds	Количество миллионных долей в дробной части секунд для значения типа timestamp
millennium	Год, разделенный на 1000 (не совпадает с текущим тысячелетием!)
mi 11 i seconds	Количество тысячных долей в дробной части секунд для значения типа timestamp
minute	Минуты в значении типа timestamp или interval
month	Месяц в значении типа timestamp или остаток от деления продолжительности интервала в месяцах на 12 для типа interval
quarter	Квартал (от 1 до 4) для значений типа timestamp
second	Секунды в значении типа timestamp или interval
week	Номер недели в году для значений типа timestamp. В стандарте ISO-8601 первая неделя года определяется как неделя, в которую входит 4 января
year	Год в значении типа timestamp или interval

Примеры

booktown=# SELECT date_part('minute'.

booktown(# intervalC3 days 4 hours 12 minutes')):

date_part

12

(1 row)

booktown=# SELECT isbn,

booktown-l date_part('year', publication)

booktown-# FROM editions

booktown-# ORDER BY date_part ASC

booktown-# LIMIT 3;

isbn | date_part

0760720002 | 1868

0679803335 | 1922

0694003611 | 1947

(3 rows)

ПРИМЕЧАНИЕ

Аналогом функции date_part() в SQL является функция extract().

date_trunc()

Синтаксис: date_trunc(s. t)

Функция date_trunc () получает два аргумента s и t, относящихся к типам text и timestamp соответственно. Строка s определяет компонент, до которого усекается аргумент t. В данном контексте под усечением понимается удаление всех компонентов, детализация которых превышает заданную.

Допустимые значения аргумента s перечислены в табл. 5.12.

Пример

booktown=# SELECT date_trune('minute', now());

date trunc

2001-08-31 09:59:00-07

(1 row)

booktown=# SELECT date_trunc( 'hour', now());

date trunc

2001-08-31 09:00:00-07 (1 row)

booktown=# SELECT date_trunc('year', now());

date trunc

2001-01-01 00:00:00-08

(1 row)

extract()

Синтаксис:

extract(k FROM t) extract(k FROM 1)

Функция SQL92 extractO представляет собой аналог функции PostgreSQL date_part() со слегка измененным синтаксисом. При перечислении аргументов вместо запятой используется ключевое слово FROM. Аргументы аналогичны аргументам функции date_part(), хотя первый аргумент является ключевым словом SQL, а не строкой и поэтому не заключается в кавычки. Допустимые значения k перечислены в табл. 5.12.

Учтите, что функция extractO является «псевдонимом» функции PostgreSQL date_part () в синтаксисе SQL92, поэтому по умолчанию в выходных данных функция extract() представляется именем date_part.

Примеры

booktown=# SELECT extract(MINUTE FROM

interval('3 days 12 minutes1)): datejpart

12

(1 row)

booktown=# SELECT extractCMONTH FROM now()):

date_part

8

(1 row)

isfinite()

Синтаксис:

isfinite(t) isfinite(i)

Функция 1 sf 1 ni te() получает один аргумент типа timestamp или 1 interval. Она возвращает true, если переданное значение не является бесконечным, созданным при помощи специальной константы Infinity или invalid (используется только для timestamp).

Пример

booktown=# SELECT isfinite('now'::timestamp) AS now_is_finite,

booktown-# isfinite('infinity'::timestamp) AS Infinity,

booktown-# isfiniteC'invalid'::timestamp) AS invalid;

now_is_finite | infinity | invalid

(1 row)

now()

Синтаксис:

now()

Функция now() вызывается без аргументов и возвращает текущую дату и время в виде значения типа timestamp.

Пример

booktown=# SELECT now();

now

2001-08-31 10:31:18-07

(1 row)

timeofday()

Синтаксис: timeofdayO

Функция timeofday() вызывается без аргументов и возвращает текущие время и дату. Она похожа на функцию now(), но возвращает значение типа text. Это делает ее менее удобной, поскольку значение не разделяется на компоненты функциями date_part() и to_char() без предварительного преобразования к другому типу.

Пример

Dooktown=# SELECT timeofday();

timeofday

Fn Aug 31 10:33:00.837338 2001 PDT

(1 row)

Функции преобразования типа

Хотя PostgreSQL позволяет выполнять явные преобразования между основными типами данных, некоторые преобразования удобнее выполнять при помощи функций. В табл. 5.13 перечислены основные функции преобразования типов PostgreSQL, а ниже приводятся их подробные описания.

Таблица 5.13. Функции преобразования типов

Функция	Описание
t>1tfromint4(n)	Преобразует число в битовую последовательность
DlttOinW(b)	Преобразует битовую последовательность в десятичное представление
to_cnar(n. f)	Преобразует число в строку в формате f
to_char(t. f)	Преобразует значение типа timestamp в строку в формате f
to_date(s. f)	Преобразует строку в формате даты f в значение типа date
to_number(s. f)	Преобразует строку в формате даты f в значение типа numeric
to timestamp(s. f)	Преобразует строку в формате даты f в значение типа timestamp
timestamp(d)	Преобразует значение типа date к типу timestamp
timestamp(d. t)	Преобразует два значения типов date и time к типу timestamp

bitfromint4()

Синтаксис:

bit.fromint4(n)

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

Возвращаемое значение относится к типу bi t, а его длина не превышает 32 бита. Поскольку тип i nteger является знаковым, допустимые значения аргумента лежат в интервале от -2 147 483 648 до 2 147 483 647.

Пример

booKtown=# SELECT bitfronrint4(16385);

bitfromint4

00000000000000000100000000000001

(1 row)

bittoint4()

Синтаксис:

bittoint4(b)

Функция bittoint4() фактически является обратной по отношению к bitfromint4() — она получает один аргумент b тина bit п возвращает его десятичное числовое значение в виде тина integer.

Соответственно аргумент функции bitfrwint4() содержит до 32 двоичных цифр, а возвращаемое значение лежит в интервале от -2 147 483 648 до 2 147 483 647.

Пример

booktown=# SELECT bittoint4(B'101010').

booktown-# Mttoint4(bitfromint4(99))

AS inverse_example: bittoint4 inverse_example

42 | 99

(1 row)

to_char() для чисел

Синтаксис:

to_char(n. f)

Функция to_char(), вызываемая с аргументом п типа numeri с и аргументом f типа text, форматирует число п в строку типа text. Строка f описывает формат выходного значения.

Форматная строка f состоит из метасимволов, вместо которых PostgreSQL подставляет представляемые ими значения. Метасимволы, используемые в форматных строках, перечислены в табл. 5.14.

Таблица 5.14. Метасимволы форматирования чисел

Символ	Описание
9	Цифра
0	Цифра или начальный/конечный ноль, если количество цифр в f превышает количество цифр в п; может использоваться для принудительного вывода цифр в левой или правой части результата
.	Точка, отделяющая целую часть числа от дробной. Число может содержать только одну точку
'	Запятая. Число может содержать несколько запятых, используемых для разделения групп разрядов (тысячи, миллионы и т. д.)
D	Десятичный разделитель (например, точка), определяемый в локальном контексте
G	Разделитель групп разрядов (например, запятая), определяемый в локальном контексте
PR	Если PR находится в конце строки f, для отрицательных значений п результат заключается в угловые скобки
SG	Знак плюс (+) или минус (-) в зависимости от значения п
MI	Знак минус (-), если число п является отрицательным
PL	Знак плюс (+), если число п является положительным
S	Знак плюс (+) или минус (-), определяемый в локальном контексте
L	Денежный знак, определяемый в локальном контексте
RN	Римские цифры для значений п в интервале от 1 до 3999
TH. th	Суффикс числительного для числа п (например, 4th или 2nd)
V	Для каждого метасимвола 9 после V добавляется лишний ноль, то есть фактически происходит умножение на степень 10
FM	Из числа удаляются все начальные и завершающие нули (созданные символами 9, но не 0), а также все лишние пробелы

Если количество цифровых позиций, обозначенных метасимволом 9 в форматной строке, превышает количество цифр в числе п, лишние позиции заполняются пробелами. Если лишние цифровые позиции обозначены метасимволом 0, лишние позиции заполняются нулями.

Если количество заданных цифровых позиций меньше необходимого для представления целой части числа, преобразование становится неоднозначным из-за потери значащих цифр. Поскольку функция to_char() не знает, какие именно цифры следует удалить, во всех заданных позициях выводится символ #. Следовательно, в форматную строку необходимо включить максимальное количество цифр, кото-

рые могут быть получены в результате форматирования. Для удаления лишних пробелов также можно воспользоваться функцией trans! ate() или функциями усечения (HrimO и т. д.).

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

ПРИМЕЧАНИЕ

В форматной строке можно свободно использовать любые символы, не являющиеся метасимволами (например, символ $ и др.) В отформатированной строке они выводятся без изменений.

Примеры

booktown=# SELECT to_char(123456789. '999G999G999D99') AS formatted,

booktown-# to_char(123456789. '999999999') AS justjrigits,

booktown-f to_char(123456789, '00999999999') AS with_zeroes;

formatted | justjrigits | with_zeroes

123.456.789.00 | 123456789 | 00123456789

(1 row)

booktown=# SELECT cost * 100 AS cost_to_order.

booktown-# to_char(cost * 100, '$99.999.99') AS monetary,

booktown-# translate(to_char(cost * 100. '$9,999.99'),' ',").

booktown-# AS translated

booktown-# FROM stock

booktown-* LIMIT 3;

cost_to_order monetary translated

2900.00 | $ 2.900.00 $2.900.00

3000.00 $ 3.000.00 $3.000.00

1600.00 $ 1,600.00 i $1.600.00

(3 rows)

booktown=# SELECT tojrhard.O. '9th "Place"') AS first.

booktown-# to_char(2.2, '9th "Place"') AS second,

booktown-# to_char(pi(&thinsp;), '9th "Place"') AS third,

booktown-# to_char(10, '99V99th "\\"Place\\.....) AS shifted jjp;

first | second | third | shifted_up

1st Place | 2nd Place | 3rd Place | 1000th "Place"

(1 row)

ПРИМЕЧАНИЕ

В PostgreSQL версии 7.1.x ошибка в реализации метасимволов RN (римская запись) приводит к тому, что функция to_char() возвращает неправильный результат, если в форматной строке отсутствует последовательность FM. Ошибка должна быть исправлена в версии 7.2, а в качестве временной меры можно использовать полную последовательность FMRN.

to_char() для типа timestamp

Синтаксис:

to_char(t. f)

При вызове с аргументом t типа timestamp и аргументом f типа text функция to_char() форматирует дату и время в строку, возвращаемую в виде значения типа text.

Как и в случае с предыдущей версией to_char(), строка f содержит метасимволы, вместо которых PostgreSQL подставляет литералы. Метасимволы, используемые в форматных строках даты и времени, перечислены в табл. 5.15.

Таблица 5.15. Метасимволы форматирования чисел

Символ	Описание
НН. НН12	Час (от 1 до 12)
НН24	Час (от 0 до 23)
Ml	Минуты (от 0 до 59)
SS	Секунды (от 0 до 59)
SSSS	Секунды, прошедшие с полуночи (от 0 до 86 399)
AM, РМ, А.М, Р.М	Обозначение части суток в верхнем регистре с необязательным разделением символов точками
am, pm, a . m, p . m	Обозначение части суток в нижнем регистре с необязательным разделением символов точками
TZ, tz	Часовой пояс в верхнем или нижнем регистре
CC	Век, представленный двумя цифрами (не равен тку, деленному на 100!)
Y, YY, YYY, YYYY, Y.YYY	Последняя цифра, две цифры, три или четыре цифры года (с необязательным включением запятой)
BC, AD, B.C, A.O	Признак эры в верхнем регистре
be, ad, b.c, a.d	Признак эры в нижнем регистре
MONTH, Month, month	Полное название месяца, дополненное справа пробелами до 9 символов и записанное либо в верхнем регистре, либо с начальной прописной буквой, либо в нижнем регистре
MON, Mon, mon	Сокращенное трехбуквенное обозначение месяца, записанное либо в верхнем регистре, либо с начальной прописной буквой, либо в нижнем регистре
MM	Номер месяца (от 1 до 12)
RN, rn	Номер месяца в римской записи (от I до XII), в верхнем или нижнем регистре
DAY, Day, day	Полное название дня недели, дополненное справа пробелами до 9 символов и записанное либо в верхнем регистре, либо с начальной прописной буквой, либо в нижнем регистре
DY, Dy, dy	Сокращенное двухбуквенное обозначение дня недели, записанное либо в верхнем регистре, либо с начальной прописной буквой, либо в нижнем регистре
DDD, DD, D	День года (от 1 до 366), день месяца (от 1 до 31) или день недели (от 1 до 7, начиная с воскресенья)
W	Неделя месяца (от 1 до 5, с первого дня месяца)
WW	Неделя года (от 1 до 53, с первого дня года)
IW	Неделя года в стандарте ISO (с первого четверга нового года)
TH, th	Суффикс для предшествующего числа в верхнем или нижнем регистре
fm	Из строки удаляются все лишние нули и пробелы

Суффикс ТН и префикс FM должны непосредственно примыкать к тому значению, которое они модифицируют. Например, чтобы префикс FM применялся к компоненту Day, строка должна иметь вид FMDay (а не FM Day). Аналогично, чтобы день месяца выводился с суффиксом числительного, потребуется форматная строка DDTH (а не DD ТН).

Примеры

booktown=# SELECT to_char(now(). 'HH:MI PM1) AS the_time;

the_time

05:04 PM

(1 row)

booktown=# SELECT to_char(now(), 'Dy (Day), Mon (Month)')

booktown-# AS abbreviations,

booktown-# to_char('yesterday'::timestamp, 'FMMonth FMDDth')

booktown-# AS yesterday.

booktown-# to_char('yesterday':itimestamp, 'FMDDth FMMonth')

booktown-# AS "yesterday UK";

abbreviations | yesterday j yesterday UK

Sat (Saturday ). Sep (September) | August 31st | 31st August

(1 row)

booktown=# SELECT isbn,

booktown-# to_char(publication, 'FMMonth FMDDth, YYYY')

booktown-# AS informal,

booktown-# to_char(publication, 'YYYY-MM-DD') AS formal,

booktown-# to_char(publication. 'Y.YYY "years" A.D.')

booktown-# AS firstj)ublished

booktown-# FROM editions LIMIT 3;

isbn | informal | formal first_pub1ished

039480001X | March 1st. 1957 1957-03-01 1.957 years A.O.

0451160916 | August 1st. 1981 | 1981-08-01 | 1.981 years A.D.

0394800753 | March 1st. 1949 | 1949-03-01 | 1.949 years A.D.

(3 rows)

to date()

Синтаксис:

to_date(s. f)

Функция to_date() получает два аргумента типа text. Аргумент f описывает формат даты, представленной строкой s, и содержит метасимволы из табл. 5.15. Результат возвращается в виде значения типа date.

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

Пример

booktown=# SELECT date('198025thJune')

booktown-f AS non_standard_date_format,

booktown -# toJate (' 198025thJune', ' YYYYDDthMonth')

booktown-# AS correct_interpretation;

non_standard_date_format correct_interpretation

2025-08-27 | 1980-06-25

(1 row)

to_number()

Синтаксис:

to_number(s. f)

Функция to_number() получает два аргумента типа text. Аргумент f описывает формат числа, представленного строкой s, и содержит метасимволы из табл. 5.14. Результат возвращается в виде значения типа numeric.

Примеры

booktown=# SELECT to_number('$2,900.00', 'L9G999D99')

booktown-# AS monetary: monetary

2900.00 (1 row)

booktown=# SELECT tojiumber('123.456.789.00' . '999G999G999D99')

booktown-# AS formatted,

booktown-# to_numberС123456789'. '999999999')

booktown-# AS just_digits,

booktown-# to_number('00123456789'. '00999999999')

booktown-# AS leading_zeroes;

formatted | just_d1gits | leading_zeroes

123456789.00 123456789 | 123456789

(1 row)

to_timestamp()

Синтаксис:

to_timestamp(s. f)

Функция to_timestamp() получает два аргумента типа text. Аргумент f описывает формат даты/времени, представленного строкой s, и содержит метасимволы из табл. 5.15. Результат возвращается в виде значения типа date.

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

Пример

booktown=# SELECT timestamp('197825thJuly01:12am')

booktown-# AS non_standard_timestamp,

booktown-l to_fimestamp('197825July01:12am',

booktown(# 'YYYYDDFMMonthHH12:MIam')

booktown-# AS correcMnterpretation;

non_standard_timestamp | correctjnterpretation

2025-06-27 01:12:00-07 1978-07-25 01:12:00-07

(1 row)

ПРИМЕЧАНИЕ

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

timestamp()

Синтаксис:

timestamp(d) timestamptd, t)

Функция ti mestampC) получает либо один аргумент типа date, либо два аргумента типов date и time соответственно. Переданные аргументы преобразуются в значение типа tlmestamp и возвращаются функцией. При передаче одного аргумента предполагается, что время соответствует полуночи заданной даты.

Пример

booktown=# SELECT timestamp(date('now'))

AS today_at_midnight,

booktown-# timestamp(dateCnow'),

booktownd time('now')) AS right_now;

today_at_m1dnight | rightjiow

2001-09-01 00:00:00-07 | 2001-09-01 18:04:16-07

(1 row)

Агрегатные функции

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

В табл. 5.16 перечислены агрегатные функции, поддерживаемые в PostgreSQL. Полный список агрегатных функций выводится в psql командой \da.

Таблица 5.16. Агрегатные функции

Функция	Описание
avg( выражение')	Возвращает среднее арифметическое значений выражения для всех записей в группе
count ( выражение)	Возвращает количество записей в группе, для которых значение выражения отлично от NULL
ma x ( выражение}	Возвращает максимальное значение выражения в группе
mint выражение)	Возвращает минимальное значение выражения в группе
stddev ( выражение)	Возвращает среднеквадратичное отклонение значений выражения в группе
surrK выражение)	Возвращает сумму значений выражения в группе
variance( выражение)	Возвращает дисперсию значений выражения в группе

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

Агрегатные выражения

При вызове агрегатной функции передается агрегатное выражение, применяемое к записям, созданным командой SELECT. По своей структуре агрегатные выражения сходны с обычными выражениями SQL, но они могут начинаться с ключевого слова ALL или DISTINCT.

Если задано ключевое слово DISTINCT, функция обрабатывает только группы с уникальными значениями агрегатного выражения; группы с повторяющимися значениями игнорируются. Ключевое слово ALL, как и в команде SELECT, всего лишь явно указывает на тот факт, что выражение относится ко всем группам. В листинге 5.19 приведены примеры разных форм агрегатных выражений.

Листинг 5.19. Использование агрегатных выражений

booktown=# SELECT count(location) AS setjocations,

booktown-# count(ALL location) AS all_set_locations,

booktown-# count(DISTINCT location) AS unique_locations,

booktown-# count(*) AS all_rows booktown-# FROM subjects;

setjocations | all_set_locations | uniquejocations all_rows

15 15 | 7 16

(1 row)

У агрегатных выражений также имеется специальная форма, продемонстрированная в столбце al l_rows выходных данных листинга 5.19. Если при вызове агрегатной функции передается звездочка (*), это означает, что функция должна обрабатывать все поля, в том числе и обычно игнорируемые поля со значениями NULL. Поскольку таблица subjects содержит одну запись с полем location, равным NULL, результат подсчета записей по полю 1 ocati on отличается от результата подсчета по тому же нолю с использованием символа *.

ВНИМАНИЕ

Записи, у которых в агрегатное выражение входит NULL, не обрабатываются агрегатными функциями (исключением является функция count().

avg()

Синтаксис: avg(выражение)

Функция avg() получает агрегатное выражение, результат которого относится к любому из числовых типов (numeric, bigint, smallint, real или double precision) или к типу interval.

Функция возвращает среднее арифметическое всех данных, описываемых выражением. Возвращаемое значение относится к типу numeric для выражения типа integer или к типу double precision для выражения типа real. В остальных случаях тип возвращаемого значения совпадает с типом выражения.

Примеры

booktown=# SELECT avg(cost) AS average_cost,

booktown-# avg(retail) AS average_price,

booktown-# avg(retail - cost) AS average_profit

booktown-# FROM stock:

average_cost | average_price | average_prof1t

24.8235294118 | 30.0088235294 5.1852941176

(1 row)

x>oktown=# SELECT avg(cost)

AS average_cost, p.name AS publisher

booktown-# FROM stock JOIN editions USING (isbn))

booktown-l JOIN publishers AS p (publisheMd)

booktown-# USING (publisheMd)

booktown-# GROUP BY p.name;

average_cost | publisher

26.5000000000 | Ace Books

19.0000000000 | Books of Wonder

26.5000000000 I Doubleday

25.0000000000 | HarperCollins

18.0000000000 | Henry Holt & Company. Inc.

23.0000000000 I Kids Can Press

23.0000000000 | Mojo Press

20.0000000000 I Penguin

23.0000000000 Random House

26.5000000000 | Roc

26.0000000000 | Watson-Guptill Publications

(11 rows)

count()

Синтаксис:

count(выражение)

Функция countO возвращает количество значений, для которых выражение отлично от NULL. Тип выражения не ограничивается. Следует помнить, что функция count () подсчитывает только значения, отличные от NULL, поэтому для получения осмысленных результатов используемое выражение не должно возвращать NULL для подсчитываемых записей.

Если при вызове count() передается символ *, функция просто подсчитывает все записи, в том числе и содержащие NULL.

Примеры

booktown=# SELECT countC*) FROM editions;

count

17

(1 row)

booktown=# SELECT count(isbn). p.name

booktown-# FROM editions JOIN publishers AS p (publisheMd)

booktown-# USING (publisheMd)

booktown-# GROUP BY p.name

booktown-# ORDER BY count DESC;

count | name

3 | Random House

2 | Ace Books

2 | Doubleday

2 Roc

1 | Books of Wonder

1 | HarperCollins

1 | Henry Holt & Company. Inc.

1 | Kids Can Press

1 | Mojo Press

1 | O'Reilly & Associates

1 | Penguin

1 | Watson-Gupti11 Publications

(12 rows)

max()

Синтаксис:

max(выражение)

Функция max() возвращает максимальное значение заданного выражения в группе. Результатом выражения может быть значение любого числового или строкового типа, а также типа даты или времени. Тип возвращаемого значения совпадает с типом выражения.

Примеры

booktown=# SELECT max(cost), max(retail) FROM stock;

max max

36.00 I 46.95

(1 row)

booktown=# SELECT max(retail), p.name

booktown-# FROM (stock NATURAL JOIN editions)

booktown-# JOIN publishers AS p (publisher_id)

booktown-# USING (publisherjd)

booktown-# GROUP BY p.name

booktown-# ORDER BY max DESC;

max | name

46.95 | Roc

45.95 i Ace Books 36.95 Doubleday 32.95

Random House 28.95 HarperCollins

28.95 I Watson-Guptill Publications

24.95 | Mojo Press

24.95 | Penguin

23.95 1 Henry Holt & Company. Inc. 23.95

Kids Can Press 21.95 Books of Wonder

(11 rows)

min()

Синтаксис:

min (выражение)

Функция min() возвращает минимальное значение заданного выражения в группе. Результатом выражения может быть значение любого числового или строкового типа, а также типа даты или времени. Тип возвращаемого значения совпадает с типом выражения.

Примеры

booktown=# SELECT min(cost). min(retail) FROM stock;

min | min

16.00 16.95

(1 row)

booktown=# SELECT min(retail), p.name

booktown-# FROM (stock NATURAL JOIN editions)

booktown-# JOIN publishers AS p (publisheMd)

booktown-# USING (publisheMd)

booktown-l GROUP BY p.name

booktown-# ORDER BY min ASC;

miP 1 ..mme._

16.95 | Random House

21.95 Ace Books

21.95 I Books of Wonder

22.95 | Roc

23.95 | Henry Holt & Company. Inc.

23.95 | Kids Can Press

24.95 | Mojo Press

24.95 I Penguin

28.95 | Doubleday

28.95 I HarperCollins

28.95 | Watson-Guptill Publications

(11 rows)

stddev()

Синтаксис:

stddev(выражение)

Функция stddev О получает выражение, описывающее значения любого числового типа (numeri с, bigi nt, smal 1 i nt, real или doubl e preci si on), и возвращает среднеквадратичное отклонение для группы. Для вещественных выражений результат возвращается в виде значения типа double precision, а для остальных типов —в виде значения типа numeric.

Примеры

booktown=# SELECT stddev(retail) FROM stock;

stddev

8.46

(1 row)

booktown=# SELECT stddev(retail), p.name

booktown-# FROM (stock NATURAL JOIN editions)

booktown-# JOIN publishers AS p ON (publisheMd = p.id)

booktown-# GROUP BY p.name

booktown-# ORDER BY stddev DESC

booktown-# LIMIT 4:

stddev | name

16.97 | Ace Books

16.97 | Roc 8.02 Random House

5.66 | Doubleday

(4 rows)

sum()

Синтаксис:

sum (выражение)

Функция sum() получает выражение, описывающее значения любого числового типа (numeric, bigint, smallint, real или double precision), и возвращает сумму значений в группе. Для выражений типа integer результат возвращается в виде значения типа numeric, а для выражений типа real — в виде значения типа double precision. В остальных случаях тип возвращаемого значения совпадает с типом выражения.

Примеры

booktown=# SELECT sum(stock) FROM stock;

sum

"BOB"

(1 row)

booktown=# SELECT sum(stock). s.subject

booktown-# FROM ((stock NATURAL JOIN editions)

booktown(# JOIN books ON (books.id = bookjd))

booktown-f JOIN subjects AS s

booktown-# ON (books.subject_id = s.id)

booktown-# GROUP BY s.subject

booktown-# ORDER BY sum DESC;

189 | Horror

166 I Science Fiction

91 | Children's Books

28 | Drama

18 | Classics

16 | Arts

(6 rows)

variance()

Синтаксис: variance(выражение)

Функция variance() получает выражение, описывающее значения любого числового типа (numeric, bigint, small int, real или double precision), и возвращает дисперсию для группы (stddevO в квадрате). Для вещественных выражений результат возвращается в виде значения типа double precision, а для остальных типов — в виде значения типа numeric.

Примеры

booktown=# SELECT variance(retaiT) FROM stock;

variance

71.60

(1 row)

booktown=# SELECT varianceCretail), p.name

booktown-# FROM (stock NATURAL JOIN editions)

booktown-# JOIN publishers AS p

booktown-# ON (editions.pub!isher_id = p.id)

booktown-# GROUP BY p.name

booktown-# ORDER BY variance DESC

booktown-# LIMIT 4: variance name

288.00 | Ace Books 288.00 I Roc 64.33 !

Random House 32.00 | Doubleday

(4 rows)

---

---