How can I create a Serial ID with as a string with common prefix (ie tag_1, tag_2, tag_3) in Postgres

user_4808079 0
Viewed 986

In Postrges, I know how to create a table with a column that has a serial (number) id:

CREATE TABLE Tag (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

I prefer to have my ids have a human readable aspect to them though. I usually do something like tag_1, tag_2, tag_3 usr_1, usr_2, usr_3.

I've seen instructions on how to generate uuids (Generating a UUID in Postgres for Insert statement?), but I don't really need that. The basic serial number is more than enough, I just want to prefix tag_ to the beginning.

How can I create a Serial ID with as a string with common prefix?

postgresql sql
1 Answers

You can do that with a sequence, which you can add to your table as a default value concatenated via || with the prefix you want e.g.

CREATE SEQUENCE tag_seq START WITH 1;

CREATE TABLE tag 
    (id TEXT NOT NULL DEFAULT 'tag_'||nextval('tag_seq'::regclass), 
    name TEXT);
    
INSERT INTO tag (name) VALUES ('foo'),('bar');    
SELECT * FROM tag;
  id   | name 
-------+------
 tag_1 | foo
 tag_2 | bar
(2 Zeilen)

Demo: db<>fiddle

user_22753880
Related