Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

208
Views
how can i store auto increment id with string in postgress sequelize

I want to store the document id in the form of (IP-01, IP-02, IP-03) in the sequelize table. I tried to use autoincrement function on a string, but it showed an error that it can work only on INTEGER value.

I want to store it in such a form that I can store and search easily in the form of (IP-0X). So please anyone can help me

about 4 years ago · Juan Pablo Isaza
1 answers
Answer question

0

So I will give you basic idea, implementation is on you. There is no default function available using which you can autoincrement the value of string. My solution might not be the optimal way to do it but I guess it will solve your problem.

There are 2 option, you can implement some logic to achieve our required output.

  1. On application level :-

By using count utility of sequelize you can count the total no of columns and generate your desired string.

//wherever you are creating the document add following

const count = (await Document.count()) + 1;

const newId = 'IP-' + count;

await Document.create({
id: newId,
...
});

  1. On Database level:-

You need to create a function which will return the id string in required pattern.

CREATE OR REPLACE FUNCTION get_id()
RETURNS varchar
LANGUAGE plpgsql
AS $$
    BEGIN
        RETURN (SELECT 'IP-' || (SELECT count(1) from table_name) + 1);
    END;
$$;

Now you just need to set default value of your id field to function call.

Before doing below changes you need to make sure that you have created get_id function in you database or else you will get function not defined error from database.

\\add this to file where you define your model

id: {
    type: Sequelize.STRING,
    defaultValue: Sequelize.fn('get_id')
}

Here is a demo of database level solution. LINK

EDIT

You can use one more approach instead of counting number of columns you can maintain a Sequence in your database and get the next Integer value from that.

CREATE SEQUENCE document_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1;

-- Then create your get_id function as below:

CREATE OR REPLACE FUNCTION get_id()
RETURNS varchar
LANGUAGE plpgsql
AS $$
    BEGIN
        RETURN (SELECT 'IP-' || nextval('document_id_seq'));
    END;
$$;
about 4 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!