Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to prefix a string before sequence generated by postgresql?

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.

like image 253
Shashi Avatar asked Jul 29 '13 12:07

Shashi


1 Answers

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.

like image 128
bma Avatar answered Sep 21 '22 01:09

bma