Недавно вышла отличная книга PostgreSQL Mistakes and How to Avoid Them от Jimmy Angelakos — системного архитектора, практика и давнего участника сообщества PostgreSQL. Книга подробно разбирает распространённые ошибки, с которыми сталкиваются разработчики и администраторы при работе с PostgreSQL, и предлагает практичные решения: от тонкостей конфигурации и миграции до антипаттернов в SQL и выбора типов данных.

Я перевёл одну из ключевых глав этой книги — про неправильное использование типов данных. В ней подробно объясняется, почему, например:

• timestamp without time zone может ломать логику расчёта интервалов;

• money — это не то, чем кажется (и почему он опасен);

• char(n) и varchar(n) не дают ожидаемой экономии и даже вредны;

• serial — это прошлый век, а identity — настоящее.

Глава будет полезна всем, кто работает с PostgreSQL в проде — особенно backend-разработчикам, независимо от языка и фреймворка. Если вы проектируете схемы БД, пишете SQL-запросы или просто хотите избежать неприятных грабель — стоит прочитать.

---

В этой главе рассматривается:

• Как избежать использования неподходящего типа данных

• Подводные камни, связанные с часовыми поясами и переходом на летнее/зимнее время

• Типы данных, которые следует избегать вовсе

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

3.1 TIMESTAMP (WITHOUT TIME ZONE)

Начнём с типов данных, которые используются для хранения даты и времени. Тип timestamp — один из самых популярных, так как позволяет сохранять дату и время одновременно. Если вы просто укажете timestamp, PostgreSQL по умолчанию интерпретирует это как timestamp without time zone, поскольку такое поведение предписано стандартом SQL.

Наши друзья из Frogge Emporium решили использовать timestamp для хранения времени открытия и закрытия обращений в службу поддержки. Из-за особенностей работы их системы поддержки клиентов, время обращения сохраняется в локальном времени клиента.

Рассмотрим, к примеру, этот тикет, созданный клиентом с западного побережья США 28 октября 2023 года в 16:00 по тихоокеанскому летнему времени (на 8 часов отстаёт от всемирного координированного времени, UTC-8) и закрытый оператором службы поддержки (который находился в Великобритании) 29 октября 2023 года в 09:00 по Гринвичу (GMT или UTC+0):

-[ RECORD 1 ]-----------------------------------------------------
id        | 132591
content   | Kindly close our account, as we don't need it anymore. Thank you
status    | 20
opened_at | 2023-10-28 16:00:00
closed_at | 2023-10-29 09:00:00

Если мы попытаемся вычислить, сколько времени ушло на закрытие тикета (например, для целей контроля качества), мы получим следующее:

SELECT pg_typeof(closed_at - opened_at), closed_at - opened_at
FROM support.tickets
WHERE id = 132591;

Результат будет таким:

pg_typeof | ?column?
----------+----------
interval  | 17:00:00
(1 row)

Семнадцать часов? Это очевидно неверно. С точки зрения клиента, тикет должен был быть закрыт 29 октября 2023 года в 02:00 по тихоокеанскому времени (UTC-8), то есть прошло всего 10 часов.

Почему мы получили такой результат? Дело в том, что TIMESTAMP WITHOUT TIME ZONE, также известный как наивный timestamp, не сохраняет информацию о часовом поясе. В результате любые арифметические операции (такие как вычитание) между временными метками, записанными в разных часовых поясах, теряют смысл — результат будет неверным.

Теперь предположим, что это поведение было учтено, и разработчики приложения договорились с администратором базы данных сохранять все значения времени в часовом поясе Europe/London, независимо от местоположения пользователя приложения. Таким образом, все значения времени будут в одном часовом поясе, и вычисления можно выполнять корректно. Тогда запись выглядела бы так:

-[ RECORD 1 ]-----------------------------------------------------
id        | 132591
content   | Kindly close our account, as we don't need it anymore. Thank you
status    | 20
opened_at | 2023-10-29 00:00:00
closed_at | 2023-10-29 09:00:00

Время, в течение которого тикет оставался открытым:

pg_typeof | ?column?
----------+----------
interval  | 09:00:00
(1 row)

