Auto Increment custom id with prefix GRO
Hi guys.
New here, anyone know how to create custom id where i want to have
GRO0001
GRO0002
and so on with prefix GRO
and the numbers will be auto increment.
7 Replies
right now i'm trying to use
custom_property_id: text('custom_property_id').default(
GRO${String(Math.floor(Math.random() * 10000)).padStart(4, '0')}),
but sometime same id is created especially when there is action from 2 different people.
BumpHello, @alka_99! I think it should be done with triggers, but as for now drizzle doesn't support it natively
trigger and functions from the supabase itself right? @Mykhailo ?
Something like this?
I think I was wrong. I found this information, so it might be useful for you
https://dba.stackexchange.com/questions/72452/auto-increment-char-varying-column-prefixed-with-some-characters
Database Administrators Stack Exchange
Auto increment char varying column prefixed with some characters
I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well.
Is there a way to achieve ...
let me try
I tried something like this before, the problem is, when i do logout from my dashboard the counter starting from GRO0001 again
So in this article telling me that the best practice is not store the data directly like GRO0001 and use normal integer instead, but the problem is when we want to like full text search filter, etc
This myt be hacky but has proved to be very finctional for us. We use a function like this for every table that needs those incremental references:
export async function genAccountCode() {
const suffix = 'GRO';
const result = await db.query.accountsTable.findMany({
where: (x, { like }) => like(x.actCode,
${suffix}%),
columns: {
actCode: true,
},
orderBy: [desc(accountsTable.actCode)],
limit: 1,
});
//checking that we have a valid result
const lastRefNo = result && result.length > 0 ? result[0]?.actCode : null;
try {
//checking that lastRefNo is not null
if (lastRefNo != null) {
// here you remove(trim) the suffix to be able to increment it.
const trimmed = lastRefNo.substring(3);
// here u increment the number part by 1
const incremented = (parseInt(trimmed) + 1).toString();
// put the number of zeros needed, e.g pad 3, on the incremented value, the put the suffix to get new code to use.
const newActCode = ${suffix}${incremented.padStart(3, '0')};
return newActCode;
} else {
return ${suffix}001;
}
} catch (error) {
throw new Error('Error generating Account Code');
}
}