Блог

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

Я знаю, что postgres предоставляет SERIAL BIGSERIAL и UUID уникальную идентификацию строк в таблице. Но клиент просто не слушает. Он хочет по-своему.

Вот пример таблицы (представление в Excel) — что-то вроде unique_id столбца, который автоматически генерируется при каждой INSERT команде:

Я действительно хочу знать, возможно ли это технически postgres . Как мне это сделать?

Вы могли бы создать SERIAL or BIGSERIAL , как вы предложили, но представить его строкой при сообщении данных в приложении (если клиент примет это):

  SELECT to_char(id, '"PREFIX0"FM0000000') AS unique_id, product_name, product_desc FROM table;
 

Например:

 SELECT to_char(123, '"PREFIX0"FM0000000') AS unique_id;
  unique_id  
----------------
 PREFIX00000123
(1 row)

Time: 2.704 ms
 

В противном случае вам пришлось бы сделать это:

 CREATE SEQUENCE my_prefixed_seq;

CREATE TABLE my_table (
    unique_id TEXT NOT NULL DEFAULT 'PREFIX'||to_char(nextval('my_prefixed_seq'::regclass), 'FM0000000'),
    product_name text,
    product_desc text
);

INSERT INTO my_table (product_name) VALUES ('Product 1');
INSERT INTO my_table (product_name) VALUES ('Product 2');
INSERT INTO my_table (product_name) VALUES ('Product 3');
 

->

 SELECT * FROM my_table;
   unique_id   | product_name | product_desc 
--------------- -------------- --------------
 PREFIX0000004 | Product 1    | {NULL}
 PREFIX0000005 | Product 2    | {NULL}
 PREFIX0000006 | Product 3    | {NULL}
(3 rows)

Time: 3.595 ms
 

Я бы посоветовал вам попытаться заставить клиента пересмотреть, но, похоже, вы уже пробовали этот маршрут

Для тех, кто прочитает это в будущем, пожалуйста, не делайте этого с вашей базой данных, это не очень хорошая практика, как признал @Beki в своем вопросе

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

 CREATE OR REPLACE FUNCTION add_prefix(INTEGER) RETURNS text AS
$ select 'PREFIX'||to_char($1, 'FM0000000'); $
LANGUAGE sql immutable;

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    unique_id TEXT UNIQUE NOT NULL GENERATED ALWAYS AS 
        (add_prefix(id)) STORED,
    product_name text
);

INSERT INTO my_table (product_name) VALUES ('Product 1');
INSERT INTO my_table (product_name) VALUES ('Product 2');
INSERT INTO my_table (product_name) VALUES ('Product 3');

 select * from my_table;
 id |   unique_id   | product_name
---- --------------- --------------
  1 | PREFIX0000001 | Product 1
  2 | PREFIX0000002 | Product 2
  3 | PREFIX0000003 | Product 3
 

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

Или, что еще хуже, «почему в последовательности есть дыры, вы не можете сделать так, чтобы дыр не было»…

… тогда вам не придется обновлять ВСЕ отношения во всех таблицах…

Один метод использует последовательность:

 create sequence t_seq;

create table t (
    unique_id varchar(255) default ('PREFIX' || lpad(nextval('t_seq')::text, 6, '0'));
)
 

Вот скрипка db<> .