Упс! Дело в том, что в Лондоне переход на зимнее время (DST) завершился в 2:00 утра 29 октября, и часы были переведены на час назад. Таким образом, 29 октября фактически было дважды 1:00 ночи: сначала по британскому летнему времени (BST, UTC+1), затем по Гринвичу (GMT, UTC+0).

Даже если мы обойдём проблему с переходом на зимнее время, используя везде UTC, всё равно неправильно сохранять такие значения с помощью TIMESTAMP. База данных не знает, что это именно UTC, и не сможет корректно пересчитывать время между часовыми поясами.

Самое простое решение — использовать TIMESTAMPTZ или TIMESTAMP WITH TIME ZONE в качестве типа данных. Таким образом, мы можем сохранять временные метки в любом часовом поясе, а база данных будет автоматически учитывать все преобразования при вычислениях. Теперь наша таблица и данные будут выглядеть так при корректной вставке:

INSERT INTO support.tickets (content, status, opened_at, closed_at) VALUES
('Kindly close our account, as we don''t need it anymore. Thank you',
 '10',
 '2023-10-28 16:00 PDT',
 '2023-10-29 09:00 GMT');

Примечание: Обратите внимание, что в предыдущем запросе мы использовали двойную кавычку (''), чтобы вставить апостроф в строку, не нарушая её синтаксис (строка ограничена одинарными кавычками).

Если просматривать эту запись из клиента базы данных, настроенного на часовой пояс Europe/London, она будет выглядеть так:

-[ RECORD 1 ]-----------------------------------------------------
id        | 1
content   | Kindly close our account, as we don't need it anymore. Thank you
status    | 10
opened_at | 2023-10-29 00:00:00+01
closed_at | 2023-10-29 09:00:00+00

Вычисление длительности даёт корректный результат:

pg_typeof | ?column?
----------+----------
interval  | 10:00:00
(1 row)

TIMESTAMP WITH TIME ZONE хранит именно момент времени, что делает арифметику над временем осмысленной — мы точно знаем, сколько времени прошло между двумя событиями. В отличие от него, TIMESTAMP WITHOUT TIME ZONE — это скорее снимок календаря и часов: дата и время есть, но без контекста.

TIMESTAMPTZ также удобен тем, что, хотя и отображается в часовом поясе клиента, PostgreSQL позволяет привести его к нужному часовому поясу по запросу. Например:

SELECT opened_at AT TIME ZONE 'PDT' AS "Ticket opened",
       closed_at AT TIME ZONE 'PDT' AS "Ticket closed"
FROM support.tickets;

Результат:

 Ticket opened       | Ticket closed
---------------------+---------------------
 2023-10-28 16:00:00 | 2023-10-29 02:00:00

Кроме того, вы не тратите дополнительное место для хранения: оба типа — TIMESTAMP — занимают по 8 байт. Благодаря компактному хранению вы можете использовать TIMESTAMPTZ как естественный первичный ключ для данных временных рядов. Нужен ли вам действительно суррогатный (искусственный) ключ, если TIMESTAMPTZ уже позволяет однозначно идентифицировать тикеты? Вдобавок этот тип прекрасно подходит для партиционирования и индексирования, так что вы можете использовать его как практический ключ партиции и на его основе строить действительно эффективные запросы.

Подводя итог: TIMESTAMPTZ — это предпочтительный тип данных для хранения конкретного момента времени. Наивный TIMESTAMP бесполезен для вычислений с датами и временем и не даёт никаких преимуществ ни в производительности, ни в объёме хранения по сравнению с типом, учитывающим часовой пояс.

3.2 TIME WITH TIME ZONE

Для некоторых данных достаточно зафиксировать только время без даты. Поэтому можно легко предположить, что TIME WITH TIME ZONE или TIMETZ — это хороший выбор для такого поля. Однако, как мы уже видели раньше, пропуск информации о часовом поясе иногда может вызывать проблемы.

Предположим, что в компании Frogge Emporium есть таблица, где хранятся данные о потреблении электроэнергии каждым филиалом. Показания собираются с помощью «умного» счётчика. Этот счётчик фиксирует время каждого измерения, и при сохранении в базу это время записывается в колонку типа TIMETZ. Для филиала 41 у нас есть следующие последовательные показания:

