Naming each row in the table with a random 2 word string

Question

I want to choose a simple naming conventions to the table in my database.

It means I have to name each row with a random 2 word string.

For example

ID NAME

1 ROMEL SUMPI

2 BORMI SUIEMOD

and so on,,,,,,,,,

It means each NAME column should have a Unique name.....

How can I do this in PHP which uses postgreSQL DB....

Thank you in advance,,.,,,,

Answer 1

I suspect you actually mean "arbitrary" unique names. Simple way could be:

INSERT INTO tbl (id, name)
SELECT g, 'name'::text || g || ' surname' || g
FROM   generate_series(1, 1000) g;

.. to generate 1000 distinct names - not random at all, but unique.

To generate 100 names consisting of two words with 3 - 10 random letters from A-Z:

INSERT INTO tbl (id, name)
SELECT g%100
      ,left(string_agg(chr(65 + (random() * 25)::int), ''), 3 + (random() * 7)::int)
       || ' ' ||
       left(string_agg(chr(65 + (random() * 25)::int), ''), 3 + (random() * 7)::int)
FROM   generate_series(1, 1000) g
GROUP  BY 1
ORDER  BY 1;

ASCII code of 'A' is 65, the one of 'Z' is 90. Fortunately, the range between spans the basic upper case alphabet. You can find out with the ascii() function, which is the reverse of chr():

SELECT ascii('A')

The second method doesn't guarantee uniqueness, but duplicates are extremely unlikely with just a few hundred names. Eliminating possible duplicates is trivial. Add another SELECT layer where you GROUP BY name and pick min(id).

Answer 2

It sounds like you might want a pair of randomly-selected words from a dictionary. It's kind of hard to tell given the lack of clarity of the question.

RANDOM DICTIONARY WORDS

The best way to pick random dictionary words is probably at the PHP end by using a pass-phrase generator that does it for you.

You can do it in PostgreSQL using a table dictionary with one word per row, though:

SELECT word FROM dictionary ORDER BY random() LIMIT 2;

Performance will be truly awful with a large dictionary. It can be done much faster if the dictionary doesn't change and there's a unique word_id with no gaps in the numbering, allowing you to write:

CREATE OR REPLACE FUNCTION get_random_word() RETURNS text AS $$
SELECT word FROM dictionary 
WHERE word_id = (
  SELECT width_bucket(random(), 0, 1, (SELECT max(word_id) FROM dictionary))
);
$$ LANGUAGE sql;

SELECT get_random_word() || ' ' || get_random_word();

against a table like this:

CREATE TABLE dictionary(word_id serial primary key, word text UNIQUE NOT NULL);

This will only produce consistent results if there are no gaps in the word numbering and if word_id is unique or the PRIMARY KEY. It can produce the same word twice. If you want to avoid that you'll need a recursive CTE or some PL/PgSQL.

RANDOM GIBBERISH

If you actually want truly random strings, that's already well covered here on Stack Overflow. See How do you create a random string that's suitable for a session ID in PostgreSQL? among others; look at this search.

To ensure uniqueness, just add a UNIQUE constraint. Have your application test to see if a unique_violation was raised when you INSERTed the row, and insert it with a new random ID if a violation occurred. If you like you can automate this with a PL/PgSQL helper procedure, though it'll still be subject to races between concurrent inserts in different transactions.