how to prefix a string before sequence generated by postgresql?

Question

This is a sequence

CREATE SEQUENCE technician_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

It generates

1
2
3
4

I need the sequence as

AAA1
AAA2
AAA3
AAA4

Is it possible? I am very much new to postgresql.

Will it always be AAA? If so do it in the presentation layer. — Clodoaldo Neto
– Clodoaldo Neto, Commented Jul 29, 2013 at 12:57
@ClodoaldoNeto it will be change. I can do it in presentation layer. Just asking, whether it is possible with postgresql. it will save some effort — Batakj
– Batakj, Commented Jul 29, 2013 at 13:01
Wouldn't it be easier to maintain the prefix on the presentation/UI layer? If somebody wants a differen prefix you can just tweak a template. If you put this stuff on DB then its much harder to change afterwards. — jsalonen
– jsalonen, Commented Jul 29, 2013 at 13:05
It depends. If you only need the prefix as something that is shown to users, I'd put it on a template. If the prefix is something that you need to be able to search etc. then in that case I'd put it into database. I think you maybe need to describe your requirements more if you want a precise answer. — jsalonen
– jsalonen, Commented Jul 29, 2013 at 13:25

Rob Bednark · Accepted Answer · 2018-04-27 16:13:20Z

17

Here are a couple ways:

-- Referencing the sequence directly:
CREATE SEQUENCE test_seq;

SELECT 'AAAA'||nextval('test_seq')::TEXT;
 ?column? 
----------
 AAAA1

SELECT 'AAAA'||nextval('test_seq')::TEXT;
 ?column? 
----------
 AAAA2


-- Using a DEFAULT
CREATE TABLE abc 
    (val TEXT NOT NULL DEFAULT 'AAAA'||nextval('test_seq'::regclass)::TEXT, 
    foo TEXT);

INSERT INTO abc (foo) VALUES ('qewr');

SELECT * FROM abc;
  val  | foo  
-------+------
 AAAA3 | qewr

These assume that you have carefully decided how to proceed, based on the comments to your original question, as asked by the others.

edited Apr 27, 2018 at 16:13

Rob Bednark

28.8k28 gold badges90 silver badges131 bronze badges

answered Jul 29, 2013 at 13:39

bma

9,8362 gold badges35 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

how to prefix a string before sequence generated by postgresql?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related