branch_id | reading_time           | reading  | unit
----------+------------------------+----------+------
       41 | 01:17:27.612383+01     | 54921.8  | kWh
       41 | 01:17:21.356247+00     | 54988.0  | kWh

Первое и второе измерения были записаны по разные стороны границы перехода на зимнее время. Мы знаем, что они были сделаны примерно с разницей в 1 час, но посмотрим, что произойдёт, если мы попытаемся вычесть одно значение из другого:

SELECT '01:17:27.612383+01'::timetz - '01:17:21.356247+00'::timetz;

Результатом будет ошибка:

ERROR: operator does not exist: time with time zone - time with time zone
LINE 1: select '01:17:27.612383+01'::timetz - '01:17:21.356247+00'::...
                                           ^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

Мы видим, что значение смещения зависит от перехода на летнее/зимнее время, и мы не можем выполнять арифметические операции с таким типом. Это ставит под сомнение полезность типа TIMETZ. Если вместо него использовать наивный тип TIME, то мы столкнёмся с теми же проблемами при вычислениях по разные стороны границы перехода на летнее/зимнее время, как и с типом TIMESTAMP WITHOUT TIME ZONE.

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

Вкратце: TIMETZ включён в PostgreSQL только ради соответствия SQL-стандарту. Поскольку он также занимает 8 байт, нет никакой причины его использовать — вместо него рекомендуется использовать TIMESTAMPTZ.

3.3 CURRENT_TIME

Хорошо, CURRENT_TIME — это не совсем тип данных, но он хорошо сочетается с предыдущим разделом о TIME WITH TIME ZONE. Функция current_time возвращает текущее время суток в виде значения типа TIME WITH TIME ZONE:

SELECT CURRENT_TIME, pg_typeof(CURRENT_TIME);

Результат:

 current_time       | pg_typeof
--------------------+--------------------
 20:46:27.094953+00 | time with time zone

Если вы решите использовать CURRENT_TIME, вы столкнётесь с теми же проблемами, что и при работе с TIMETZ. Более чистое решение — использовать тип временной метки (timestamp), который фиксирует конкретный момент времени. Если впоследствии вам не нужна дата, вы можете просто отбросить её с помощью EXTRACT(), date_part() или программно — на стороне приложения. Объём памяти, занимаемый полем, всё равно будет одинаковым.

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

Таблица 3.1. Функции PostgreSQL для работы с датой/временем и их возвращаемые типы

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

Больше информации о функциях даты и времени в PostgreSQL можно найти в официальной документации:
https://www.postgresql.org/docs/current/functions-datetime.html

3.4 CHAR(n)

Теперь рассмотрим символьные типы PostgreSQL, то есть то, как хранятся строки текста в базе данных. Начнём с CHAR(n) или CHARACTER(n). Это текстовый тип фиксированной длины с заполнением пробелами. Его длина всегда равна n, как указано в определении. Если строка короче n символов, оставшееся пространство дополняется пробелами.

Примечание: Поскольку это символьный тип с фиксированной длиной и заполнением пробелами, он также известен как тип BPCHAR(n). Ранее он использовался исключительно как внутреннее обозначение типа, но начиная с PostgreSQL 16, это обозначение стало документированным.

Пример: строка 'postgres', приведённая к типу CHAR(10), будет выглядеть так:

SELECT 'postgres'::CHAR(10);
-- bpchar
-- ---------
-- postgres
-- (1 row)

Ниже показано более наглядно, что происходит (пробелы обозначены символом ␣):

p o s t g r e s ␣ ␣

Поскольку 'postgres' содержит 8 символов, в конец добавляются 2 пробела, чтобы довести длину до 10. Эти пробелы игнорируются при сравнении строк, так как считаются семантически незначимыми, например:

SELECT 'postgres'::CHAR(10) = 'postgres'::CHAR(20);
-- ?column?
-- ---------
-- t
-- (1 row)

Но будьте осторожны: при использовании LIKE и регулярных выражений (regex) пробелы не игнорируются!

SELECT 'postgres'::CHAR(10) LIKE '%ostgres';
-- ?column?
-- ---------
-- f
-- (1 row)

Оператор LIKE не находит совпадение, потому что строка заканчивается двумя пробелами.

Аналогично, регулярное выражение, ищущее окончание строки на 'ostgres', тоже не сработает:

