OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

What is an optimal way to create unique human readable values in postgres?

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

Guest
I have an orders table. For simplicity sake, I'll just include the columns that matters.

idnumber
uuid1ABC123
uuid2DEF456

The number column is unique but it is not auto-generated by the database, although I hope it could. And it has to be easy to read.

Currently, I'm running a recursive function to look up the table for a number before inserting.

Code:
const generateOrderNumber = async(length) => {
  const number = generateKey(length).toUpperCase(); // random readable string with length of 6

  const exists = await pg.query(`SELECT * FROM orders WHERE number = $1`, [number]);

  if (exists.rows.length) {
    return await generateOrderNumber(length + 1);
  }

  return number;
);

const number = await generateOrderNumber(6);

await pg.query(`INSERT INTO orders (number) VALUES ($1)`, [number]);

What this function does is it will generate a string of the specified length, look up the orders number column with that string, if exists, rerun the function again with length + 1 until none returns.

Is this an optimal way to tackle this problem? Are there better solutions?

<p>I have an orders table. For simplicity sake, I'll just include the columns that matters.</p>
<div class="s-table-container"><table class="s-table">
<thead>
<tr>
<th>id</th>
<th>number</th>
</tr>
</thead>
<tbody>
<tr>
<td>uuid1</td>
<td>ABC123</td>
</tr>
<tr>
<td>uuid2</td>
<td>DEF456</td>
</tr>
</tbody>
</table></div>
<p>The <code>number</code> column is unique but it is not auto-generated by the database, although I hope it could. And it has to be easy to read.</p>
<p>Currently, I'm running a recursive function to look up the table for a number before inserting.</p>
<pre><code>const generateOrderNumber = async(length) => {
const number = generateKey(length).toUpperCase(); // random readable string with length of 6

const exists = await pg.query(`SELECT * FROM orders WHERE number = $1`, [number]);

if (exists.rows.length) {
return await generateOrderNumber(length + 1);
}

return number;
);

const number = await generateOrderNumber(6);

await pg.query(`INSERT INTO orders (number) VALUES ($1)`, [number]);
</code></pre>
<p>What this function does is it will generate a string of the specified length, look up the orders number column with that string, if exists, rerun the function again with length + 1 until none returns.</p>
<p>Is this an optimal way to tackle this problem? Are there better solutions?</p>
 

Latest posts

A
Replies
0
Views
1
AgencyAnalytics
A
S
Replies
0
Views
1
Stacker Media
S
C
Replies
0
Views
1
CC.Talent
C
Top