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?

Create a view when you have to show modified column values from a table. — klin
– klin, Commented Jan 30, 2021 at 2:55
@klin you mean the view on the front-end? Yes, it has been "discussed". No use. They want it in the database. — Beki
– Beki, Commented Jan 30, 2021 at 3:09

Gab · Accepted Answer · 2021-01-30 02:41:30Z

5

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

edited Jan 30, 2021 at 2:41

answered Jan 30, 2021 at 2:33

Gab

3,5381 gold badge13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Beki Over a year ago

Yes, explaining stuff to your clients and advising them reasonably against whatever they want in Japan is often considered rude. @Gab thank you. Your answer solves my problem.

Gordon Linoff · Accepted Answer · 2021-01-30 02:37:33Z

5

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.

answered Jan 30, 2021 at 2:37

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Beki Over a year ago

stackoverflow only allows me to accept one answer. I am really grateful for both of your answers. If there is a way to accept multiple answers, I am all up for it. I actually used your answer. Let me know if there is any other way to thank you.

bobflux · Accepted Answer · 2021-01-30 13:09:47Z

3

As Gab says that's a pretty cumbersome thing to do. If you also want to keep a normal primary key for internal use in your app, here's a solution:

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

Sure, you get an extra index gobbling up RAM and disk space for nothing. But, when the client then inevitably asks you "I want to update the unique identifier" in a few months...

Or even worse, "why are there holes in the sequence can't you make it so there are no holes"...

...then you won't have to update ALL the relations in all the tables...

answered Jan 30, 2021 at 13:09

bobflux

11.6k3 gold badges31 silver badges33 bronze badges

3 Comments

Beki Over a year ago

will definitely do. Thank you @bobflux

bobflux Over a year ago

required viewing lol youtube.com/watch?v=UMXs9i201AQ

Gab Over a year ago

pretty clever @bobflux

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related