SELECT 'postgres'::CHAR(10) ~ '.*ostgres$';
-- ?column?
-- ---------
-- f
-- (1 row)

Та же ситуация возникает и с POSIX-выражениями, использующими оператор ~.

Кроме того, есть ещё одна неприятная особенность: если привести строку, длина которой превышает n, к типу CHAR(n), она будет усечена без предупреждений или ошибок, поскольку это предусмотрено SQL-стандартом:

SELECT 'I heart PostgreSQL'::CHAR(10);
-- bpchar
-- -------------
-- I heart Po
-- (1 row)

Даже если вам нужно строго ограничить длину строки n символами, использование CHAR(n) — неподходящий способ, так как он без проблем принимает строки короче n.

Что ещё хуже — внутри PostgreSQL тип CHAR(n) не хранится как поле фиксированной длины. Поскольку символы могут занимать более одного байта (в зависимости от кодировки), сохраненная строка на диске представлена в виде значения переменной длины. В результате вы можете тратить место на диске впустую, сохраняя ненужные пробелы, так как они сохраняются явно. Это также влияет на производительность: при работе с таким типом серверу приходится дополнительно удалять пробелы, чтобы корректно сравнивать строки.

Наконец, индексы, созданные по колонкам с типом CHAR(n), могут не работать в запросах, где используется значение типа TEXT, переданное из клиента PostgreSQL.

Итог: практически во всех случаях лучше использовать TEXT — текстовый тип переменной длины без ограничения (хотя он и может называться VARCHAR без указания лимита). Тип TEXT даёт вам больше гибкости и может обеспечить лучшую производительность по сравнению с фиксированной длиной CHAR.

3.5 VARCHAR(n)

CHARACTER VARYING(n) или VARCHAR(n) — это текстовое поле переменной длины с ограничением по количеству символов. Можно хранить любую строку длиной до n. При попытке вставить более длинную строку произойдёт ошибка:

CREATE TEMP TABLE test1 (col VARCHAR(5));
-- CREATE TABLE

INSERT INTO test1 VALUES ('12345678');
-- ERROR:  value too long for type character varying(5)

Однако, если лишние символы в строке — это пробелы, то строка будет тихо усечена до длины n без сообщения об ошибке, как того требует стандарт SQL:

INSERT INTO test1 VALUES ('1234 ');
-- INSERT 0 1

Мы видим, что строка '1234 ' (пять символов, заканчивающаяся пробелом) успешно вставлена. Такое поведение схоже с CHAR(n). Аналогично, если более длинное значение привести к VARCHAR(n), оно также будет усечено без предупреждения:

SELECT 'Just use TEXT'::VARCHAR(10);
-- varchar
-- ------------
-- Just use T

В отличие от CHAR(n), тип VARCHAR(n) не добавляет пробелы в конец строки, что избавляет от проблем с лишним занятым местом и странным поведением при сравнении строк.

Тем не менее, ограничение длины всё равно не даёт никаких преимуществ — данные на диске хранятся точно так же, как и при использовании TEXT. Более того, бывают ситуации, когда это ограничение создаёт проблемы. Например, если вы установили для столбца company_name тип VARCHAR(50), а затем кто-то попытается ввести полное название компании длиной 52 символа:

SELECT length('Peterson''s and Sons and Friends Bits & Parts Limited');
-- length
-- ---------
-- 52

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

Чтобы изменить длину столбца, придётся использовать ALTER TABLE, что может потребовать блокировки таблицы и усложнит миграцию. Причём уменьшить длину обратно вообще невозможно.

Если вам действительно нужно строгое ограничение по длине — например, по требованиям комплаенса — лучше использовать ограничение CHECK, которое легко изменить:

DROP TABLE test1;

CREATE TEMP TABLE test1 (col TEXT CHECK(length(col)<=5));
-- CREATE TABLE

INSERT INTO test1 VALUES ('12345678');
-- ERROR:  new row for relation "test1" violates check constraint "test1_col_check"
-- DETAIL:  Failing row contains (12345678).

Альтернативно, можно использовать CREATE DOMAIN поверх TEXT, чтобы повторно не описывать однотипные ограничения. Домены — это пользовательские типы с уже заданными ограничениями, что удобно при повторном использовании.

