Re: [GENERAL] Query to get the "next available" unique suffix for a name
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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to get the "next available" unique suffix for a name
On Tue, Sep 28, 2010 at 12:44 AM, Arjen Nienhuis 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,1)) 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!! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to get the "next available" unique suffix for a name
> 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,1)) x WHERE t NOT in (SELECT a FROM test) LIMIT 1 RETURNING *; Groeten, Arjen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to get the "next available" unique suffix for a name
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general