Здравствуйте. То, о чем вы говорите, называется GUID, но какого-то унифицированного одного алгоритма его генерации для всех нет. Но в postgres есть расширения, которые могут вам помочь и вам не придется генерить его на уровне приложения.
Эта суперкороткая статья может быть вам полезна:
https://postgrespro.ru/docs/postgrespro/9.5/dataty... и дать начальный стимул и понимание куда копать дальше. Удачи )
Update (29.01.20):
Сразу к делу и примерам. Сделать это можно, например, так:
-- Начнем со схемы данных. На всякий случай проиллюстрирую как может выглядеть идея генерации guid базой, с которой
-- я в самом начале и начал. Но вам, наверное, не будет смысла эту функцию использовать, т.к. сценарий использования
-- у вас другой.
--
-- Обратите также внимание, что я поменял тип на BIGINT. У меня была гипотеза, что вы используете TEXT, т.к., возможно,
-- не знали про BIGINT. Недостаток TEXT в том, что Postgres не построит по нему индекс и выборки по id будут все
-- медленнее и медленнее. Я бы порекомендовал сразу отрефакторить все так, чтобы использовался именно BIGINT.
--
-- Все поля также назвал английскими словами, т.к. это можно считать индустриальным стандартом. NOT NULL добавил
-- по наитию: логика подсказывает, что PRIMARY KEY у каждой таблице обязателен, как и данные. Просто для полноты.
-- Начнем с функции, которая будет генерировать нам ID для примера.
CREATE OR REPLACE FUNCTION guid()
RETURNS BIGINT AS
$BODY$
BEGIN
-- Количество секунд с начала эпохи Линукса и домножаем на какой-то множитель, чтобы увеличить точность
-- и получить 1580307917143.431 вместо 1580307917.143431
RETURN CAST(EXTRACT(EPOCH FROM NOW()) * 1000 AS BIGINT);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-- Теперь перейдем к самой схеме данных и создадим ее.
CREATE TABLE users (
id BIGINT PRIMARY KEY NOT NULL DEFAULT guid(),
pseudonym TEXT NOT NULL
);
CREATE TABLE posts (
id BIGINT PRIMARY KEY NOT NULL DEFAULT guid(),
content TEXT NOT NULL
);
CREATE TABLE comments (
id BIGINT PRIMARY KEY NOT NULL DEFAULT guid(),
text TEXT NOT NULL
);
-- Для того чтобы повесить CONSTRAINT на id целевых таблиц, понадобится сделать VIEW, котоый будет содержать все
-- id из всех этих таблиц, а также функцию, которую мы сможем использовать для CONSTRAINT. По сути, делаем то же,
-- что делали бы на уровне приложения, но на уровне БД.
CREATE OR REPLACE VIEW all_ids AS
SELECT id FROM users UNION
SELECT id FROM posts UNION
SELECT id FROM comments;
-- Теперь перейдем к функции, которая и будет выполнять всю грязную работу.
CREATE OR REPLACE FUNCTION is_unique_id (BIGINT)
RETURNS BOOLEAN AS 'SELECT CASE WHEN
(SELECT 1
FROM all_ids
WHERE id = $1) > 0
THEN FALSE ELSE TRUE END'
LANGUAGE 'sql' WITH (isstrict);
-- Осталось только повесить CONSTRAINT
ALTER TABLE users ADD CONSTRAINT id CHECK (is_unique_id(id));
ALTER TABLE posts ADD CONSTRAINT id CHECK (is_unique_id(id));
ALTER TABLE comments ADD CONSTRAINT id CHECK (is_unique_id(id));
-- А теперь внимание. Теперь вам придется быть очень внимательным при добавлении таблиц, в рамках которых id должен
-- быть уникален. При добавлении новой таблцы будет необходимо:
-- 1. Пересоздать VIEW, дополнив запрос новыми таблицами.
-- 2. Не забыть повесить аналогичный CONSTRAINT на новую таблицу.
--
-- Также обратите внимание, что при вставке новой записи в любую из таблиц будет проверяться весь созданный VIEW
-- и очень важно чтобы это был не полнотекстовый поиск, а работали индексы, поэтому так важно отрефакторить все в BIGINT.
-- Пришло время тестирования. Вставляем данные.
INSERT INTO users (pseudonym) VALUES ('Первый');
INSERT INTO users (pseudonym) VALUES ('Второй');
INSERT INTO posts (content) VALUES ('О том как надо');
INSERT INTO posts (content) VALUES ('О том как не надо');
INSERT INTO comments (text) VALUES ('Я думаю что решение...');
INSERT INTO comments (text) VALUES ('Хорошо я пропробую сделать...');
-- И глянем что получилось.
SELECT * FROM users;
-- 1580326610797 Первый
-- 1580326611809 Второй
SELECT * FROM posts;
-- 1580326613690 О том как надо
-- 1580326613712 О том как не надо
SELECT * FROM comments;
-- 1580326613779 Я думаю что решение...
-- 1580326613797 Хорошо я пропробую сделать...
-- Время X: тестируем нашу проверку, пытаясь вставить в таблицу users id из таблицы comments:
INSERT INTO users (id, pseudonym) VALUES (1580326613779, 'tiabc');
-- [23514] ERROR: new row for relation "users" violates check constraint "id" Detail: Failing row contains (1580326613779, tiabc)
-- Profit!
Что хочу сказать с точки зрения проектирования архитектуры и вообще. Использование любого нового инструмента должно быть обосновано. Как правило, сложная схема БД ведет к сложностям в поддержке и к тому, что какие-то вещи забывают обновляться, в отличие от уровня приложения.
В вашем же случае, честно говоря, схему очень сильно хочется упростить, а не усложнить. Хочется добавить поле created_at с DEFAULT CURRENT_TIMESTAMP() и хочется добавить реляционные связи. Либо же в принципе уйти на нереляционную БД и задать структуру сущностей там (что опять же должно быть обосновано).
И дальше именно на уровне приложения делать эту выборку, т.к. это упростит поддержку кода, а с точки зрения трудозатрат ваших и БД при выборках и вставках будет то же самое.
Помимо этого, поскольку вы делаете процессинг на основе id и его порядковых номеров, не могу не порекомендовать обратить внимание на одни из базовых принципов
проектирования ПО. Конечно, вся картина не видна, но я тут вижу нарушение буквы S (Single Responsibility), что id у вас и за порядок отвечает, и за уникальность (да еще и между несколькими таблицами). Хочется как-то их развязать. Со временем жизни продукта это часто оказывается полезно.
Опять же, беспокойств о том, что вы хотите достичь, достаточно много, но если отвечать именно конкретно на ваш вопрос и давать дополнительно какие-то рекомендации, то как-то так )
Желаем удачи, образования и всех благ )
С уважением,
Иван Томилов
CEO of Athanor