Итог: не используйте VARCHAR(n), он может только создать проблемы, не давая при этом никаких преимуществ. Используйте TEXT.

Примечание: Дополнительную информацию о типах символов в PostgreSQL и их производительности можно найти в официальной документации:
https://www.postgresql.org/docs/current/datatype-character.html#DATATYPE-CHARACTER

Тип BPCHAR

Также начиная с PostgreSQL 16 документирован тип BPCHAR без указания длины. Это символьный тип с неограниченной переменной длиной, в котором пробелы в конце строки не считаются значимыми.

Подобно VARCHAR, тип BPCHAR принимает строки любой длины, но трактует пробелы в конце как незначимые. Однако это означает, что BPCHAR ведёт себя значительно иначе, чем VARCHAR.

Пример:

CREATE TEMP TABLE trailing_ws (v VARCHAR, b BPCHAR);
-- CREATE TABLE

INSERT INTO trailing_ws VALUES ('vvv ', 'bbb ');
-- INSERT 0 1

SELECT v, ('vvv' = v), b, ('bbb' = b) FROM trailing_ws;
-- v   | ?column? | b   | ?column?
-- ----+----------+-----+----------
-- vvv | f        | bbb | t
-- (1 row)

Из этого видно, что BPCHAR игнорирует пробелы в конце строки и считает 'bbb ' эквивалентной 'bbb', в то время как VARCHAR считает 'vvv ' и 'vvv' разными строками.

3.6 MONEY

Наши друзья из Frogge Emporium хотят сохранять суммы платежей, которые они получают. Естественно, они рассматривают возможность использования типа данных MONEY. Этот тип может хранить денежные суммы с определённой дробной точностью, заданной в базе данных. Frogge Emporium создаёт следующую таблицу:

CREATE TABLE erp.payments (
    id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    tstamp timestamp with time zone NOT NULL,
    amount money NOT NULL,
    invoice bigint NOT NULL
);

Вставим пример платежа:

INSERT INTO erp.payments (tstamp, amount, invoice)
VALUES (now(), 99.99, 0);

Результат выполнения:

INSERT 0 1

TABLE erp.payments;
 id |        tstamp         | amount  | invoice
----+------------------------+---------+---------
  1 | 2023-11-20 21:25:12... | £99.99  |       0
(1 row)

Разберёмся, что здесь произошло. В базе данных, используемой автором, локаль PostgreSQL по параметру LC_MONETARY унаследовала системную настройку en_GB, что означает британский фунт стерлингов (GBP). Поэтому база данных предположила, что мы вставили значение £99.99 именно в этой валюте. Это уже тревожный сигнал: тип MONEY на самом деле не хранит тип валюты — он просто следует за тем, что настроено на сервере.

Но допустим, мы захотим задним числом применить 25% скидку ко всем платежам клиента как знак благодарности. Это должно быть просто — ведь мы знаем, что:

SELECT 99.99 * 0.25;

?column?
---------
24.9975
(1 row)

Теперь применим это к значению в таблице:

SELECT amount * 0.25
FROM erp.payments
WHERE id = 1;

?column?
---------
£25.00
(1 row)

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

Более того, оказывается, что MONEY — вопреки ожиданиям — на самом деле отвратительный тип для хранения денежных значений. Более того, он спокойно принимает мусор на входе:

SELECT '1,123,456.,7.8.1,0,9'::MONEY;

money
---------------
£12,345,678.11
(1 row)

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

Примечание: Помните, что не следует использовать никакие типы с плавающей запятой, такие как real или double precision, для хранения денежных значений, поскольку они подвержены ошибкам округления. По определению, это неточные числовые типы, и их приближённые значения не подходят для хранения точных сумм, таких как деньги.

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

Совет
В PostgreSQL нет никакой разницы между NUMERIC и DECIMAL.

3.7 Тип данных SERIAL

SERIAL — это расширение PostgreSQL, то есть нестандартный способ (вне спецификации SQL) попросить базу данных создать автоинкрементируемое целочисленное поле. То же самое касается и его "старшего брата" BIGSERIAL, который автоинкрементирует BIGINT. Раньше это было полезным сокращением, но сегодня оно приносит больше проблем, чем пользы. Чтобы разобраться, давайте посмотрим, как работает таблица с первичным ключом SERIAL:

