PostgreSQL: How to create a prefixed sequence as a unique identifier?

Question

One of my clients insists that I create a unique identifier that starts with a given prefix, then increments by one in a postgres table. For example, PREFIX000001.

I know postgres provides SERIAL, BIGSERIAL and UUID to uniquely identify rows in a table. But the client just does not listen. He wants it his way.

Here is the sample table (Excel representation) - something like unique_id column that auto generates on every INSERT command:

I really want to know whether this is technically possible in postgres. How should I go about this?

Answer 1

You could create a SERIAL or BIGSERIAL like you suggested but represent it with a string when reporting the data in the application (if the client would accept that):

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

For example:

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

Time: 2.704 ms

---

Otherwise you would have to do this:

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

I would advice you to try to make the client reconsider but it looks like you already tried that route

To whomever reads this in the future, please don't do this to your database, this is not good practice as @Beki acknowledged in his question

Answer 2

One method uses a sequence:

create sequence t_seq;

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

Here is a db<>fiddle.