CODEWITHSUNDEEP
postgresql
Beasterfield
Beasterfield

Reputation: 7113

Creating a sequence which values are a mix of character and numbers

I am using Postgres 9.1 and would like to create a sequence which I can use as primary key and which should look like this:

| entity_id_seq |
-----------------
|         X0001 |
|         X0002 |
           ....
|         X0123 |

and nextval( 'entity_id_seq' ) would return 'X0124'.

Any idea's how and if this is possible with pure Postgres?

Many thanks!

Upvotes: 3

Views: 3659

Answers (2)

Szymon Lipiński
Szymon Lipiński

Reputation: 28604

Something like this should be fine:

Create the sequence:

CREATE SEQUENCE special_seq;

Create a function, or not:

CREATE OR REPLACE FUNCTION
nextval_special()
RETURNS TEXT
LANGUAGE sql
AS
$$
    SELECT 'X'||to_char(nextval('special_seq'), 'FM0000'); 
$$;

Now check if it works:

SELECT nextval_special() FROM generate_series(1,10);

 nextval_special 
-----------------
 X0001
 X0002
 X0003
 X0004
 X0005
 X0006
 X0007
 X0008
 X0009
 X0010
(10 rows)

Now you can create the table using the above function:

CREATE TABLE test (
    id TEXT PRIMARY KEY DEFAULT nextval_special(),
    a int
);

or just without function with the simple query from the function:

CREATE TABLE test (
    id TEXT PRIMARY KEY DEFAULT 'X'||to_char(nextval('special_seq'), 'FM0000'),
    a int
);

Upvotes: 7

Siddharth
Siddharth

Reputation: 102

We could make a function that would make use nextval and prepend the X to it. Something like this :

CREATE FUNCTION nextseq() RETURNS text
AS 
begin 
return 'X'||lpad(nextval( 'entity_id_seq',4,'0' );
end;

Upvotes: 2

Related Questions