CREATE TEMP TABLE transactions (
    id SERIAL PRIMARY KEY,
    amount numeric NOT NULL
);

Результат команды \d transactions:

           Table "pg_temp_4.transactions"
 Column |  Type   | Collation | Nullable |           Default
--------+---------+-----------+----------+-----------------------------
 id     | integer |           | not null | nextval('transactions_id_seq'::regclass)
 amount | numeric |           | not null |
Indexes:
    "transactions_pkey" PRIMARY KEY, btree (id)

Как и ожидалось, автоматически создаётся последовательность transactions_id_seq для генерации значений в колонке id.

Теперь предоставим другому пользователю доступ к этой таблице:

CREATE USER jimmy;
CREATE ROLE;

GRANT ALL ON TABLE transactions TO jimmy;
GRANT;

Проверим, сможет ли jimmy вставить данные в таблицу, переключившись на его роль:

SET ROLE jimmy;
-- SET выполнено успешно

INSERT INTO transactions (amount) VALUES (10.00);
-- ОШИБКА: permission denied for sequence transactions_id_seq

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

Ещё хуже то, что если вы создадите похожую таблицу с помощью CREATE TABLE … LIKE, новая таблица будет использовать ту же самую последовательность!

CREATE TEMP TABLE new_tx (LIKE transactions INCLUDING ALL);

Команда \d new_tx покажет:

       Table "pg_temp_4.new_tx"
 Column |  Type   | Collation | Nullable |           Default
--------+---------+-----------+----------+-----------------------------
 id     | integer |           | not null | nextval('transactions_id_seq'::regclass)
 amount | numeric |           | not null |
Indexes:
    "new_tx_pkey" PRIMARY KEY, btree (id)

Такой результат неожиданный и, скорее всего, нежелательный. Кроме того, вы не сможете удалить оригинальную таблицу, потому что новая зависит от её последовательности. Чтобы избежать этих проблем, используйте identity columns вместо SERIAL. Пример:

DROP TABLE new_tx;

CREATE TEMP TABLE new_tx (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    amount numeric not null
);

Команда \d new_tx:

       Table "pg_temp_4.new_tx"
 Column |  Type   | Collation | Nullable |              Default
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | generated by default as identity
 amount | numeric |           | not null |
Indexes:
    "new_tx_pkey" PRIMARY KEY, btree (id)

С identity column вам не нужно знать имя последовательности, чтобы управлять ею. Например:

ALTER TABLE new_tx ALTER COLUMN id RESTART WITH 1000;

INSERT INTO new_tx (amount) VALUES (10.00);
-- INSERT 0 1

TABLE new_tx;
 id   | amount
------+--------
1000  | 10.00
(1 row)

Если вы используете конструкцию CREATE TABLE … LIKE с таблицей, в которой определены identity колонки, то новая таблица получит собственные новые последовательности. Таким образом, проблем не возникнет.

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

3.8 XML

Для начала стоит признать, что PostgreSQL действительно поддерживает тип данных XML, хотя это решение и может показаться неудачным. Многие из доводов против использования XML вне базы данных применимы и здесь. Если говорить о проблемах, не зависящих от СУБД, то как формат документов XML сочетает в себе худшие стороны обоих миров: по сути, он настолько же или даже менее эффективен, чем обычный текстовый файл, и зачастую лишь едва ли читаем человеком — чуть выше по удобству, чем бинарный формат.

Вот несколько более практических причин, почему не стоит использовать XML:

• Он требует наличия единственного корневого элемента, из-за чего, например, объединение XML-фрагментов требует дополнительного парсинга и становится гораздо сложнее.

• Он использует концепцию пространств имён (namespaces), что влечёт за собой дополнительные проблемы — например, необходимость обработки конфликтов пространств имён.

• Как текстовый формат, он допускает неконсистентность при представлении чисел. Типичный пример — числа, введённые в разных локалях, где правила записи и разделители могут отличаться (например, точка против запятой).

• Он одновременно поддерживает множество способов экранирования символов, и правила экранирования зависят от того, где именно находится текст — внутри обычного текста, атрибута, комментария, CDATA и т.п.

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

