Функції та оператори

Логічні оператори та оператори порівняння

Звичні логічні оператори все ще доступні:

boolean AND boolean -> boolean
boolean OR boolean -> boolean
NOT boolean -> boolean

SQL використовує тризначну логічну систему з істиною, хибністю і нулем, який представляє собою невідоме.

Таблиця істини

a

b

a AND b

a OR b

NOT a

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

FALSE

TRUE

FALSE

TRUE

NULL

NULL

TRUE

FALSE

FALSE

FALSE

FALSE

FALSE

TRUE

FALSE

NULL

FALSE

NULL

TRUE

NULL

NULL

NULL

NULL

NULL

Оператори AND і OR є комутативними, тобто ви можете міняти місцями лівий і правий операнди без впливу на результат. Однак не гарантується, що лівий операнд буде обчислено раніше правого.

Оператори порівняння

Доступні звичайні оператори порівняння.

Оператор

Опис

datatype < datatype -> boolean

Менше ніж

datatype > datatype -> boolean

Більше ніж

datatype <= datatype -> boolean

Менше ніж або дорівнює

datatype >= datatype -> boolean

Більше або дорівнює

datatype = datatype -> boolean

Дорівнює

datatype <> datatype -> boolean

Не дорівнює

datatype != datatype -> boolean

Не дорівнює

Математичні функції та оператори

Математичні оператори і передбачені для багатьох типів PostgreSQL.

Операція

Опис

numeric_type + numeric_type

Додавання

+ numeric_type

Унарний плюс (без операції)

numeric_type - numeric_type

Віднімання

- numeric_type

Заперечення

numeric_type * numeric_type

Множення

numeric_type / numeric_type

Ділення (для інтегральних типів ділення усікає результат до нуля)

numeric_type % numeric_type

За модулем (залишок); доступний для smallint, integer, bigint та numeric

numeric ^ numeric double precision ^ double precision

Піднесення до степеня

|/ double precision

Квадратний корінь

||/ double precision

Кубічний корінь

@ numeric_type

Абсолютне значення

integral_type & integral_type

Побітове І

integral_type | integral_type

Порозрядне АБО

integral_type # integral_type

Побітове виключне АБО

~ integral_type

Побітове І

integral_type << integer

Порозрядний зсув вліво

integral_type >> integer

Порозрядний зсув вправо

Рядкові функції

Рядки у цьому контексті включають значення типів символ, текст та символьна змінна.

text || text -> text

З’єднує два рядки.

'Post' || 'greSQL' -> PostgreSQL

text || anynonarray -> text
anynonarray || text -> text

Перетворює нестрокове введення у текст, а потім об’єднує два рядки. (Нестроковий ввід не може бути типом масиву, оскільки це створить неоднозначність з операторами масиву ||. Якщо ви хочете об’єднати текстовий еквівалент масиву, перетворіть його у текст явно)

'Value: ' || 42 -> Value: 42

char_length(text) → integer
character_length(text) → integer

Повертає кількість символів у рядку.

char_length('josé') -> 4

lower(text) → text

Перетворює рядок у всі регістри нижнього регістру відповідно до правил локалі бази даних.

lower('TOM') -> tom

ltrim(string text[, characters text]) → text

Видаляє найдовший рядок, що містить лише символи (за замовчуванням пробіл) з початку рядка.

ltrim('zzzytest', 'xyz') -> test

rtrim(string text[, characters text]) → text

Видаляє з кінця рядка найдовший рядок, що містить лише символи (за замовчуванням пробіл).

rtrim('testxxzx', 'xyz') -> test

trim([ LEADING | TRAILING | BOTH ] [ characters text ] FROM string text ) → text

Видаляє найдовший рядок, що містить лише символи (за замовчуванням пробіл) з початку, кінця або обох кінців рядка (за замовчуванням BOTH).

trim(both 'xyz' from 'yxTomxx') -> Tom

Зіставлення шаблонів

У PostgreSQL існує три окремі підходи до зіставлення шаблонів: традиційний оператор LIKE, найновіший оператор SIMILAR TO та регулярні вирази у стилі POSIX.

У цьому розділі буде розглянуто лише оператор LIKE.

string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]

Вираз LIKE повертає значення true, якщо рядок відповідає заданому шаблону (як і очікувалося, вираз NOT LIKE повертає значення false, якщо LIKE повертає значення true, і навпаки).

Якщо шаблон не містить знаків відсотків або підкреслень, то шаблон представляє лише сам рядок; у цьому випадку LIKE діє як оператор рівності. Підкреслення (_) у шаблоні означає (відповідає) будь-якому окремому символу; знак відсотка (%) відповідає будь-якій послідовності з нуля або більше символів.

