How to create a sequence in varchar in the Postgres database?

Question

How to create a sequence in varchar in the Postgres database?

Navigation

#1 by (5 votes)

6

I have the following table in the Postgres database:

CREATE TABLE customer
(
  userid character varying(30) NOT NULL,
  firstname character varying(30) NOT NULL,
  lastname character varying(30) NOT NULL,
  balance double precision NOT NULL,
  PRIMARY KEY (userid)
)

Would it be possible to create a sequence of type auto increment but that is of type VARCHAR ?

I have a business rule object that needs a String type.

I know the procedure for creating a string with type serial :

CREATE TABLE customer
(
  userid serial NOT NULL,
  ...
  ...
  ...
  PRIMARY KEY (userid)
)

But as I said before, I need a type VARCHAR , because the values that will be entered as primary key will have this form:

id001  
id002  
id003

mysql sql java postgresql

asked by anonymous 21.09.2015 / 20:56

1 answer

Difference between "list (range (1, 10))" and "range (1, 10)"? How do I specify that the builder type is the same as declared in the class?

score 5 · Answer 1

With varchar you will have to create a tigger and increment your field.

Example:

CREATE FUNCTION update_transaction_id() RETURNS trigger AS $BODY$
BEGIN
  NEW.userid  :=  'id' || nextval('seq_name');
  RETURN NEW;
END; 
$BODY$ LANGUAGE 'plpgsql';

Being seq_name is the name of your generator. (If it does not exist you must create it)

Create generator: CREATE SEQUENCE seq_name START 1;

And the trigger in the table to execute the function:

CREATE TRIGGER inc_id
  AFTER INSERT
  ON customer
  FOR EACH ROW
  EXECUTE PROCEDURE update_transaction_id();