И всё это — ещё не повод говорить о главной проблеме: использование XML может быть крайне неудачным выбором из-за проблем с управлением памятью при его разборе. Например, вам придётся отдельно рассматривать плюсы и минусы DOM-парсинга против SAX/стримингового парсинга и учитывать соответствующие ограничения.

В PostgreSQL существуют и дополнительные проблемы, связанные с типом данных XML, в частности:

• Определение типа документа (DOCTYPE, DTD) — представляет собой проблему, так как требует внешнего или встроенного определения DTD (что совершенно не имеет смысла в контексте базы данных). PostgreSQL не валидирует входные значения по DTD и не поддерживает другие языки схем XML, такие как XML Schema.

• Кодировка символов, указанная в XML-декларации, может отличаться от той, в которой текст XML был фактически сохранён. Ситуация усложняется, если на клиенте и сервере используются разные кодировки, как и в самих XML-данных. По умолчанию PostgreSQL преобразует все символьные данные, передаваемые между клиентом и сервером, включая строки значений XML, что может сделать XML-декларацию недействительной. Вы сами должны гарантировать, что все три кодировки (клиент, сервер, XML) синхронизированы.

• Языковой атрибут xml:lang также не имеет значения внутри PostgreSQL и игнорируется.

• Для типа XML не существует операторов сравнения. Нет надёжного алгоритма сравнения XML-структур даже вне PostgreSQL. Поэтому нельзя, к примеру, выполнить WHERE xml_column = '<some-value>'. Единственный вариант — привести XML к TEXT и сравнивать строки, что крайне неудобно.

• Запросы к XML возможны через XPath 1.0, но это бессмысленно, так как поле не индексируемо. В отличие от этого, JSONB хорошо индексируется с помощью GIN и GiST.

• XML очень многословен, содержит много избыточных байт, что приводит к перерасходу хранилища.

• В PostgreSQL XML не даёт никаких преимуществ по сравнению с гораздо более компактным форматом JSON. Рассмотрим следующий пример, в котором даже отсутствуют стандартные заголовки XML-документа:

SELECT
  '<property>
     <key>color</key>
     <value>00000</value>
   </property>'::XML,
  pg_column_size(
   '<property>
      <key>color</key>
      <value>00000</value>
    </property>'::XML
  );

Результат:

xml                      | pg_column_size
-------------------------+----------------
<property>               | 72
  <key>color</key>       |
  <value>00000</value>   |
</property>              |
(1 row)

Для сравнения представим ту же самую информацию в более лаконичном формате:

SELECT
  '{"color":0}'::JSON,
  pg_column_size(
    '{"color":0}'::JSON
  );

Результат:

     json      | pg_column_size
---------------+----------------
 {"color":0}   | 15
(1 row)

Примечание: pg_column_size() — это функция, с помощью которой измеряется объём занимаемого в байтах пространства для конкретного значения данных.

---

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

Дополнительную информацию о JSONB можно найти в официальной документации PostgreSQL:
https://www.postgresql.org/docs/current/datatype-json.html

Резюме

• Нет смысла использовать TIMESTAMP (WITHOUT TIME ZONE), так как это может привести к ошибкам при вычислениях из-за отсутствия контекста часового пояса и перехода на летнее/зимнее время. Правильный тип для хранения временных меток как конкретных моментов времени — TIMESTAMP WITH TIME ZONE.

• TIMETZ и CURRENT_TIME имеют сомнительную полезность, так как часовые пояса сами по себе не имеют смысла без даты. Даже если нам не нужно отображать дату, всё равно предпочтительно использовать TIMESTAMPTZ.

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

• Типы SERIAL и BIGSERIAL фактически устарели и были вытеснены identity-колонками, которые обладают более предсказуемым поведением в плане владения последовательностями, их использования и понятности, к какой таблице они относятся.

• Использование ограниченных символьных типов CHAR(n) и VARCHAR(n) не экономит место. Более того, пробелы в CHAR(n) могут ухудшать производительность. Также вы рискуете столкнуться с ограничениями SQL или неожиданным поведением. Тип TEXT — предпочтительный выбор.

• XML — крайне неудачный выбор для хранения документов, если только вы не собираетесь просто копировать неизменяемые XML-данные в базу. Если вы планируете выполнять запросы или модифицировать данные, используйте JSON(B).