3

I need to create alpha numeric sequence Example : I have 2 tables say city and student. I want sequence for city to be like "city1,city2,city3...." and for student like "stud1,stud2,stud3" etc

Thanks.

SHRUTAYU Kale
  • 369
  • 1
  • 3
  • 10
  • 1
    Please provide more information by editing your question. What exactly have you tried? Why isn't that working? What does the table look like? – mhlz Mar 25 '15 at 14:02

2 Answers2

5

A sequence cannot be alphanumeric. In fact, you can't even restrict to a lesser numeric type in PostgreSQL: it's always a bigint.

But sequence is just one part of creating a column, which is like a so-called auto-increment.

PostgreSQL's pseudo type serial works like this:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

To achieve what you want, you only need to define custom DEFAULT expression, like:

CREATE SEQUENCE city_city_id_seq;
CREATE TABLE city (
    city_id text NOT NULL DEFAULT ('city' || nextval('city_city_id_seq'))
);
ALTER SEQUENCE city_city_id_seq OWNED BY city.city_id;
pozs
  • 34,608
  • 5
  • 57
  • 63
4

You can use BEFORE INSERT trigger for that. This trigger can get a number from a sequence and add a word to it.

Other option is to use a simple VIEW on top of the table.

UPD: Using a custom default like DEFAULT ('city' || nextval('city_city_id_seq')) (from pozs answer) is better than using a trigger.

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44