Обсуждение: Query to get the "next available" unique suffix for a name

Hi all -

Let's say the user signs up for an account on my site and they need to
pick a unique user name.  They type in:

MikeChristensen

However, me and several of my dopplegangers already have accounts.
Thus, the users table already has:

MikeChristensen1
MikeChristensen2
MikeChristensen3
MikeChristensen4

I want to write a SQL query that figures out that MikeChristensen5 is
the next available username and thus suggest it.  Here's some things I
could do:

1) Just tack a random number at the end and check it to make sure it's
unique, if not then loop.  I might do a few SQL calls but it would
work.  However, it looks a bit hacky if I say "Do you want to create
the account MikeChristensen394783?"

2) I could write a DO...WHILE loop in a SQL function that first checks
if the base name is available, and then tacks on a number and
increments it until the name is available.  However, this gets slower
and slower and has to run one query per name.  The username "Joe"
might require 1,000 queries someday.  Maybe this could be optimized by
incrementing more each loop.

3) Do something really funky with the schema, attempting to store the
next available suffix for each name somewhere in a table.  This gets
messy the more I think about it.  Ick.

4) Do a SELECT on each row that starts with "MikeChristensen" and then
trying to append the row count to the end, this might not be exact but
it's somewhat intelligent as a starting point.  However, this might
require some special indexes on this table to quickly scan rows that
start with a certain name.  However, if I get to the point where this
becomes slow then I can worry about it at that point since this would
only be run on new account creation and not super speed critical.

This post isn't really a how-to question, just looking for someone who
wants to toss out a few ideas for avenues to explore..  Thanks!!

Mike

  On 09/27/10 6:36 PM, Mike Christensen wrote:
> Thus, the users table already has:
>
> MikeChristensen1
> MikeChristensen2
> MikeChristensen3
> MikeChristensen4
>
> I want to write a SQL query that figures out that MikeChristensen5 is
> the next available username and thus suggest it.  Here's some things I
> could do:

bogo pseudocode.
$n is 'MikeChristensen'

     table users {
         uid serial,
         username text unique,
         userroot text,
         namesuffix integer,
         ...
         }

     sql.exec ("begin;");
     sql.exec ("select max(namesuffix) from users where userroot=$1;", $n);
     $i = sql.result(1);
     newname = $n.$i;
     sql.exec("insert ......");
     sql,exec("commit;");



of course, you need to deal with an unique constraint exception on that
insert, and rollback and retry the whole thing.

> 4) Do a SELECT on each row that starts with "MikeChristensen" and then
> trying to append the row count to the end, this might not be exact but
> it's somewhat intelligent as a starting point.  However, this might
> require some special indexes on this table to quickly scan rows that
> start with a certain name.  However, if I get to the point where this
> becomes slow then I can worry about it at that point since this would
> only be run on new account creation and not super speed critical.
>

CREATE TABLE test (a text PRIMARY KEY);

INSERT INTO test (a)
SELECT * FROM (SELECT 'MikeChristensen' || generate_series AS t FROM
generate_series(1,10000)) x
WHERE t NOT in (SELECT a FROM test) LIMIT 1 RETURNING *;

Groeten, Arjen

On Tue, Sep 28, 2010 at 12:44 AM, Arjen Nienhuis <a.g.nienhuis@gmail.com> wrote:
>> 4) Do a SELECT on each row that starts with "MikeChristensen" and then
>> trying to append the row count to the end, this might not be exact but
>> it's somewhat intelligent as a starting point.  However, this might
>> require some special indexes on this table to quickly scan rows that
>> start with a certain name.  However, if I get to the point where this
>> becomes slow then I can worry about it at that point since this would
>> only be run on new account creation and not super speed critical.
>>
>
> CREATE TABLE test (a text PRIMARY KEY);
>
> INSERT INTO test (a)
> SELECT * FROM (SELECT 'MikeChristensen' || generate_series AS t FROM
> generate_series(1,10000)) x
> WHERE t NOT in (SELECT a FROM test) LIMIT 1 RETURNING *;

Now that's an interesting approach, I will play around with that - thanks!!

On Mon, Sep 27, 2010 at 06:36:25PM -0700, Mike Christensen wrote:
> Thus, the users table already has:
>
> MikeChristensen1
> MikeChristensen2
> MikeChristensen3
> MikeChristensen4
>
> I want to write a SQL query that figures out that MikeChristensen5 is
> the next available username and thus suggest it.

Why not do something like:

  SELECT max(nullif(substring(username FROM '[0-9]*$'),'')::numeric) AS lastnum
  FROM users
  WHERE username ~ '^MikeChristensen[0-9]*$';

It's a pretty direct translation from what I'd do in any imperative
language.

--
  Sam  http://samason.me.uk/