Деякі приклади:

'abc' LIKE 'abc'  -- true
'abc' LIKE 'a%'   -- true
'abc' LIKE '_b_'  -- true
'abc' LIKE 'c'    -- false

шаблони LIKE завжди збігаються з усім рядком. Тому, якщо потрібно знайти послідовність у будь-якому місці рядка, шаблон повинен починатися і закінчуватися знаком відсотка.

Для відповідності буквеному підкресленню або відсотковому знаку без відповідності іншим символам перед відповідним символом у шаблоні має стояти екранований символ. Типовим символом за замовчуванням є зворотна коса риска, але ви можете вибрати інший символ за допомогою пункту ESCAPE. Щоб відповідати самому символу екранування, напишіть два символи екранування.

Ключове слово ILIKE можна використовувати замість LIKE, щоб зробити збіг нечутливим до регістру відповідно до активної локалі. Це не передбачено стандартом SQL, але є розширенням PostgreSQL.

Функції та оператори дати/часу

Вилучення

exctract(field FROM source)

Функція extract витягує підполя, такі як рік або година, зі значення дати/часу. Джерело має бути виразом типу timestamp, time або interval. Вирази типу date приводяться до timestamp, тому їх також можна використовувати. field - це ідентифікатор або рядок, який вказує, яке саме поле потрібно витягти з вихідного значення. Функція extract повертає значення типу numeric. Нижче наведено припустимі імена полів:

  • centurty

  • day

  • decade

  • dow

  • doy

  • epoch

  • hour

  • isodow

  • isoyear

  • julian

  • microseconds

  • millennium

  • milliseconds

  • minute

  • month

  • quarter

  • second

  • timezone

  • timezone_hour

  • timezone_minute

  • week

  • year

Функція date_part змодельована на основі традиційного для Ingres еквівалента стандартної функції SQL extract:

date_part('field', source)

Поточна дата/час

PostgreSQL надає ряд функцій, які повертають значення, пов’язані з поточною датою і часом. Всі ці стандартні функції SQL повертають значення на основі часу початку поточної транзакції:

CURRENT_DATE()
CURRENT_TIME()
CURRENT_TIME(precision)

Надавати поточне значення часу з часовим поясом.

точність:

Необов’язковий параметр, який призводить до округлення результату до такої кількості дробових цифр у другому полі. Якщо параметр не вказано, результат буде надано з повною доступною точністю.

CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP(precision)

Видавати поточне значення мітки часу з часовим поясом.

точність:

Необов’язковий параметр, який призводить до округлення результату до такої кількості дробових цифр у другому полі. Якщо параметр не вказано, результат буде надано з повною доступною точністю.

LOCALTIME()
LOCALTIME(precision)

Надавати поточне значення часу без часового поясу.

точність:

Необов’язковий параметр, який призводить до округлення результату до такої кількості дробових цифр у другому полі. Якщо параметр не вказано, результат буде надано з повною доступною точністю.

LOCALTIMESTAMP()
LOCALTIMESTAMP(precision)

Надавати поточне значення часу без часового поясу.

точність:

Необов’язковий параметр, який призводить до округлення результату до такої кількості дробових цифр у другому полі. Якщо параметр не вказано, результат буде надано з повною доступною точністю.

Функції форматування

Функції форматування PostgreSQL надають потужний набір інструментів для перетворення різних типів даних (дата/час, цілі числа, числа з плаваючою комою, числові) у відформатовані рядки і для перетворення з відформатованих рядків у певні типи даних.

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

to_char ( timestamp, text ) → text
to_char ( timestamp with time zone, text ) → text

Конвертує мітку часу в рядок відповідно до заданого формату.

to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') → 05:31:12

to_char ( interval, text ) → text

Перетворює інтервал у рядок відповідно до заданого формату.

to_char(interval '15h 2m 12s', 'HH24:MI:SS') → 15:02:12

to_char ( numeric_type, text ) → text

Перетворює число в рядок відповідно до заданого формату; доступний для цілих, біґінтових, числових, дійсних, подвійної точності.

  • to_char(125, '999') → 125

  • to_char(125.8::real, '999D9') → 125.8

  • to_char(-125.8, '999D99S') → 125.80-

to_date ( text, text ) → date

Конвертує рядок у дату відповідно до заданого формату.

to_date('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05

to_number ( text, text ) → numeric

Перетворює рядок у число відповідно до заданого формату.

to_number('12,454.8-', '99G999D9S') → -12454.8

to_timestamp ( text, text ) → timestamp with time zone

Перетворює рядок у мітку часу відповідно до заданого формату.

to_timestamp('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05 00:00:00-05

Умовні вирази

CASE

Вираз SQL CASE - це загальний умовний вираз, подібний до операторів if/else в інших мовах програмування.

COALESCE

Функція COALESCE повертає перший з аргументів, який не є нульовим. Null повертається лише у випадку, якщо всі аргументи є нульовими. Вона часто використовується для заміни значення за замовчуванням на нульові значення, коли дані отримуються для відображення:

SELECT COALESCE(description, short_description, '(none)') ...

Повертає description, якщо він не є нульовим, інакше short_description, якщо він не є нульовим, інакше (none).

Як і вираз CASE, COALESCE обчислює тільки ті аргументи, які потрібні для визначення результату; тобто аргументи праворуч від першого ненульового аргументу не обчислюються.

NULLIF

NULLIF(value_1, value_2)

Функція NULLIF повертає нульове значення_1, що дорівнює значенню_2; інакше повертає значення_1. Це може бути використано для виконання оберненої операції до COALESCE.

SELECT NULLIF(value, '(none)') ...

У наведеному вище прикладі, якщо value дорівнює (none), повертається нуль, інакше повертається значення value.

Два аргументи повинні бути порівнянних типів. Якщо бути точним, вони порівнюються так само, як якщо б ви написали value_1 = value_2, тому має бути відповідний оператор =.

Результат має той самий тип, що і перший аргумент - але є один нюанс. Насправді повертається перший аргумент неявного оператора =, і в деяких випадках він буде приведений до типу другого аргументу.

Агрегатні функції

Агрегатні функції обчислюють один результат з набору вхідних значень. Їх існує багато, і різні СКБД реалізують різні функції агрегації. У цьому розділі ми зосередимося лише на найбільш поширених і часто використовуваних агрегатних функціях.

Загальна схема використання представлена нижче:

../_images/aggregate-function.svg

Середнє значення

avg(smallint) → numeric
avg(integer) → numeric
avg(bigint) → numeric
avg(numeric) → numeric
avg(real) → double precision
avg(double precision) → double precision
avg(interval) → interval

Обчислює середнє значення (середнє арифметичне) всіх ненульових вхідних значень у групі

Важливо

SQLite реалізує функцію avg таким чином, що вона повертає NULL тоді і тільки тоді, коли всі вхідні дані є нулями. З іншого боку, PostgreSQL видасть помилку у цьому випадку.

SELECT extract(year from date) as year,
       extract(month from date) as month,
       ceil(avg(amount)) as avg
FROM revenue
GROUP BY month, year
ORDER BY year, month
LIMIT 5;

year

month

avg

2013

11

7798

2013

12

6709

2014

1

6523

2014

2

6832

2014

3

10218

Кількість

count(*) → bigint

Обчислює кількість вхідних рядків

count("any") → bigint

Обчислює кількість вхідних рядків, в яких вхідне значення не дорівнює нулю

SELECT extract(year from date) as year,
       extract(month from date) as month,
       count(*)
FROM revenue
GROUP BY month, year
ORDER BY year, month
LIMIT 5;

year

month

count

2013

11

5

2013

12

7

2014

1

5

2014

2

8

2014

3

5

Максимальне значення

max(see text) → same as input type

Обчислює максимальне з ненульових вхідних значень. Доступно для будь-яких чисел, рядків, дати/часу або перелічуваних типів.

SELECT extract(year from date) as year,
       extract(month from date) as month,
       max(amount)
FROM revenue
GROUP BY month, year
ORDER BY year, month
LIMIT 5;

year

month

max

2013

11

12383

2013

12

12264

2014

1

8853

2014

2

12919

2014

3

14661

Мінімальне значення

min(see text) → same as input type

Обчислює мінімальне з ненульових вхідних значень. Доступно для будь-якого числового, рядкового, дати/часу або перелічуваного типу.

SELECT extract(year from date) as year,
       extract(month from date) as month,
       min(amount)
FROM revenue
GROUP BY month, year
ORDER BY year, month
LIMIT 5;

year

month

min

2013

11

3365

2013

12

3564

2014

1

2636

2014

2

3442

2014

3

2654

Сума значень

sum(smallint) → bigint
sum(integer) → bigint
sum(bigint) → numeric
sum(numeric) → numeric
sum(real) → real
sum(double precision) → double precision
sum(interval) → interval
sum(money) → money

Обчислює суму ненульових значень

SELECT extract(year from date) as year,
       extract(month from date) as month,
       sum(amount)
FROM revenue
GROUP BY month, year
ORDER BY year, month
LIMIT 5;

year

month

сума

2013

11

38988

2013

12

46958

2014

1

32611

2014

2

54652

2014

3

51087